CODEWITHSUNDEEP
htmlgoogle-apps-scriptgoogle-sheets
Pankaj Pandey
Pankaj Pandey

Reputation: 11

How to send HTML email with multiple tables from Google Sheets?

I have been working with Google Apps Script lately and found a way to send HTML email containing a table in Google Sheets https://spreadsheet.dev/send-html-email-from-google-sheets#:~:text=Search%20for%20Content%2DType%3A%20text,the%20document%20except%20this%20HTML. But I am not able to configure how I should be doing this with multiple tables (4 in my case) in a single mail only?

function sendEmail() {
 var stockData = getData();
 var body = getEmailText(stockData);
 
 MailApp.sendEmail({
   to: "[email protected]",
   subject: "Stock update",
   body: body
 });
}

function getEmailText(stockData) {
 var text = "";
 stockData.forEach(function(stock) {
   text = text + stock.name + "\n" + stock.ticker + "\n" + stock.price + "\n-----------------------\n\n";
 });
 return text;
}

/**
* @OnlyCurrentDoc
*/
function getData() {
 var values = SpreadsheetApp.getActive().getSheetByName("Data").getRange("Stocks").getValues();
 values.shift(); //remove headers
 var stocks = [];
 values.forEach(function(value) {
   var stock = {};
   stock.name = value[0];
   stock.ticker = value[1];
   stock.price = value[2];
   stocks.push(stock);
 })
 //Logger.log(JSON.stringify(stocks));
 return stocks;
}

The above code works perfectly well for a single table. What modifications can I make to have multiple tables? Any help will be much appreciated.

Upvotes: 0

Views: 1246

Answers (1)

Tanaike
Tanaike

Reputation: 201533

I believe your goal is as follows.

  • You want to retrieve the values with the font styles and background color of the cells from the named ranges and want to create the HTML table and send it as an email.
  • In your one table, only the header row has the specific background color. And the background color of other rows is #ffffff. This is from your sample images. image1, image2.

Modification points:

  • In your script, the values from the named range are not converted to the HTML. And, your script used one named range. I thought that this might be the reason for your issue.

  • In order to achieve your goal, how about the following flow?

    1. Retrieve the values from all named ranges including the font styles and the background colors.
    2. Crete HTML tables from the retrieved values.
    3. Send an email using the created HTML tables.

When this flow is reflected in a sample script, it becomes as follows.

Sample script:

In this sample script, in order to covert from the richtext to HTML, "RichTextApp" of a Google Apps Script library is used. So before you use this script, please install the library.

function myFunction() {
  const namedRanges = ["sampleNamedRange1", "sampleNamedRange2",,,]; // Please set your named ranges in this array.
  const emailAddress = "[email protected]"; // Please set the email address.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const { htmls, backgounds } = namedRanges.reduce((o, r) => {
    const range = ss.getRangeByName(r);
    o.htmls.push(RichTextApp.RichTextToHTMLForSpreadsheet({ range }));
    o.backgounds.push(range.getBackgrounds());
    return o;
  }, { htmls: [], backgounds: [] });
  const tables = htmls.map((v, i) => `table ${i + 1}<br><table border="1" style="border-collapse: collapse"><tr style="background: ${backgounds[i][0][0]};">${v.map(r => "<td>" + r.join("</td><td>")).join("</tr><tr>")}</tr></table>`);
  MailApp.sendEmail({ to: emailAddress, subject: "Stock update", htmlBody: tables.join("<br>") });
}
  • When this script is run, the values are retrieved from all named ranges by including the font styles and the background color of cells, and the retrieved values are converted to the HTML tables, and then, the HTML tables are sent as an email.

References:

Edit:

I updated RichTextApp. By this, the HTML table can be directly converted from the range of Spreadsheet to a HTML table. Ref When this is used, the sample script is as follows. Before you use this script, please install the library.

Sample script:

function myFunction() {
  const namedRanges = ["sampleNamedRange1", "sampleNamedRange2",,,]; // Please set your named ranges in this array.
  const emailAddress = "[email protected]"; // Please set the email address.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const htmlTables = namedRanges.map(r => RichTextApp.RangeToHTMLTableForSpreadsheet({range: ss.getRangeByName(r)}));
  const tables = htmlTables.map((t, i) => `table ${i + 1}<br>${t}`);
  MailApp.sendEmail({ to: emailAddress, subject: "Stock update", htmlBody: tables.join("<br>") });
}

Upvotes: 1

Related Questions