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

Question

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.

I have to apologize for my poor English skill. Unfortunately, I cannot imagine your goal from your scriipt and how I should be doing this with multiple tables (4 in my case) in a single mail only?. Can you provide the sample output situation as an image? — Tanaike
– Tanaike, Commented Oct 19, 2021 at 1:40
The issue is that I have more than one table in a google sheet and I want to get all the tables in HTML as an e-mail. The above code (functions) is for a single table only. How should I modify the code so that it works for 3 more tables? I hope this clears your doubt. — Pankaj Pandey
– Pankaj Pandey, Commented Oct 19, 2021 at 1:46
Thank you for replying. I have to apologize for my poor English skill, again. Unfortunately, I cannot still imagine your expected output situation about 3 more tables. But I would like to try to understand it. When I could correctly understand it, I would like to think of the solution. — Tanaike
– Tanaike, Commented Oct 19, 2021 at 1:50
For demo I have an image. Head over here-> imgur.com/a/9jwHMHK and you'll understand what I'm trying to say. I want to get both of these tables in HTML format as email. — Pankaj Pandey
– Pankaj Pandey, Commented Oct 19, 2021 at 2:18
Thank you for replying. I have to apologize for my poor English skill again. When I saw your image, I can find 2 tables (B2:E5, C8:F11) in a sheet in a Google Spreadsheet. In your actual situation, about 4 in my case in your question, where can I find 2 more tables? And, in your script, it seems that the named range might be used. Is my understanding correct? If my understanding is correct, your 4 tables can be retrieved by the named ranges? And, your script removes the header row of the table. How about this? Can I ask you about the output you expect using your sample image? — Tanaike
– Tanaike, Commented Oct 19, 2021 at 3:06

Tanaike · Accepted Answer · 2021-10-20 08:41:26Z

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>") });
}

Collectives™ on Stack Overflow

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

1 Answer 1

Modification points:

Sample script:

References:

Edit:

Sample script:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Sample script:

References:

Edit:

Sample script:

Comments

Your Answer

Sign up or log in

Post as a guest

Related