Sheets to Docs to Email

Question

I have created a spreadsheet that contains quite a large amount of data.

The plan is to consolidate this data into a readable email to be sent out weekly, each specific row of data is its own email.

I tried going directly from sheets to email, but frankly it never quite looked right, plus the idea was to have a document template, where we could easily update the body without messing with code.

So I decided to write a email template in DOCS, set out a table, then have a script that copied the email template and updated the table with the row of data the script was looking at, then send it via email.

The code works great, but there is one little snag, the table never quite copies over to the email properly.

Below is are images of how the table is formatted in the email compared to the format in the template.

I just can not figure out how or why the format does not carry over.

I have also listed my code below, any help or advice on how I achieve the correct formatting would be appreciated.

UPDATE;

I have updated the question to show the code where we find the url of the document and convert to HTML,

var classArray=[];


   //get html from Doc
  var subject= row[30];
  var forDriveScope = DriveApp.getStorageUsed(); //needed to get Drive Scope requested
  var url = "https://docs.google.com/feeds/download/documents/export/Export?id="+newID+"&exportFormat=html";
  var param = {
    method      : "get",
    headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
    muteHttpExceptions:true,
  };
  var html = UrlFetchApp.fetch(url,param).getContentText();

  //docs uses css in the head, but gmail only takes it inline. need to move css inline.
  //DOES NOT HANDLE HEADER CLASSES (eg h1, h2).
  var headEnd = html.indexOf("</head>");
  //get everything between <head> and </head>, remove quotes
  var head = html.substring(html.indexOf("<head>")+6,headEnd).replace(/"/g,"");
  //split on .c# with any positive integer amount of #s
  var regex = /\.c\d{1,}/;
  var classes = head.split(regex);
  //get class info and put in an array index by class num. EG c4{size:small} will put "size:small" in classArray[4]
  var totalLength = 0;
  for(var i = 1; i < classes.length; i++){
    //assume the first string (classes[0]) isn't a class definition
    totalLength = totalLength + classes[i-1].length;
    var cNum = head.substring(totalLength+2,head.indexOf("{",totalLength)); //totallength+2 chops off .c, so get what's between .c and {
    totalLength = totalLength + 2 + cNum.length //add .c and the number of digits in the num
    classArray[cNum] = classes[i].substring(1,classes[i].indexOf("}")); //put what's between .c#{ and } in classArray[#]
  }

  //now we have the class definitions, let's put it in the html  
  html = html.substring(headEnd+7,html.indexOf("</html>")); //get everything between <html> and </html>
  var classMatch = /class=\"(c\d{1,} ){0,}(c\d{1,})\"/g
  //matches class="c# c#..." where c#[space] occurs any number of times, even zero times, and c#[no space] occurs after it, exactly once
  html = html.replace(classMatch,replacer); //replace class="c# c#..." with the definitions in classArray[#]

  //make the e-mail!
  GmailApp.sendEmail(row[31], subject, "HTML is not enabled in your email client. Sad face!", {
    htmlBody: html,
  });

function replacer(match){
  var csOnly = match.substring(7,match.length-1); //class=" has 7 chars, remove the last "
  var cs = csOnly.split(" "); //get each c#
  var ret = "style=\""
  for(var cCount = 0; cCount < cs.length; cCount++){
    ret = ret + classArray[cs[cCount].substring(1)];
  }
  return ret+"\"";
}


})

}

I think that most users find that they can achieve better formatting results with html — Cooper, May 27 '21 at 17:28
Unfortunately, from `the table never quite copies over to the email properly.` and `the format does not carry over.`, I cannot understand about your current issue. I apologize for my poor English skill. In order to correctly understand about your question, can you provide the sample input and output situation you expect, and provide the current situation with the issue as the images? — Tanaike, May 28 '21 at 00:11
Please consider to replace the current code by a [mcve] of the part that you are having problems (if I understood well, it's the part that reads the content from the HTML file result from exporting a Google document and use that to set the HTML content of an email). — Rubén, May 28 '21 at 00:56

score 0 · Answer 1 · answered May 29 '21 at 00:01

The comments in the code says that Gmail can only use inline styling. That was true several years ago but currently Gmail allows to have a style tag inside a head tag. Considering this, the script could be much more simple that the one included in the question.

Below there is a script showing a sample that sends a Google Document content as the HTML body of an email message.

/**
 * Get document as HTML 
 * Adapted from https://stackoverflow.com/a/28503601/1595451
 */
function getGoogleDocumentAsHTML(id) {
  var forDriveScope = DriveApp.getStorageUsed(); //needed to get Drive Scope requested
  var url = "https://docs.google.com/feeds/download/documents/export/Export?id=" + id + "&exportFormat=html";
  var param = {
    method: "get",
    headers: { "Authorization": "Bearer " + ScriptApp.getOAuthToken() },
    muteHttpExceptions: true,
  };
  var html = UrlFetchApp.fetch(url, param).getContentText();
  return html;
}

/**
 * Send the content of a Google Document as the HTML body of a email message 
 */
function sendEmail(){
  const url = /* add here the URL of your Google Document */;
  const id = url.match(/[^\/]{44}/)[0];
  const doc = getGoogleDocumentAsHTML(id);
  const head = doc
    .replace(/<meta[^>]+?>/g,'') // get rid of the meta tags
    .match(/<head.+?<\/head>/)[0];
  const body = doc.match(/<body[^>]+?>.+<\/body>/)[0];
  const htmlBody = [head,body].join('\n');
  MailApp.sendEmail({
    to: /*add here the recipient email address */,
    subject: /*add here the email subject */,
    htmlBody: htmlBody
  })
}

NOTE: You might want to clear the class of the body tag to avoid the margins set for it.

This does not work as I am using forEach to determine things such as document url, ID, email address, subject and so on. Unfortunately it seems too difficult for script to do a simple copy and paste keeping the formatting — Daniel Lawrie, Jun 01 '21 at 19:21
@DanielLawrie The code in the question doesn't include `forEach`. If you need help to use the code in my answer, I suggest you to create a new question, showing how are you using `forEach` — Rubén, Jun 01 '21 at 21:01
The forEach was in my original question before being advised to change my question to show the code that converts to HTML, I can change the question again to show the full code? — Daniel Lawrie, Jun 02 '21 at 09:17

Sheets to Docs to Email

1 Answers1