I am collecting requests via Google Forms and they are stored in a Google Sheets. So whenever a new submission comes, I would like to send an email based on the choices.

Currently I have created a separate tab where country and recipient of the email exist.

In my form response tab, I wrote a formula:

=query(ArrayFormula(iferror(vlookup(B2:B;Sayfa2!$A$2:$B$4; 2; false))); "where Col1 <>''")

It looks like:

So pulling the data when a new submission comes. However, my GAS does not trigger email

//setup function

var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

if (ActiveSheet.getName() == 'Form Yanıtları 1') {

 var StartRow = 2;

 var RowRange = ActiveSheet.getLastRow() - StartRow   1;

 var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,4);

 var AllValues = WholeRange.getValues();
 
   
 var message = ""

  //iterate loop

 for (i in AllValues) {

   //set current row

   var CurrentRow = AllValues[i];
   var row = AllValues[i]
   var alertRecipient = row[3];
   var emailMessage= row[2];
   var emailSubject = "A suggestion received for your style guide"

     //set HTML template for information

     message  =


         "<p><b>Option: </b>"   CurrentRow[1]   "</p>"  

           "<p><b>Comment: </b>"   CurrentRow[2]   "</p>"  

             "<p><b>Recipient: </b>"   CurrentRow[3]   "</p>"  
                      "</p><br><br>";


   }

 }//For loop close

 //define who to send emails to

 // var SendTo = "[email protected]";

 //set subject line

 // var Subject = "New Form";

 //send the actual email   if message is not empty

 if (message) {

   MailApp.sendEmail(alertRecipient,emailSubject,emailMessage);

 }//if message

}//if sheetName Review
//End Func

I set up my GAS trigger as onEdit.

Could you please help me to understand where I do the mistake?

Thanks so much in advabce!

CodePudding user response：

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]);
      }
    }
  }
}

CodePudding user response：

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).