google apps script - form not automatically mailing spreadsheet response

Question

I am new to Google Scripts but am getting the gist of it pretty quickly. I am trying to automate ordering of toner for our commercial printer. I set up a Google Form with 4 scale questions, one for each color (CMYK), with multiple choices from 1 through 5 toner cartridges.

Then, in the Google Spreadsheet that records responses, I created a script (mainly from internet examples) that gets the last entry of the spreadsheet (bottom row) and sends an email with the amount of toner cartridges needed, with a trigger onFormSubmit().

When I use the form and hit the submit button, nothing happens. There seems to be a disconnect between the form and the spreadsheet? Maybe some triggers are not working? I tried using the triggers from the script editor and also the programmable triggers and it still does not work from the form submission.

The "ScriptApp" part of the script's trigger doesn't seem to be recognized by the Google Scripts editor, as it's not in a particular color, different from the other code, which makes me wonder if I have to write some form of #include statement or something? All the internet examples show ScriptApp in color.

No bugs are found when "compiling"...

function sendFormByEmail(e) 
{
  ScriptApp.newTrigger("sendFormByEmail").forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet()).onFormSubmit().create();

  var email = "[email protected]"; 

  var s = SpreadsheetApp.getActiveSheet();
  var headers = s.getRange(1,2,1,s.getLastColumn()-1).getValues()[0];
  var colors = s.getRange(s.getLastRow(),2,s.getLastRow(),s.getLastColumn()-1).getValues()[0];
  var message = "";
  var subject = "Toner order";


  for(var i in headers) 
    message += headers[i] + ': ' + colors[i] + "\n\n";



  MailApp.sendEmail(email, subject, message); 
}

You are just creating a trigger for the form attached to your spreadsheet. sendFormByEmil(e) will only be fired when you submit your form (as per your created trigger). — rpm
The Initialize() function doesn't call the sendFormByEmail() function. And you wouldn't want it to if you only want the sendFormByEmail() function to run when the form is submitted. — Alan Wells
Why are you deleting all the triggers first? And why create the trigger in code? — Alan Wells
I edited the question to eliminate redundant code that wasn't needed, such as deleting the triggers first. this code isn't working either... — Yubal Oliveras
Also, I'm creating the triggers in code because from the Resources section of the editor it does not work either so I thought programming them would work? it does not... — Yubal Oliveras

Yubal Oliveras Yubal Oliveras · Accepted Answer · 2015-01-12T19:43:13

This is the answer that definitely works. Had to clear past triggers first, else it would send multiple emails because it was creating a new trigger with each submission:

var triggers = ScriptApp.getProjectTriggers();

  for(var i in triggers) {
    ScriptApp.deleteTrigger(triggers[i]);
  }

ScriptApp.newTrigger("sendFormByEmail").forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet()).onFormSubmit().create();

function sendFormByEmail(e) 
{    

  var email = "[email protected]"; 

  var s = SpreadsheetApp.getActiveSheet();
  var headers = s.getRange(1,2,1,s.getLastColumn()-1).getValues()[0];
  var colors = s.getRange(s.getLastRow(),2,s.getLastRow(),s.getLastColumn()-1).getValues()[0];
  var message = "";
  var subject = "toner order";


  for(var i in headers) {
    message += headers[i] + ': ' + colors[i] + "\n\n";
  }


  MailApp.sendEmail(email, subject, message); 
}

google apps script - form not automatically mailing spreadsheet response

1 Answers