Google Script with Time-Base trigger: email are sent, but without data

I am looking for guidance!

I have a Google Sheet that is populated from a Google Form. From those data (in the Google Sheet), I am doing some test, and I would like the Google Script to send me an email with some information.

For testing purpose, I am using a time based trigger set to Every Minutes; once I know it works, I will go back to once a day.

In the same spreadsheet, I have 2 sheets: INPUT and analysis.

  • The Google Form fills in the INPUT sheet.
  • On the analysis sheet, I do the test using from data that have a live link from INPUT to analysis (updating the data in the destination sheet analysis whenever changes are made in the source sheet INPUT).

The issue I am having:

  • when I manually execute the Google Script, the Execution Log shows correct values (see below), AND the email is sent the way I would like to, with the numerical values (see below):
Execution log:
5:41:37 PM  Notice  Execution started
5:41:48 PM  Info    23.0
5:41:52 PM  Info    300.0
5:41:52 PM  Info    150.0
5:41:52 PM  Info    No
5:41:52 PM  Info    Do Not Send EMail
5:41:55 PM  Notice  Execution completed

Email:
Oil Change due soon! Current time on oil is: 300 FH; remaining: 150 FH.
  • now, when I let the Google Script executes with the time based trigger, I got the email (yeah!), and the text in the body of the email, but WITHOUT numerical values (see below): the 300 and 150 are missing.
Email:
Oil Change due soon! Current time on oil is:  FH; remaining:  FH.

(For testing purpose, I am sending the email, regardless of the test condition).

I am not sure where the issue is. For me, if I manually run the Google Script and the output is correct, the automatic triggered execution should provide the same output…

I think I am not too far, but I can’t figure out what I am doing incorrectly.

Any help would be much appreciated!

Thank you very much!!

Here is my Google Script:

//@OnlyCurrentDoc
function MaintenanceEmail(e) {
  if(SpreadsheetApp.getActiveSheet().getSheetName() != "analysis") return;

  //find the last input, looking at the Date Submission column.
  var Avals = SpreadsheetApp.getActiveSheet().getRange("A1:A").getValues();
  var Alastend = Avals.filter(String).length;
  Logger.log(Alastend);

  //############################ OIL CHANGE
  var cellL1 = SpreadsheetApp.getActiveSheet().getRange("L"+ Alastend).getValue();  //Time on oil
  var cellM1 = SpreadsheetApp.getActiveSheet().getRange("M"+ Alastend).getValue();  //Time on oil remaining
  var cellN1 = SpreadsheetApp.getActiveSheet().getRange("N"+ Alastend).getValue();  //Oil due?
  Logger.log(cellL1);
  Logger.log(cellM1);
  Logger.log(cellN1);

  if(cellN1 == "Oil Change due"){
    Logger.log("Send EMail");
    MailApp.sendEmail({
      to: "[email protected]",
      subject: "Aircraft: Oil Change due soon",
      body: "Oil Change due soon! Current time on oil is: " + cellL1 + " FH; remaining: " + cellM1 + " FH."
    });
  } else {
    Logger.log("Do Not Send EMail");
    MailApp.sendEmail({
      to: "[email protected]",
      subject: "Aircraft: Oil Change due soon",
      body: "Oil Change due soon! Current time on oil is: " + cellL1 + " FH; remaining: " + cellM1 + " FH."
    });
  }
  //############################ OIL CHANGE
}

The trigger is the following (see picture).
enter image description here