CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
David Shaw
David Shaw

Reputation: 23

sendEmail() schedule trigger sends an empty email, but running from the script editor sends accurate data?

When I inserted query function into the sheet from which it pulls the data, I began to get blank emails when the trigger ran. When I go into the script editor and debug or run, I get the email the way it is programmed to run

Email I receive on scheduled send:

wrong

Email I receive when sent from Script Editor

desired Here is the code I am running:

function sendEmail() {

  //setup function
  var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var StartRow = 2;
  var aVals = ActiveSheet.getRange("A1:A").getValues();
  var aLast = aVals.filter(String).length;
  var RowRange = aLast - StartRow + 2;
  var WholeRange = ActiveSheet.getRange(StartRow, 1, RowRange, 10);
  var AllValues = WholeRange.getValues();

  var message = "";
  //iterate loop
  for (i in AllValues) {

    //set current row
    var CurrentRow = AllValues[i];

    //define column to check if sent (starts from "0" not "1")
    var hasTracking = CurrentRow[8];

    //if row has been sent, then continue to next iteration
    if (hasTracking !== "")
      continue;

    //set HTML template for information
    message +=
      "<p><b>Order: </b>" + CurrentRow[0] + "</p>" +
      "<p><b>Name: </b>" + CurrentRow[4] + "</p>" +
      "<p><b>Date Initiated: </b>" + CurrentRow[5] + "</p>" +
      //  "<p><b>Date Initiated: </b>" + Utilities.formatDate(CurrentRow[5], "GMT", "MM-dd-YYYY") + "</p>" +
      "<p><b>Gorgias Link: </b>" + CurrentRow[6] + "</p><br><br>";

    //set the row to look at
    var setRow = parseInt(i) + StartRow;

    //mark row as "sent"
    //ActiveSheet.getRange(setRow, 11).setValue("sent");
  }

  //define who to send grants to 
  var SendTo = "[email protected]";

  //set subject line
  var Subject = "Returns Needing Processed";


  //send the actual email  
  MailApp.sendEmail({
    to: SendTo,
    cc: "",
    subject: Subject,
    htmlBody: message,
  });
}

Upvotes: 2

Views: 58

Answers (1)

Marios
Marios

Reputation: 27348

Don't use .getActiveSheet() when you run the function via a trigger.

The reason is that .getActiveSheet() gets the active sheet of your file which can be any sheet.

Instead use .getSheetByName().

Replace:

var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

with:

var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');

adjust Sheet1 with your specific case.

Upvotes: 1

Related Questions