修改Google电子表格脚本以通过电子邮件发送值

Question

这是一个类似的帖子here的分支。Rivero提供了一个很好的答案，但他的代码发送了谷歌电子表格的响应表上的值。我想知道脚本是否会返回由代码指定的相同行和列范围的值，但从同一电子表格中的其他工作表返回。我对此非常陌生，似乎找不到正确的代码组合来做到这一点。这是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());
  }
}

Answer 1

如果你可以命名你的工作表并使用它，而不是使用活动的电子表格，那会更好。

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++) 
{

}