How to trigger a conditional email when a new Google Sheet row is added via Google Forms

Solution 1:

Try this, launch once myTriggerSetup

const adresseEmail = '[email protected]'
const sujet = 'New form ...'

function myTriggerSetup() {
  if(!isTrigger('onFormSubmit')) {
    ScriptApp.newTrigger('onFormSubmit')
      .forSpreadsheet(SpreadsheetApp.getActive())
      .onFormSubmit()
      .create();
  }
}
function isTrigger(funcName) {
     var r=false;
     if(funcName) {
       var allTriggers=ScriptApp.getProjectTriggers();
       var allHandlers=[];
       for(var i=0;i<allTriggers.length;i++) {
         allHandlers.push(allTriggers[i].getHandlerFunction());
       }
       if(allHandlers.indexOf(funcName)>-1) {
         r=true;
       }
     }
     return r;
}

function onFormSubmit(e) {
  var responses = e.namedValues;
  getData(eval(responses))
  MailApp.sendEmail({
    to: adresseEmail,
    subject: sujet,
    htmlBody: '<table>' + result + '</table>', 
  });
  var d = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd MMM yyyy hh:mm a");
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRow();
  var column = e.values.length + 1;
  sheet.getRange(row, column).setValue('Envoyé : ' + d);
}

let result = ''
function getData(obj) {
  for (let p in obj) {
    if (obj[p]!=null){
      if (typeof obj[p] != 'object' && typeof obj[p] != 'function'){
        result += '<tr><td>'+ obj[p] + '</td></tr>';
      }
      if (typeof obj[p] == 'object') {
        if (obj[p].length){
          result += '<tr><td><b>'+ p + ' :</b></td></tr>';
        }
        getData(obj[p]);
      }
    }
  }
}

Solution 2:

Posting this for documentation purposes, solution was provided in a comment by JPV.

Issues:

• An onEdit trigger fires when users manually change values in the spreadsheet. A form submission causing a new row to be added to a sheet doesn't fire this trigger.
• A simple onEdit trigger won't be able to do actions like sending an email, since that requires authorization, an simple triggers cannot access services that require authorization (see Restrictions).

Solution:

• Install an onFormSubmit trigger instead, which will fire whenever the form is submitted. This could be installed for your form or for the spreadsheet to which submission data is getting sent. You can either install it manually or programmatically.
• Instead of using SpreadsheetApp methods to get the form response data, use the Event object, which also contains submission data (these are the object fields if you install the spreadsheet trigger, and these are the ones if you install it in your form).