Getting active spreadsheet row after form submissi

问题:

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.

Here is my code:

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);
}

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.

Any feedback would be appreciated!

回答1:

Using getActiveRow in the context of a form submission is somewhat strange as one cannot consider that a user is actually active on the sheet... I don't know why you did choose that approach and I'm actually wondering how it happened to work for so long...

There are other possibilities to handle form submissions but the one that will need the fewest changes in your code is to simply use getLastRow() instead of getActiveRange().getRow()

There are a few risks to use that simple "strategy" as there might be concurrency issues when 2 or more people send a form simultaneously. The other solution is to get all the field values directly from the event properties that comes on form submission as in that case each event is unique, no matter how it comes into the spreadsheet but your script will have to be rewritten more deeply.

回答2:

I believe that Google Forms has well covered the case you mention, the trigger "onFormSubmit" the spreadsheet, receives an object as a parameter with all the information you need.

I agree with Serge that the script be rewritten deeply, but definitely, it will save many problems.

Go to the documentation, specifically in "Spreadsheet Form Submit Events" https://developers.google.com/apps-script/understanding_events.

回答3:

You are looking for the code e.range.getRow().

So we have:

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

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!

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.