google apps script: mailapp stopped including text in body

Question

I have pieced together two basic functions to create a Google form that returns a unique identifying number when a user hits submit. The functions are in the spreadsheet where responses are collected and are triggered on submission. This morning everything worked great. Then this afternoon when the last field in the form submitted was "No" the body of the email was blank. Now the body of the email is always blank. Is there any reason this code will no longer work?

function addFormula() {
  var sheet1 = SpreadsheetApp.getActiveSheet();
  var startRow1 = 2;
  var startColumn1 = 6;
  var numberRows1 = 1;
  var numberColumns1 = 9;
  var lastRow1 = sheet1.getLastRow();
  var sourceRange1 = sheet1.getRange(startRow1, startColumn1, numberRows1, numberColumns1);
  var destinationRange1 = sheet1.getRange(lastRow1, startColumn1, numberRows1, numberColumns1);
  sourceRange1.copyTo(destinationRange1);
};


var EMAIL_SENT = "EMAIL_SENT";

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = sheet.getLastRow();  // First row of data to process
  var numRows = 9;   // Number of rows to process
  // Fetch the range of cells 
  var dataRange = sheet.getRange(startRow, 1, numRows, 9)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var TimeStamp = row[0];  // First column
    var Name = row[1];       // Second column
    var Email = row[2];     // Third column
    var Leg1 = row[3];
    var CNeed = row[4];
    var Year = row[5];
    var CCode = row [6];
    var Number = row [7];
    var TripNumber = row [8];
    var emailSent = row [9];
    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      var subject = "Automated Message: Your Trip Number";
      MailApp.sendEmail(Email, subject, TripNumber);
      sheet.getRange(startRow + i, 10).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

from what I understand you are reading 9 rows below the last row... they are indeed empty... Am I wrong or do I miss something obvious ? (this is only a suggestion...) — Serge insas
Thanks for taking a look. It should be reading 9 columns in the last row with data. The email address and subject end up in the email, but sometimes the body of the email is blank. — user3285805
? when writing sheet.getRange(startRow, 1, numRows, 9) you are reading 9 rows by 9 columns starting from lastRow column A — Serge insas
That might be right. This is my first script... It seemed to work before, but now the mail app piece isn't working. — user3285805
try var startRow = 1 or 2 if you have headers and it should get better. — Serge insas

user3285805 user3285805 · Accepted Answer · 2014-02-08T16:49:05

I think I figured it out. The two functions were both executing on form submit and it was random which one would fire first. I combined the two functions into one and that looks like it solved the issue. Thanks Serge for helping out.

google apps script: mailapp stopped including text in body

1 Answers