Trying to send an email on last row of sheet-CodePudding

I am trying to send an email based on the last row with data in a google sheet. I am currently getting

undefined submitted a time off request From: undefined undefined To: undefined undefined

here is my script. any help would be greatly appreciated.

function sendEmail() {
  // Load the sheet that contains the birthdays.
  var sheet = SpreadsheetApp.getActive().getSheetByName("Responses");
  var row = sheet.getRange(sheet.getLastRow(),1,1,sheet.getLastColumn()).getValues();
  var name = row[1];
  var startDate = row[3];
  var startTime = row[4];
  var endDate = row[5];
  var endTime = row[6];
  var subject = name   " submitted a time off request";
  var recipient = "[email protected]";
  var body = name   " submitted a time off request \nFrom: "   startDate   " "   startTime   "\nTo: "   endDate   " "   endTime;
  MailApp.sendEmail(recipient, subject, body);
  }

CodePudding user response：

Try it this way:

function sendEmail() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Responses");
  const vs = sh.getRange(sh.getLastRow(), 1, 1, sh.getLastColumn()).getValues()[0];
  const name = vs[1];
  const startDate = vs[3];
  const startTime = vs[4];
  const endDate = vs[5];
  const endTime = vs[6];
  const subject = name   " submitted a time off request";
  const recipient = "[email protected]";
  const body = name   " submitted a time off request \nFrom: "   startDate   " "   startTime   "\nTo: "   endDate   " "   endTime;
  MailApp.sendEmail(recipient, subject, body);
}