How to debug an email-sending Google Script that appears to have stopped working?

Question

This was working yesterday and is no longer working today.

The function of this script is to take info from a Google Form that populates a Google Sheet, then email a copy to the user as well as company email. (listed as fake email below)

Email includes Header of populated cells as well as content and omits columns that were not populated. Subject of email includes: 3 required fields from form.

Triggers are setup based on submit form. I'm fine with a re-write as well. This script is a few years old now.

function email()
{
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Master");  
// this used to sort sheet first - sheet.sort(1, true);
var Response = new Array(50);
var Header =new Array(50);
var j=0;
var row=sheet.getLastRow();
var userEmail=sheet.getRange(row,4,1,1).getValue();
var str="";
var str1="";
for(var i=1; i<=sheet.getLastColumn();i++)
{
if(sheet.getRange(row,i,1,1).getValue()!="")
{
  Response[j]=sheet.getRange(row,i,1,1).getValue();
  Header[j]=sheet.getRange(1,i,1,1).getValue();

Logger.log(Response[j]);
Logger.log(Header[j]);
   str = str + Header[j] + ":\t" + Response[j]+ '\n';
  if(Header[j]=="Full Name"||Header[j]=="Vehicle type"||Header[j]=="Serial Number")
    str1 = str1 + Response[j]+ ':\t';
  if(Header[j]=="Vehicle type")
    str=str+'\n';

j++;
   sheet.getRange(sheet.getLastRow(),4,1,1).setNote("emailed");
}
}

Logger.log(str);
GmailApp.sendEmail("[email protected]" + "," + userEmail, str1, str);  
}

I'm not familiar with Google Script, but I suspect Logger.log would be useful to you here. What logs are you getting from that when this script triggers? Have you determined that the script indeed is running? and does the loop iterate over all the cases you expect? If you can add that info to your question, that would be helpful for readers generally, I should think. — halfer
Bit of a guess as well: when more than one email is sent, is a comma the correct separator? I dimly recall seeing a semi-colon being used here, so perhaps try editing GmailApp.sendEmail() to be a single, hard-wired email address, to see if that gets anything going. — halfer

Cooper Cooper · Accepted Answer · 2017-10-24T21:15:28

Try this:

function email()
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Master");  
  var row=sheet.getLastRow();
  var userEmail=sheet.getRange(row,4,1,1).getValue();
  var str="";
  var str1="";
  for(var i=1; i<=sheet.getLastColumn();i++)
  {
    if(sheet.getRange(row,i,1,1).getValue()!="")
    {
      var r=sheet.getRange(row,i,1,1).getValue();
      var h=sheet.getRange(1,i,1,1).getValue();
      Response.push(r);
      Header.push(h);
      str+=h + ":\t" + r + '\n';
      if(h=="Full Name"||h=="Vehicle type"||h=="Serial Number")
      {
        str1+= r + ':\t';
      }
      if(h=="Vehicle type")
      {  
        str+='\n';
      }
      sheet.getRange(sheet.getLastRow(),4,1,1).setNote("emailed");
    }
  }
  Logger.log(str);
  GmailApp.sendEmail(userEmail, str1, str {cc:[email protected]});  
}

How to debug an email-sending Google Script that appears to have stopped working?

1 Answers