How to run script if a google sheets cell contains email?

Question

I'm looking for help to send an email whenever a new row is added by a google form entry if said entry contains an email in the Email column. I'm new to Javascript, but I've pieced together some code which I plan to run off an onEdit trigger in GSheets.

My problem is that if there is no email address, the code will fail. I need to know how to wrap this in an "if/else" or maybe just a simple error handling bit would be fine, not sure.

If I go with an "if/else", I'll need to check if the email column contains a value. I don't need to check if it is a valid email; the google form already does this on submission.

Here is the code I have right now:

function MessageNotification() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.setActiveSheet(ss.getSheetByName("Message Board"));
// 
//extracts the values in last row and stores them into a two-dimensional 
array called data
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var dataRange = sheet.getRange(lastRow,3,1,8);
var data = dataRange.getValues();
// 
//pull column elements into a one-dimensional array called rowData
for (i in data) {
 var rowData = data[i];
 var emailAddress = rowData[2];
 var poster = rowData[7];
 var subject = rowData[3];
 var recipName = rowData[6];
 var comment = rowData[4];
 var replyLink = rowData[5];
  //
  //
   var message = 'Dear ' + recipName + ',\n\n'+poster+' has posted the 
following comment directed to you: '+'\n'+comment+'\n\n'+'To reply to this 
comment click: '+replyLink;
   var subject = subject;
     MailApp.sendEmail(emailAddress, subject, message);
 }
}

thanks in advance for any help you can give me.

Why an `on edit` trigger? There is a much more [appropriate trigger](https://developers.google.com/apps-script/guides/triggers/events#form-submit). A google search regarding `if else` and javascript should resolve your question about how to use `if/else` Also, https://stackoverflow.com/questions/500504/why-is-using-for-in-with-array-iteration-a-bad-idea — tehhowch, Mar 21 '18 at 17:51
Indeed. `on edit` will not fire from programmatic events such as form response insertion. I linked the event object reference, which includes links to the appropriate documentation for what is contained in it. `if(e.namedValues["this is the name of the question to which an email is an answer"].length) { /* there was an email in the answer array */ } else { /* there was not an email */ }` — tehhowch, Mar 21 '18 at 17:58

score 0 · Answer 1 · answered Mar 21 '18 at 19:06

Thank you tehhowch for the help. I'm new at this so I'll have to continue researching the link you referred to regarding iteration best practice. However I was able to get this working with a simple 'if' wrapper, which turned out to be simpler than I thought.
I did find out that form submission does not recognize an active sheet, so manually testing my code worked, while form submission did not trigger it. After some looking, I replaced: var ss = SpreadsheetApp.getActiveSpreadsheet(); with this:

var ssID = '//insert spreadsheet id here';
var ss = SpreadsheetApp.openById(ssID);

This still did not work, so I had to kickstart it by deleting the trigger and putting it back in (found this info: On form submit trigger not working)

This may not be the most efficient code, but here is what I have now, and it does work:

function MessageNotification() {
var ssID = '//insert spreadsheet id here';
var ss = SpreadsheetApp.openById(ssID);
ss.setActiveSheet(ss.getSheetByName("Message Board"));
//extracts the values in last row and stores them into a two-dimensional 
array called data
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var dataRange = sheet.getRange(lastRow,3,1,8);
var data = dataRange.getValues();
// 
//pull column elements into a one-dimensional array called rowData
for (i in data) {
 var rowData = data[i];
 var emailAddress = rowData[2];
 var poster = rowData[7];
 var subject = rowData[3];
 var recipName = rowData[6];
 var comment = rowData[4];
 var replyLink = rowData[5];
  //
  //
   var message = 'Dear ' + recipName + ',\n\n'+poster+' has posted the 
following comment directed to you: '+'\n'+comment+'\n\n'+'To reply to this 
comment click: '+replyLink;
   var subject = subject;
  if(emailAddress)
  {
    MailApp.sendEmail(emailAddress, subject, message);}
 }
}

score 0 · Answer 2 · answered Mar 21 '18 at 20:01

As mentioned in the question comments, you want to use the event object available to the on form submit trigger. This can be accessed from a container-bound script on either the form or its responses spreadsheet, simply by adding a parameter to the function that receives the trigger.

This object is of the form:

e: {
  authMode: <enum>,
  namedValues: {
    'q1title': [ <q1string> ],
    'q2title': [ <q2string> ],
    ...
  },
  range: <Range>,
  triggerUid: <string>,
  values: [<q1string>, <q2string>, ...]
}

Using this object means that accessing of the Spreadsheet, for the purposes of emailing someone based on contents of the form, is unnecessary.

function MessageNotification(e) {
  if(!e) return; // No form event object was provided.

  var responses = e.namedValues;
  var emailQTitle = /* the title of the question that asks for the email */;
  // Check that 1) this question exists in the response object, and also 
  // 2) it has an answer with a value that 3) is "truthy".
  // https://developer.mozilla.org/en-US/docs/Glossary/Truthy
  if(responses[emailQTitle]                     // 1
      && responses[emailQTitle].length          // 2
      && responses[emailQTitle][0])             // 3
  {
    var emailAddress = responses[emailQTitle][0];
    /* access the responses variable in a similar manner
       for the other variables needed to construct the email */
    MailApp.sendEmail(emailAddress, ... );
  } else {
    /* There was no response to the email question. */
    // You can use View->Stackdriver Logging to inspect the form response, for
    // example, to make sure that it had the format or values you expected. 
    console.log({form_object: e, responses: responses, emailTitle: emailQTitle});
  }
}

How to run script if a google sheets cell contains email?

2 Answers2