提交表单后获取活动电子表格行 [英] Getting active spreadsheet row after form submission

问题描述

我相对较不熟悉Google Apps脚本，并且一直使用大约一年前创建的非常简单的脚本，该脚本是在用户通过Google表单提交输入时触发的.该脚本一直运行良好，直到大约今年五月为止，而且我一直在努力找出未对代码进行任何更改时发生的情况.我搜索了许多不同的地方，但似乎找不到问题所在.

I am relatively new to Google Apps Script and have been using a very simple script that I created about a year ago that is triggered when a user submits inputs via a Google Form. The script has been working perfectly until approximately this past May and I've been scrambling trying to figure out what happened when I haven't made any changes to my code. I have searched many different places and cannot seem to find what is wrong.

基本上，我有一个用户可以填写然后提交的表格.提交后，脚本将从最新行中获取输入，并将它们存储在变量中，然后将这些变量组装成电子邮件确认消息，以确认每个用户的提交输入.

Basically, I have a form that users complete and then submit. Upon submission, the script takes the inputs from the most recent row and stores them in variables that would then be assembled into an email message confirmation that acknowledges each user's submitted input.

这是我的代码:

function acknowledgement() {

  var ActiveSheet = SpreadsheetApp.getActiveSheet();
  var ActiveRow = ActiveSheet.getActiveRange().getRow();
  var emailAddy = ActiveSheet.getRange("E"+ActiveRow).getValue();
  var locvar = ActiveSheet.getRange("C"+ActiveRow).getValue();
  var employeevar = ActiveSheet.getRange("B"+ActiveRow).getValue();

  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Variables");
  var contactvar = sh.getRange("A2").getValue();
  var subject = sh.getRange("B2").getValue();
  var contactvar2 = sh.getRange("C2").getValue();

  var linebrk = "<br />";

  var msg = "Dear " + employeevar + ","
    + linebrk + linebrk
    + "This confirms that you have completed your review of the latest security presentation."
    + linebrk + linebrk
    + "Your location number is " + locvar + "."
    + "Thank you very much for your participation."
    + linebrk + linebrk
    + contactvar;

  var msghtml = '<p>'+msg+'</p>';

  var advancedargs = {cc:contactvar2, htmlBody:msghtml};
  MailApp.sendEmail(emailAddy, subject, msg, advancedargs);
}

当前正在发生的事情是我的代码不再获取当前行号(即用户刚刚提交的活动行).相反，它只是抓住工作表的第一行(即我的行标题，例如雇员姓名"，电子邮件地址"等)并将这些行标题分配给变量，从而在尝试发送电子邮件确认时产生错误.例如，我的变量emailAddy将包含电子邮件地址"，导致sendEmail失败.

What is currently happening is that my code is no longer grabbing the current row number (i.e. the active row that was just submitted by a user). Instead, it is simply grabbing the top row of the sheet (i.e. my row headings like 'Employee Name', 'Email Address', etc.) and assigning those row headings to the variables thus producing an error when trying to send the email confirmation. For instance my variable emailAddy would contain "Email Address" causing sendEmail to fail.

任何反馈将不胜感激！

推荐答案

您正在寻找代码e.range.getRow().

所以我们有:

function onFormSubmit(e) {
   const row = e.range.getRow()
}

您还需要为onFormSubmit功能设置触发器.

You'll also need to setup a trigger to the onFormSubmit function.

编辑>当前项目的触发器

Edit > Current project's triggers

现在(终于)有些有趣:每次提交表单时，您都会知道它对应的行.玩得开心！

Now (finally) some fun: everytime a form is submitted, you'll know the row it corresponds to. Have fun!

请注意，onFormSubmit函数可以具有任何名称-触发器将映射到任何命名函数，并向其传递包含范围等信息的"e"参数.

Note that the onFormSubmit function could have any name - the trigger will map to any named function and pass to it an "e" argument that contains range.getRow() among other information.

这篇关于提交表单后获取活动电子表格行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！