CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Terry Holt
Terry Holt

Reputation: 15

Script that will email data from Google Sheets

I created a script to email data from a particular range in a Google sheet, but the data runs together. What changes do I need to make to my script so the data is arranged in separate lines in the email body or a table format?

Data is in cells A5:B16 and is actually formulas that pull filtered data from other parts of the spreadsheet. The filter is a date based filter so the data that is returned in cells A5:B15 varies weekly. I want an email at the beginning of the week with the new data. The code "works", but is hard to read. What changed to I need to make to my script so the data is arranged in separate lines in the email body? Also, how do I format the dates?

function sendEmails() {
  var recipient = '[email protected]'; 
  var subject = "New Hires starting this week";  
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 5;
  var numRows = 10; 
  var dataRange = sheet.getRange(startRow, 1, numRows, 2);
  var message = dataRange.getValues();
  MailApp.sendEmail(recipient, subject, message);
}

This is how the email looks:

Name,End Date,Jane Doe,Fri Sep 27 2019 00:00:00 GMT-0400 (EDT),John Hancock,Fri Sep 27 2019 00:00:00 GMT-0400 (EDT),Joe Smith,Fri Sep 27 2019 00:00:00 GMT-0400 (EDT),,,,,,,,,

Upvotes: 1

Views: 214

Answers (1)

Cooper
Cooper

Reputation: 64062

function sendEmails() {
  var recipient = '[email protected]'; 
  var subject = "New Hires starting this week";  
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 5;
  var numRows = 10; 
  var dataRange = sheet.getRange(startRow, 1, numRows, 2);
  var message = dataRange.getValues();
  var s='';
  for(var i=0;i<message.length;i++) {
    var dt=Utilities.formatDate(new Date(message[i][1]),Session.getScriptTimeZone(),"MM-dd-yyyy");
    s+=Utilities.formatString('%s: %s\n',message[i][0],dt);
  }
  GmailApp.sendEmail(recipient, subject, s);
}

Utilities.formatString()

I'm assuming you have the Meta Data in column1 and the data in column 2. If that's not correct let me know it's probably easy to fix.

Upvotes: 1

Related Questions