function SendEmail(){ var emailRange = SpreadsheetApp.getActiveSpreadsheet() .getSheetByName("HLP REQUESTS").getRange("K:K"); var emailAddress = emailRange.getValues() .flat() // convert a 2d array into a flat array .filter(String) // remove empty elements from the array .pop(); // get the last element from the array var message = 'A request has been submitted for professional learning related to an HLP you champion. Please check the Design Team Notes document in case follow-up is required.'; var subject = 'HLP Request for Professional Learning'; MailApp.sendEmail(emailAddress, subject, message); }