Import Report from Sheet to Email using Google Apps Script

Question

I have report in google sheet in which data is populated regularly. I want to send this Google sheet report to specific email address using google apps script. I have tried different things to capture that report into email body so that receiver can receive his report in the email but could not find any solution to send it in the form of email body or image. Here is a link to my report sheet:

https://docs.google.com/spreadsheets/d/11g_a4lgcpBftBxd52nVpGGnlmC78bWeUOg7xk5GejWw/edit?usp=sharing

I have a following code which extracts any charts from sheet and import them into mail. But I want the whole sheet to be exported into mail but not just charts. Any help would be appreciated.

function email(sheet,toMail,msg){

var charts = sheet.getCharts();

var slides= SlidesApp.create("sample")
var slide = slides.getSlides()[0]

var emailImages={};
var chartBlobs= new Array(charts.length)
var emailBody="";

for (var i=0;i<charts.length;i++)
  {
   var image=slide.insertSheetsChartAsImage(charts[i]);
   chartBlobs[i]= image.getAs("image/png").setName("chartBlob"+i);
   emailBody =emailBody + "<p align='left'><imag src='cid:chart" +i+"'></p>
   emailImages["chart"+i]=chartBlobs[i];
  }

   MailApp.sendEmail({
             to:toMail,
             subject:"ABC",
             htmlBody:emailBody,
             inlineImages:emailImages});


             DriveApp.getFileById(slides.getId()).setTrashed(true);
}

Would converting the sheet to pdf be a suitable solution for you? — ziganotschka, Feb 08 '21 at 09:24
This is the last option. But ideal situation would be to get it into email body. — , Feb 08 '21 at 13:43

score 0 · Answer 1 · answered Feb 09 '21 at 08:54

Making a Files: get request will return you the following exportLinks

For a Google Docs File:

{
 "exportLinks": {
  "application/rtf": "https://docs.google.com/feeds/download/documents/export/Export?id=1qvfrIFTYZ3lL0pkP6VTEG_YdThY0UeB-Oyjfl48sL5o&exportFormat=rtf",
  "application/vnd.oasis.opendocument.text": "https://docs.google.com/feeds/download/documents/export/Export?id=1qvfrIFTYZ3lL0pkP6VTEG_YdThY0UeB-Oyjfl48sL5o&exportFormat=odt",
  "text/html": "https://docs.google.com/feeds/download/documents/export/Export?id=1qvfrIFTYZ3lL0pkP6VTEG_YdThY0UeB-Oyjfl48sL5o&exportFormat=html",
  "application/pdf": "https://docs.google.com/feeds/download/documents/export/Export?id=1qvfrIFTYZ3lL0pkP6VTEG_YdThY0UeB-Oyjfl48sL5o&exportFormat=pdf",
  "application/epub+zip": "https://docs.google.com/feeds/download/documents/export/Export?id=1qvfrIFTYZ3lL0pkP6VTEG_YdThY0UeB-Oyjfl48sL5o&exportFormat=epub",
  "application/zip": "https://docs.google.com/feeds/download/documents/export/Export?id=1qvfrIFTYZ3lL0pkP6VTEG_YdThY0UeB-Oyjfl48sL5o&exportFormat=zip",
  "application/vnd.openxmlformats-officedocument.wordprocessingml.document": "https://docs.google.com/feeds/download/documents/export/Export?id=1qvfrIFTYZ3lL0pkP6VTEG_YdThY0UeB-Oyjfl48sL5o&exportFormat=docx",
  "text/plain": "https://docs.google.com/feeds/download/documents/export/Export?id=1qvfrIFTYZ3lL0pkP6VTEG_YdThY0UeB-Oyjfl48sL5o&exportFormat=txt"
 }
}

For a Google Sheets File:

{
 "exportLinks": {
  "application/x-vnd.oasis.opendocument.spreadsheet": "https://docs.google.com/spreadsheets/export?id=1wGIfU1L9__Tt1JIxfkCYqUiUH1RSIt3P7KsMsuVUYIc&exportFormat=ods",
  "text/tab-separated-values": "https://docs.google.com/spreadsheets/export?id=1wGIfU1L9__Tt1JIxfkCYqUiUH1RSIt3P7KsMsuVUYIc&exportFormat=tsv",
  "application/pdf": "https://docs.google.com/spreadsheets/export?id=1wGIfU1L9__Tt1JIxfkCYqUiUH1RSIt3P7KsMsuVUYIc&exportFormat=pdf",
  "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet": "https://docs.google.com/spreadsheets/export?id=1wGIfU1L9__Tt1JIxfkCYqUiUH1RSIt3P7KsMsuVUYIc&exportFormat=xlsx",
  "text/csv": "https://docs.google.com/spreadsheets/export?id=1wGIfU1L9__Tt1JIxfkCYqUiUH1RSIt3P7KsMsuVUYIc&exportFormat=csv",
  "application/zip": "https://docs.google.com/spreadsheets/export?id=1wGIfU1L9__Tt1JIxfkCYqUiUH1RSIt3P7KsMsuVUYIc&exportFormat=zip",
  "application/vnd.oasis.opendocument.spreadsheet": "https://docs.google.com/spreadsheets/export?id=1wGIfU1L9__Tt1JIxfkCYqUiUH1RSIt3P7KsMsuVUYIc&exportFormat=ods"
 }
}

In other words: There is no inbuilt functionality to export a Google Sheets file to HTML

This is the reason why you can email a Docs files from the UI as HTML, but not a Google Sheets.

Conclusion:

You need to build an html table yourself to get the sheet content into your email body.

Here is a sample of how you can do it.

Alternatively - consider converting the whole sheet content to an image, since images can be isnerted into the email body.

Thank you for your answer. It really helped me to look for the possible solutions. After some digging I found [this](https://stackoverflow.com/questions/62324006/google-apps-script-send-google-sheet-as-email-attachment?noredirect=1&lq=1) — , Feb 09 '21 at 15:23

Import Report from Sheet to Email using Google Apps Script

1 Answers1

Making a Files: get request will return you the following exportLinks