How to send an email once an online form updates a google sheet?

user_19587840 0
Viewed 30

I created an online submission form on tally.so linked to a Google Sheet. I also wrote a script that should send an email once the sheet has been updated with the form's answer, but the email doesn't seem to send.

Here is what I've wrote. Thank you in advance:

   var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FormSubmission");
   const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,18).getValues();

...

   if (e.range.columnStart == 3){ 
   MailApp.sendEmail({
     to: "abc@gmail.com",
     subject: "New Request",
     htmlBody: msg,
     noReply: true
   });
   }

}
google-apps-script google-sheets forms email javascript
1 Answers

Modification points:

  • From your question of I created an online submission form on tally.so linked to a Google Sheet. I also wrote a script that should send an email once the sheet has been updated with the form's answer, but the email doesn't seem to send., I guessed that in your situation, the value might be appended to the sheet using Sheets API.

  • From e.range.columnStart and e.range.rowStart of your showing script, you might be using the installed OnEdit trigger.

If my understanding is correct, when a value is appended to the sheet using Sheets API, the installable OnEdit trigger is not fired. I thought that this might be the reason for your issue.

If you want to run the script when the value is appended using Sheets API, it is required to use the installed OnChange trigger. And, in this case, in order to retrieve the appended row, var rData = sheet.getRange(sheet.getLastRow(), 1, 1, 18).getValues() or var rData = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getValues() is required to be used.

When these points are reflected in your script, how about the following modification?

Modified script:

Please install the OnChange trigger to the function of installedOnChange. Ref When you use this script, please submit the values using your form.

function installedOnChange(e) {
  var sheet = e.source.getSheetByName("FormSubmission");
  var rData = sheet.getRange(sheet.getLastRow(), 1, 1, 18).getValues(); // Modified

  // do something
  // Please set msg.

  MailApp.sendEmail({
    to: "abc@gmail.com",
    subject: "New Request",
    htmlBody: msg,
    noReply: true
  });
}

  • rData is the value of the last row of "FormSubmission" sheet.

  • Please add your script and please set msg.

  • The event object of OnChange trigger doesn't include the property of range. Please be careful about this.

Reference:

user_71086530
Related