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

Question

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!

Use a 'onFormSubmit' trigger. See here

JPV
– JPV

2021-12-03 11:24:36 +00:00
Commented Dec 3, 2021 at 11:24 — JPV
– JPV, Commented Dec 3, 2021 at 11:24

Mike Steelson · Accepted Answer · 2021-12-03 12:56:06Z

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

edited Dec 3, 2021 at 12:56

answered Dec 3, 2021 at 12:17

Mike Steelson

15.4k2 gold badges7 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BCLC Over a year ago

lunch vs launch?

Mike Steelson Over a year ago

ok, sorry, I'am french !! execute via the script interface to put the onformsubmit trigger in place

Iamblichus · Accepted Answer · 2021-12-06 08:48:37Z

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

MRGDRK · Accepted Answer · 2022-01-13 13:03:57Z

0

Thanks for your contributions. I found an easier solution and wanted to share:

    function sendEmailv3() { 
     var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses Sheet'); 
     var emailAddress = sheet.getRange(sheet.getLastRow(),4).getValue();
     var message = sheet.getRange(sheet.getLastRow(),3).getValue();


     MailApp.sendEmail(emailAddress, "SubjectLine", message,{noReply:true});
  }

I hope you find it useful.

answered Jan 13, 2022 at 13:03

MRGDRK

627 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Issues:

Solution:

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Issues:

Solution:

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related