Google Sheets content to email (lay out)

Question

I have a list of events in a google sheet. It's a sheet with 4 columns of data, and a few dozen rows. When events are upcoming in the next week, I want to send out an email automatically.

I have a tab in the google sheet which filters for upcoming events. So I only need to copy this data to an e-mail.

It works with this code :

var first = ss.getSheetByName("Sheet2");
first.activate();

var sheet = SpreadsheetApp.getActiveSheet();
var data = first.getDataRange().getValues();

// Generate content 

var body = [];
for (var x = 0; x <data.length; x++)
body.push(data[x] + "\n");
body.push("/n" + "That's all for next week");

MailApp.sendEmail(mailaddress, subject, "Upcoming events" +"\n" +"\n" + body)

The problem is that it looks terrible. In the mail it looks like this:

CAR,Thu Mar 24 2016 08:00:00 GMT+0100 (CET),FY,c
,INBEV,Wed Apr 27 2016 09:00:00 GMT+0200 (CEST),AGM,c

The date information is way too long, and the cells are not with spacing between. I would like to see something like this:

Car * Mar 24 * FY (c)
INBEV * Apr 27 * AGM (c)

I have very basic knowledge of javascript and google-apps script. What would be the way to proceed? How to I adjust the output?

Edit It works fine after using the suggestion by Sandy Good. Except for the second row to start with a comma (,). It's not part of the value, so I can't chop it off with substring. Anybody knows why a second (and third, fourth...) row from a sheet starts wih a comma?

edit2

This is what I am currently using. It only gets the last line from the sheet, as I switched from array to text as input. The body is not really assembled, the content is replaced all the time before it finally sends.

function myFunction() {
var mail = 'my mail address'
var ss = SpreadsheetApp.openById('ID code sheet');

// first is the active sheet
var first = ss.getSheetByName("Sheet2");
first.activate();

var sheet = SpreadsheetApp.getActiveSheet();
var data = first.getDataRange().getValues();

// Generate content for body of email

var body = "",
  thisRow,
  column_1_Value,
  column_2_Value,
  column_3_Value,
  column_4_Value;

for (var x = 0; x <data.length; x++) {
thisRow = data[x];
column_1_Value = thisRow[0];
column_2_Value = thisRow[1];
column_3_Value = thisRow[2];
column_4_Value = thisRow[3];

column_2_Value = Utilities.formatDate(new Date(column_2_Value), "GMT", "MMMMM dd");

body = **body** + column_1_Value + " * " +
       column_2_Value + " * " + 
       column_3_Value + " (" +
       column_4_Value + ")" + "\n"

}

MailApp.sendEmail("mail address to receive mail", "upcoming events", "Upcoming events" +"\n" +"\n" + body + "\n" + "That's all for next week")


}

Alan Wells Alan Wells · Accepted Answer · 2016-03-21T16:30:59

You can use the Utilities.formatDate() method to format the date. You can use JavaScript string methods, to change the case. Here is an example, it may not be a complete solution, but hopefully it shows how you can accomplish what you need:

function test(){
  var body = "",
      allRowsData = "",
      thisRow,
      column_1_Value,
      column_2_Value,
      column_3_Value,
      column_4_Value;

  for (var x = 0; x <data.length; x++) {
    thisRow = data[x];
    column_1_Value = thisRow[0];
    column_2_Value = thisRow[1];
    column_3_Value = thisRow[2];
    column_4_Value = thisRow[3];

    column_1_Value = column_1_Value.slice(0,1) + column_1_Value.slice(1).toLowerCase();;//Change to Upper and lower case
    column_2_Value = Utilities.formatDate(new Date(column_2_Value), timeZone, "MMM dd");

    body = "";//Reset on every loop
    body = column_1_Value + " * " +
           column_2_Value + " * " + 
           column_3_Value + " * " +
           column_4_Value + " * " +
           "\n" + "/n" + "That's all for next week";

    allRowsData = allRowsData + body;
  };

};

Google Sheets content to email (lay out)

1 Answers