How do I use Google Scripts Sheet to put a spreadsheet table into an email

Question

In essence I'd like the script to "copy paste" a section of a row and column directly into an email. I've learned a bit about adding HTML tables into an email, but I'm still unable to simply take the data from a spreadsheet and put it into an email using a script so that the data is presented exactly the way it looks in the spreadsheet.

For example here is the spreadsheet. How do I get the highlighted table to appear in this format into an email (I can do it by manually copy and pasting but is there a code using the JavaScript of Google Scripts?).

Tedinoz Tedinoz · Accepted Answer · 2019-08-01T02:28:34

You want to take a range of data from a Google sheet, convert it to an html table, and include it in an email. I have assumed that you know how to send an email - both html and non-html; the following answer focuses on creating the html table from the spreadsheet. The key challenge is that the css must be included inline.

It is worth noting that there are two date fields "9/20" and "9/21". If the standard getValues() is used, these values are retrieved as full timestamp values. However, by using getDisplayValues() the values are returned as displayed, and they can be included in the table without having to apply date formats.

The code generate a variable messagetable. This can be includes in the email as the message, or it can be incorporated into the message.

function SO5722046102() {
  // setup spreadheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  // define table
  var tablerange = sheet.getRange(4,1,4,3); // get the range
  //Logger.log("the range is "+tablerange.getA1Notation());

  // get the values - Display Values
  var tabledata = tablerange.getDisplayValues();

  // build the opening element of the html table
  var messagetable = '<table style="border-collapse:collapse;border: 1px solid black;"><tbody>';
 // Logger.log("messagetable = "+messagetable);

  // loop through the rows and build up each row of the table
  for (i= 0; i< 4;i++){

    messagetable+='<tr style="border: 1px solid black;"><td style="border: 1px solid black;">'+tabledata[i][0]+"</td>"+'<td style="border: 1px solid black;">'+tabledata[i][1]+"</td>"+'<td style="border: 1px solid black;">'+tabledata[i][2]+"</td></tr>";

  }

  // add the closing html
  messagetable=messagetable+"</tbody></table>";
  // Logger.log("DEBUG: the table = "+messagetable);

  // add code for sending email and include the table as either the message, or incorporate it into the message
}

    <table style="border-collapse:collapse;border: 1px solid black;"><tbody>
<tr style="border: 1px solid black;"><td style="border: 1px solid black;">DATE</td><td style="border: 1px solid black;">09/20</td><td style="border: 1px solid black;">09/21</td></tr>
<tr style="border: 1px solid black;"><td style="border: 1px solid black;">AMOUNT</td><td style="border: 1px solid black;">115</td><td style="border: 1px solid black;">115</td></tr>
<tr style="border: 1px solid black;"><td style="border: 1px solid black;">NOTES</td><td style="border: 1px solid black;"></td><td style="border: 1px solid black;"></td></tr>
<tr style="border: 1px solid black;"><td style="border: 1px solid black;">TOTAL</td><td style="border: 1px solid black;"></td><td style="border: 1px solid black;">230</td></tr>
</tbody></table>

Spreadsheet screenshot

Table screenshot

How do I use Google Scripts Sheet to put a spreadsheet table into an email

1 Answers