0

I have a list of customer names and email addresses that are being updated by a Google form.

I'm wondering if it's possible if every time a new customer is recorded, they are automatically sent a welcome email, without running the script every time.

I have "Name" in column "a", and "Email address" in column "B"

I have this simple code that I found, but it only sends to the last row when I run the script.

function activeRowEmail() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var lastRow = sheet.getLastRow() ;

  var name = sheet.getRange(lastRow, 1).getValue();

  var email = sheet.getRange(lastRow, 2).getValue();

  MailApp.sendEmail(email,"new email","hello " + name + ", thank you..... " );
}
ross
  • 2,684
  • 2
  • 13
  • 22
Kobi Perelman
  • 3
  • 2
  • Does every form submission create a new customer? – Cooper Jul 25 '19 at 20:58
  • You only want it to go to the last row because that will always be the person who just filled out the form, right? – J. G. Jul 25 '19 at 20:58
  • I would use an onFormSubmit trigger and get the data from e.values. So it would be something like this: `function activeRowEmail(e) { var body=Utilities.formatString('Hello %s, thank you....',e.values[1]); MailApp.sendEmail(e.values[2],"New Email",body ); }` where I assumed that there is a timestamp in e.values[0] – Cooper Jul 25 '19 at 21:09
  • J.g yeah that right – Kobi Perelman Jul 25 '19 at 21:22
  • thanks cooper, im new in google script so if you can explain me what to do. – Kobi Perelman Jul 25 '19 at 21:28

1 Answers1

1
  1. Create onFormSubmit trigger for activeRowEmail() in spreadsheet
  2. Using the event object find the e.values indexes for mail and name. I assumed that they were 2 and 1 because normally there is a timestamp in the first column of a linked sheet.
    function activeRowEmail(e) { 
      var body=Utilities.formatString('Hello %s, thank you....',e.values[1]); 
      MailApp.sendEmail(e.values[2],"New Email",body ); 
    }
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • thank you, actually i didnt change my code, i just crate a trigger.. i didnt knew about the trigger option. thanks again. – Kobi Perelman Jul 26 '19 at 01:11
  • If you get a lot of form submissions very quickly you may have problems with getLastRow() that is why I like using the data from the event object. It is also possible to get spurious triggers and you can eliminate them with [this](https://stackoverflow.com/a/56388938/7215091) which requires the use of the event object. – Cooper Jul 26 '19 at 01:15