E-mail sheet preview with formatting in HTML body + attach sheet

Question

I have applied this solution to e-mail the rows of a google sheet as part of the HTML body.

Unfortunately, it errors out because there is a limit set on the HTML body. The number of rows in my data is dynamic.

Limit Exceeded: Email Body Size. (line 209, file "SideBar")

Is there a way of getting around this? I would be OK with providing a preview of the rows, let's say 10 rows with all columns, on the HTML body and then providing a link to view the rest. Because the content on the sheet changes, the link should not be to that sheet. Instead I was thinking of saving a copy of the sheet as a new file on their own drive and linking to that. Another option is attaching an HTML file that has all the rows.

Here is what I currently have:

function emailBreakdown(emailUser, bodyAdd){
  SpreadsheetApp.getActiveSpreadsheet().toast('Please wait while information is refreshed.');
  if(emailUser == null){emailUser = 'xxxxx@yyyyyy.com'}
  if(bodyAdd == null){bodyAdd = 'Testing running of function on script editor'}
  var ss = SpreadsheetApp.getActive();
  var detailsSht = ss.getSheetByName("Details");
  var dataRange = detailsSht.getDataRange();
  var curDate = Utilities.formatDate(new Date(), "GMT+1", "MM/dd/yyyy");
  var subject = 'Summary - Exported Data ' + curDate;
  var body = '<br />---------------------------------------------------------------<br />You have received an export of a dataset from the <a href="google.com">Summary</a> dashboard. Please see below:<br /><br />' //provide link to the whole dashboard.

  convSheetAndEmail(dataRange, emailUser, body, bodyAdd, subject);
}

function convSheetAndEmail(rng, emailUser, body, bodyAdd, subject){
  var HTML = SheetConverter.convertRange2html(rng);
  MailApp.sendEmail(emailUser, subject, '', {htmlBody : bodyAdd + body + HTML});
}

@I'-'I Here is the error code: `Limit Exceeded: Email Body Size. (line 209, file "SideBar")` — DanCue, Jul 30 '18 at 14:59
I think I found a solution though. I'm going to post as an answer. I'm open to other's suggestions so I will not choose my own answer for the time being. — DanCue, Jul 30 '18 at 15:00

score 2 · Accepted Answer · answered Jul 30 '18 at 15:13

The following is code I've been able to assemble through further research. It works well and addresses my requests. Here is what it does:

Attaches a sheet. In this case an xls file. Chose this over HTML. To allow user to manipulate in excel if needed.
Provides a preview of 10 lines as HTML in the body of the e-mail.
Preview and attached file preserves format.

What it does not address:

Save file to user's personal drive.

Here it goes:

function emailBreakdown(emailUser, bodyAdd){
  SpreadsheetApp.getActiveSpreadsheet().toast('Please wait while information is refreshed.');

  //If running on script editor the variables will not be transferred. Assign values below:
  if(emailUser == null){emailUser = 'xxxxxx@yyyyyyy.com'}
  if(bodyAdd == null){bodyAdd = 'Testing running of function on script editor'}

  var ss = SpreadsheetApp.getActive();
  var detailsSht = ss.getSheetByName('Details');
  var dataRange = detailsSht.getRange('A1:FS11'); //For the preview we are only looking at the first 10 rows of data.
  var curDate = Utilities.formatDate(new Date(), 'GMT+1', 'MM/dd/yyyy');

  //Gather data to convert specific sheet to excel document so it can be attached to the e-mail
  var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();
  var sheetID = detailsSht.getSheetId().toString()
  var requestData = {'method': "GET", 'headers':{'Authorization':'Bearer ' + ScriptApp.getOAuthToken()}};
  var url = 'https://docs.google.com/spreadsheets/d/' + ssID + '/export?format=xlsx&id=' + ssID + '&gid=' + sheetID;
  var result = UrlFetchApp.fetch(url , requestData);  
  var contents = result.getContent();

  //Assemble E-mail components
  var subject = 'Summary - Exported Data ' + curDate;
  var body = bodyAdd + 
    '<br /><br /><hr style="border: none;border-top: 3px double #333;color: #333;overflow: visible;text-align: center;height: 5px;"><br />' +
    'You have received an export of a dataset from the <a href="https://docs.google.com/spreadsheets/d/' + ssID + '/">Summary</a> dashboard. Below is a preview of the dataset:<br /><br />'
  var afterBody = '<br /><br /><b>You can view the full dataset through the attached XLS file.</b>'

  convSheetAndEmail(ss, contents, dataRange, emailUser, body, afterBody, subject);
};

function convSheetAndEmail(ss, contents, rng, emailUser, body, afterBody, subject){
  var HTML = SheetConverter.convertRange2html(rng);

  //Send email
  MailApp.sendEmail(
    emailUser, 
    subject, 
    '',{
     htmlBody : body + HTML + afterBody,
     name: 'Full Data Export',
     attachments:[{fileName:'Export Data - ' + ss.getName() + '.xls', content:contents, mimeType:'application//xls'}]
    }
  );
};

Apart from the resource listed in the question, I also borrowed code from here.

E-mail sheet preview with formatting in HTML body + attach sheet

1 Answers1