How to send an email only to the last Google form submission?

user_16836415 0
Viewed 59

I am using a Google Form for a classroom 'store' for students to purchase items from. A student cashier will fill out the form and then the sheet will send an email receipt to the student that purchased the items. The email sends fine with my current code and trigger, however, it sends an email to all submissions on each new submission. I only want it to send to the last submission. I know there's probably an easy fix to this and I'm just overlooking something. Here is my current code:

function sendAutomatedEmails() {
  var spreadSheet = SpreadsheetApp.getActiveSheet();
  var dataRange = spreadSheet.getDataRange();
  
  var data = dataRange.getValues();
  for (var i = 1; i< data.length; i++) {
    (function(val) {
      var row = data[i];
      var emailAddress = row[3];
      var message = 'At '+ row[0] + 'You purchased the following:' + '\n\n' + row[4] + '\n' + row[5] + '\n' + row[6] + '\n' + row[7] + '\n' + row[8] + '\n' + row[9] + '\n' + 'Your total cost is $' + row[10];
      var subject = 'Personal Purchase Reciept';
      MailApp.sendEmail(emailAddress, subject, message);
    })(i);
  }
}

I can't delete submissions every time as another classroom job is to check that purchases were paid for in our online bank system each week which is when the submissions will be deleted. Any help is greatly appreciated.

google-forms google-apps-script google-sheets
2 Answers

This would fit your need.. With .getDataRange() you get the hole sheet. Then you loop trough the data and sending an email for each row. I assume you got the script from somewhere?

function sendAutomatedEmails() {
  var spreadSheet = SpreadsheetApp.getActiveSheet();
  var dataRange = spreadSheet.getRange(spreadSheet.getLastRow(), 1, 1, spreadSheet.getLastColumn()).getValues();
  var row = dataRange[0];
  var emailAddress = row[3];
  var message = 'At ' + row[0] + 'You purchased the following:' + '\n\n' + row[4] + '\n' + row[5] + '\n' + row[6] + '\n' + row[7] + '\n' + row[8] + '\n' + row[9] + '\n' + 'Your total cost is $' + row[10];
  var subject = 'Personal Purchase Reciept';
  MailApp.sendEmail(emailAddress, subject, message);
}

Reference:

user_134611830

This function sends the email for the data in the form submission even if multiple form submission come at the same time. If you expect multiple submission at the same time you should consider using Lock Service in the function. This does not have to do any additional reads of the data sheet because all of that info is already in the formSubmit event object.

function sendAutomatedEmails(e) {
  let message = 'At ' + e.values[0] + 'You purchased the following:' + '\n\n' + e.values[4] + '\n' + e.values[5]+ '\n' + e.values[6] + '\n' + e.values[7] + '\n' + e.values[8] + '\n' + e.values[9] + '\n' + 'Your total cost is $' + e.values[10];
  MailApp.sendEmail(e.values[3], 'Personal Purchase Receipt', message);
}

function runThisToSetupTrigger() {
  const ss = SpreadsheetApp.getActive();
  if (ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == 'sendAutomatedEmails').length == 0) {
    ScriptApp.newTrigger('SendAutomatedEmails').forSpreadsheet(ss.getId()).onFormSubmit().create();
  }
}
user_72150910
Related