CODEWITHSUNDEEP
javascripthtmlgoogle-apps-scriptgoogle-sheetsdatetime-format
Ben Huggins
Ben Huggins

Reputation: 9

How to control format of date read from spreadsheet, and display as table in email?

complete coding newbie here so excuse my ignorance.

We have a google spreadsheet that contains an e-mail address in B2, and a bunch of data between cells A4 & DX (depending on size of data) and I have cobbled together some script to get an e-mail to send out for every tab:

function sendEmail() {

var ss = SpreadsheetApp.getActive();
for(var n in ss.getSheets()){// loop over all tabs in the spreadsheet
var sheet = ss.getSheets()[n];// look at every sheet in spreadsheet
var lastRow = sheet.getLastRow();
var to = sheet.getRange('B2').getValue();
var data = sheet.getRange('A4:D' + lastRow).getValues();
var body = '';
for( var row in data ) {
for( var col in data[row] ) {
body += data[row][col] + '\ ,';
  }
body += '\n';}
MailApp.sendEmail(to, 'Your Holiday Extras Company Credit Card Statement', body);
}
}

This works OK, but sends out an e-mail like this:

Cardholder ,Merchant ,Date Occurred ,Amount ,
A CLOSE ,test 11 ,Thu Jun 25 2015 00:00:00 GMT+0100 (BST) ,8.4 ,
A CLOSE ,test 12 ,Tue Jun 30 2015 00:00:00 GMT+0100 (BST) ,1.5 ,
A CLOSE ,test 13 ,Tue Jun 30 2015 00:00:00 GMT+0100 (BST) ,17 ,
A CLOSE ,test 14 ,Tue Jun 30 2015 00:00:00 GMT+0100 (BST) ,24.55 ,
A CLOSE ,test 15 ,Tue Jun 30 2015 00:00:00 GMT+0100 (BST) ,2.58 ,
A CLOSE ,test 16 ,Thu Jul 02 2015 00:00:00 GMT+0100 (BST) ,133.2 ,

...which looks quite terrible, though does contain the correct data.

Is there anyway, using the script above as a shell, to format the e-mail body into a table to show the above data, and if not, how would I go about changing the format of the data to be just dd/mm/yy as opposed to including time etc. and also change the formatting to stop random spaces appearing after every field?

Upvotes: 0

Views: 131

Answers (1)

OrangeBender
OrangeBender

Reputation: 76

Okay the only way to format the email with a table is to use the HTMLbody option. As for the date you have two option either you turn them to string and remove what you want or go as Zig said and use javascript date formats.

I modified your code a bit to have the table and used the string format to modify the text..

function sendEmail() {
  var ss = SpreadsheetApp.getActive();
  for(var n in ss.getSheets())  // loop over all tabs in the spreadsheet
  {
    var sheet = ss.getSheets()[n];// look at every sheet in spreadsheet
    var lastRow = sheet.getLastRow();
    var to = sheet.getRange('B2').getValue();
    var headers = sheet.getRange('A4:D4').getValues()[0];
    var data = sheet.getRange('A5:D' + lastRow).getValues();
    var htmlmessage = "<HTML><BODY>"
     +"<P> This is a new paragraphe before the table</P>"
     +"<BR> This is a new line before the table</BR>"
     +"<P><TABLE border='0' table-layout:auto;>"
     +"<TR>";
    for (var header in headers)
    {
      htmlmessage += "<TD><Strong>"+headers[header]+"</strong></TD>";
    }
    htmlmessage += "</TR>";
    for (var row in data)
    {
      htmlmessage += "<TR>";
      for (var col in data[row])
      {
        htmlmessage += "<TD>" + data[row][col].toString().replace("00:00:00 GMT+0100 (BST)","") + "</TD>"
      }
      htmlmessage += "</TR>";
    }
    MailApp.sendEmail({
      to: to,
      subject: 'Your Holiday Extras Company Credit Card Statement',
      htmlBody: htmlmessage});
  }
}

you can also replace this 

    for (var row in data)
    {
      htmlmessage += "<TR>";
      for (var col in data[row])
      {
        htmlmessage += "<TD>" + data[row][col].toString().replace("00:00:00 GMT+0100 (BST)","") + "</TD>"
      }
      htmlmessage += "</TR>";
    }

by this if you want to use the javascript data format.

  for (var col in data[row])
  {
    var temp = data[row][col]
    if (col == 2)
    {
      temp = Utilities.formatDate(temp, "GMT", "MM/dd/YYYY");
    }
    htmlmessage += "<TD>" + temp + "</TD>"
  }
  htmlmessage += "</TR>";
}

result :

Cardholder  Merchant    Date Occurred   Amount
 A CLOSE    Test 12        6/30/2015    1.5
 A CLOSE    Test 13        6/30/2015    17
 A CLOSE    Test 14        6/30/2015    24.55
 A CLOSE    Test 15         2/7/2015    2.58
 A CLOSE    Test 16         2/7/2015    133.2

I hope this helps. If you have any question, I will answer in the comments.

  1. To add an initial sentence you'll need to add a section before the

    +"<TABLE border='1' table-layout:auto;>"

ex :

+"<P> This is a new paragraphe before the table</P>"
+"<BR> This is a new line before the table</BR>"
  • I'm not an expert HTML write, so for more advance formating, you can always search for html formating.
  1. To put somthing in bold you can simply add "strong" before and after the section you want to bold.

ex:

htmlmessage += "<TD><strong>"+headers[header]+"</strong></TD>";
  1. If you want to remove or modify the border settings of the table you can always increase or lower the number next to "border=1".

I have modified the above code with the modification you specified.

Upvotes: 1

Related Questions