电子表格中变量的行号(Apps脚本)

Question

在我的电子表格中，我从用户那里获取了答案，并添加了一个列来对他们进行评分。现在，我想把他们输入的全部数据和他们各自的成绩邮寄给他们。要做到这一点，我必须知道变量"key2“的行号，它将随着用户的每次响应而变化。我在这里提供我的代码。谁来帮帮我。

/* 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) {
    var s1 = SpreadsheetApp.getActiveSpreadsheet();
    var s2 = SpreadsheetApp.setActiveSheet(s1.getSheetByName("Sheet2"));

    try {
        var  cc, sendername, subject, column2;
        var message, value, textbody, sender;

        // This is your email address and you will be in the CC
        cc = Session.getActiveUser().getEmail();

        // This will show up as the sender's name
        sendername = "Quiz2win";

        // Optional but change the following variable
        // to have a custom subject for Google Docs emails
        subject = "Google Form Successfully Submitted";

        // This is the body of the auto-reply
        message += "We have received your details.<br />Thanks!<br /><br />";

        column2 = s2.getRange(1,1,1,10).getValues()[0];
        // This is the submitter's email address
        sender = e.namedValues["Email Address"].toString();

        // Only include form values that are not blank

        for ( var keys in column2 ) {

            var key2 = column2[keys];
            if ( e.namedValues[key2] ) {
                message += key2 + ' :: '+ e.namedValues[key2] + "<br />";
            } else  {
                var key1 = s2.getValues();
                message += key2 + ' :: ' + key1 + "<br/>";
            }
        }

        textbody = message.replace("<br>", "\n");

        GmailApp.sendEmail(sender, subject, textbody, 
                        {cc: cc, name: sendername, htmlBody: message});

    } catch (e) {
    Logger.log(e.toString());
}

Answer 1

您在这里提到的范围s2.getRange(1,1,1,10)基本上只从工作表中获取第一行数据。

取而代之的是，将所有行和列值放入一个范围变量中，循环遍历每行的范围，并在循环本身中发送一封电子邮件。

有关更多详细信息，请查看此documentation。