Correct script to send only the last row of data in Google Sheets

Question

The script I have is sending out the information in the format I want. The problem I have is that it is sending out each row as an indiviual email instead of only sending out the latest data. I only want the last row of data to be sent out.

function CustomEmail() {
    var sheet = SpreadsheetApp.getActiveSheet();
        lastRow = sheet.getLastRow();
        startrow= 2;
    var range = sheet.getRange("A2:Z1000");
    var UserData = range.getValues();
    for (i in UserData) {
       var row = UserData[i];
       var name = row[2];//market 
       var senderEmail = ''
       if (name === 'South') 
       {senderEmail = '[email protected]';}
       else if (name === 'West') 
       {senderEmail = '[email protected]';}
       else if (name === 'East') 
       {senderEmail = '[email protected]';}
       var AgentOwner = row[18];//Agent Owner
       var address = row[20];//Address
       var City = row[21];//City
       var State = row[22]//state
       var Incident = row[17]//incident type
       var Date = row[4]//date and time

  emailBody = "New Security Incident Report from: " +AgentOwner+ "\nAddress: " +address+ "\nCity: " +City+ "\nState: " +State + "\nIncident: " +Incident +  "\nDateTime:" +Date

  MailApp.sendEmail(senderEmail,"Security Incident Report", emailBody);
}

}

Answer 1

Try this -

function CustomEmail() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn())[0];
  Logger.log(row);
  var name = row[2]; //market
  var senderEmail = '';
  if (name === 'South') {
    senderEmail = '[email protected]';
  } else if (name === 'West') {
    senderEmail = '[email protected]';
  } else if (name === 'East') {
    senderEmail = '[email protected]';
  }
  var AgentOwner = row[18]; //Agent Owner
  var address = row[20]; //Address
  var City = row[21]; //City
  var State = row[22]; //state
  var Incident = row[17]; //incident type
  var Date = row[4]; //date and time

  emailBody =
    'New Security Incident Report from: ' +
    AgentOwner +
    '\nAddress: ' +
    address +
    '\nCity: ' +
    City +
    '\nState: ' +
    State +
    '\nIncident: ' +
    Incident +
    '\nDateTime:' +
    Date;

  MailApp.sendEmail(senderEmail, 'Security Incident Report', emailBody);
}

Edit: Go to script, paste new code, run the function, then in menu, View > Log and see if the row values are logged properly. If any issues with data indices, adjust them accordingly.