这是类似帖子的一个分支。Rivero提供了一个很好的答案,但他的代码发送了Google电子表格响应表上的值。我想知道脚本是否会从同一电子表格中的其他工作表返回代码 BUT 指定的相同行和列范围的值。我对此真的很陌生,似乎找不到正确的代码组合来做到这一点。这是Rivero的代码,我希望有人可以帮助我完成此操作。提前感谢!
/* Send Confirmation Email with Google Forms */
function Initialize() {
var triggers = ScriptApp.getProjectTriggers();
for (var i in triggers) {
ScriptApp.deleteTrigger(triggers[i]);
}
ScriptApp.newTrigger("SendConfirmationMail")
.forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
.onFormSubmit()
.create();
}
function SendConfirmationMail(e) {
try {
var ss, cc, sendername, subject, columns;
var header, message, value, textbody, sender, itemID, url;
// This is your email address and you will be in the CC
cc = "name@email.com";
// This will show up as the sender's name
sendername = "name to be displayed as sender";
// Optional but change the following variable
// to have a custom subject for Google Docs emails
subject = "Choose an approppiate subject";
// This is the body of the auto-reply
message = "";
ss = SpreadsheetApp.getActiveSheet();
columns = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];
// This is the submitter's email address
sender = e.namedValues["Username"].toString();
// Only include form values that are not blank
for ( var keys in columns ) {
var key = columns[keys];
//Use this to look for a particular named key
if ( e.namedValues[key] ) {
if ( key == "Username" ) {
header = "The user " + e.namedValues[key] + " has submitted the form, please review the following information.<br />";
} else {
message += key + ' ::<br /> '+ e.namedValues[key] + "<br />";
}
}
}
}
textbody = header + message;
textbody = textbody.replace("<br>", "n");
Logger.log("Sending email");
GmailApp.sendEmail(cc, subject, textbody,
{cc: cc, name: sendername, htmlBody: textbody});
} catch (e) {
Logger.log(e.toString());
}
}
如果您可以命名工作表并使用它而不是使用活动电子表格,那就更好了。
var sheet = SpreadsheetApp.openById('spreadsheetId').getSheetByName('name');
或者,您也可以使用工作表编号来访问它。
var sheet = SpreadsheetApp.openById('spreadsheetId').getSheets()[0];
引用工作表后,获取列的范围,后跟数据数组:
var columnRange = sheet.getRange('a1Notation');
var dataArray = columnRange.getValues();
然后根据您的方便迭代数据
for(var i=1; i<=dataArray.length; i++)
{
}