Need Script for Google Docs to Send Auto Email

0
votes

I'm looking for a script that I can add to a Google sheet that will auto generate an email and include some of the fields in the spread sheet.
I had created a Google Form and I have that data going to the Google spreadsheet, the idea is when the user submits the form it sends that data to the spreadsheet and the spreadsheet sends an automated email.
I found this script and edited it some but it fails on the 4th line (var theEvent = e.values[1]):

function AutoConfirmation(e){
      var theirFirst = "Bill";
      var theirEmail = [email protected];
      var theEvent = e.values[1];
      var subject = "Form Submitted";
      var message = "Thank you, " + theirFirst + " for the expressed interest in our " + theEvent;
   MailApp.sendEmail (theirEmail, subject, message);
}

Shouldn't line 4 pull the data from column 1 in my google sheet? Is this old an script and it doesn't work now? In my google sheet I have Site instead of event as a column and another that has Complete as a header. Let me know what I have missed here as it seems that this should be simple. I tried to run this and this is the result: screenshot of the error I get I get the same type of error when running the code above so I thought I would run a logger to see if I get anything with that and the result is in the screen shot. Click the link to see it.

2
google-apps-scriptgoogle-sheetstriggersgoogle-forms
Although, unfortunately, From but it fails on the 4th line (var theEvent = e.values[1]):, I cannot understand about the detail of your issue, if you directly run the function by the script editor, an error occurs because the event object is not used. How about this? – Tanaike
How is this failing? What error do you get? e.values[1] corresponds to column B. Is that the data you want to retrieve? – Iamblichus
This is the error I get with the above code: TypeError: Cannot read property 'values' of undefined (line 4, file "Code") – kerrr

2 Answers

0
votes

It looks that the script that you found is function to be put in a Apps Script project bounded to a Google spreadsheet and called by an installable trigger.

Shouldn't line 4 pull the data from column 1 in my google sheet?

No. e.values returns an Array which use a zero based index. This means that index for Column A, the first column, is 0.

0
votes

Issue:

The error you are getting:

TypeError: Cannot read property 'values' of undefined

Means that the event object (e) is undefined, which means that you are trying to run this function manually. Functions that are attached to a trigger are supposed run when the corresponding trigger event happens: in this case, when a user submits the Form. You don't have to run it manually.

Solution:

  • Step 1: Install the trigger: If you haven't done so yet, install the trigger, either manually, following these steps, or programmatically, by copy the following function to your bound script and running it once:
function createOnFormSubmitTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("AutoConfirmation")
  .forSpreadsheet(ss)
  .onFormSubmit()
  .create();
}
  • Step 2: Submit the form!: Once the trigger is installed, when a user submits the Form that is attached to your Spreadsheet (that is, assuming that you have attached the Form to the Spreadsheet), AutoConfirmation runs automatically, and the event object, containing these properties, is passed as an argument. If you run it manually, e is undefined (no event object is passed as parameter), and you get the error.

Note:

  • e.values[1] will retrieve the same value that is written to column B when the form is submitted, since JavaScript arrays are zero-indexed. You might want to use e.namedValues['yourProperty'] instead, to make sure your are retrieving the desired information.

Reference: