Send email from google spreadsheet

Question

I found the following script to insert form submission values into a google spreadsheet.

function doPost(e) { // change to doPost(e) if you are recieving POST data
  var ss = SpreadsheetApp.openById(ScriptProperties.getProperty('active'));
  var sheet = ss.getSheetByName("DATA");
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; //read headers
  var headers2 = sheet.getRange(2, 1, 1, sheet.getLastColumn()).getValues()[0]; //read headers  
  var nextRow = sheet.getLastRow(); // get next row
  var cell = sheet.getRange('a1');
  var col = 0;
  for (i in headers2){ // loop through the headers and if a parameter name matches the header name insert the value
    if (headers2[i] == "Timestamp"){
      val = new Date();
    } else {
      val = e.parameter[headers2[i]]; 
    }
    cell.offset(nextRow, col).setValue(val);
    col++;
  }
  //http://www.google.com/support/forum/p/apps-script/thread?tid=04d9d3d4922b8bfb&hl=en
  var app = UiApp.createApplication(); // included this part for debugging so you can see what data is coming in
  var panel = app.createVerticalPanel();
  for( p in e.parameters){
    panel.add(app.createLabel(p +" "+e.parameters[p]));
  }
  app.add(panel);
  return app;
}
//http://www.google.sc/support/forum/p/apps-script/thread?tid=345591f349a25cb4&hl=en
function setUp() {
  ScriptProperties.setProperty('active', SpreadsheetApp.getActiveSpreadsheet().getId());
}

Now I want to send a formatted email to two of my coworkers every time a row gets inserted. I tried to use:

    var emailAddress = "[email protected]";  // First column
    var message = "message";       // Second column
    var subject = "Sending emails from a Spreadsheet";
    MailApp.sendEmail(emailAddress, subject, message);

but it's not sending anything.. can anyone advise please?

MailApp is the correct service to use, and your code looks to be correct. It could be that there is a mistake somewhere in your code, so try to debug the problem. — Eric Koleda
Are the values getting inserted into the spreadsheet? Are you getting any errors? When you set up the trigger for this script, if you setup notifications at the same time, you can get an immediate email when there is a problem. — bigelowr

Blue Toque Blue Toque · Accepted Answer · 2013-03-02T00:49:52

I had the same trouble.

With the most recent version of Google Apps I had to save the script, create a new revision, and re-publish the script making sure to select the new revision. After this the new code was in effect. I wasted several hours in the belief that the script would be updated if I just saved it.

Somehow the new method saves the script elsewhere. It's almost impossible to tell what code is actually running unless you go through the process of saving a revision, and re-publishing.

Send email from google spreadsheet

1 Answers