javascript google-apps-script google-sheets gmail

Pass Google Apps Script (GAS) - Charts Service chart to HTML Template within GAS

https://developers.google.com/apps-script/reference/charts

I'd simply like to pass the chart object generated from Charts Service within the HTML template (where I choose - not as an attachment, but within the template) to email. I have had success attaching the chart object, but I'd like to integrate this within the email itself/embed

Here is the Google Apps Script code (using the link above code as well) ↓↓↓ */

var data = Charts.newDataTable()
    .addColumn(Charts.ColumnType.STRING, 'Month')
    .addColumn(Charts.ColumnType.NUMBER, 'In Store')
    .addColumn(Charts.ColumnType.NUMBER, 'Online')
    .addRow(['January', 10, 1])
    .addRow(['February', 12, 1])
    .addRow(['March', 20, 2])
    .addRow(['April', 25, 3])
    .addRow(['May', 30, 4])
    .build();

var chart = Charts.newAreaChart()
    .setDataTable(data)
    .setStacked()
    .setRange(0, 40)
    .setTitle('Sales per Month')
    .build();

var htmlOutput = HtmlService.createHtmlOutput().setTitle('My Chart').setWidth(1000).setHeight(1000);
var imageData = Utilities.base64Encode(chart.getAs('image/png').getBytes());
var imageUrl = "data:image/png;base64," + encodeURI(imageData);
htmlOutput.append("<div style=\"text-align: center\"><strong>I can generate the chart in a Modeless Dialog below here ↓↓↓<br><br>However, I'd like to place this chart within the HTML template file and send via email with Google Apps Script<br/></strong><br></div>");
htmlOutput.append("<div style=\"text-align: center\"> <img border=\"1\" src=\"" + imageUrl + "\"></div>");

var templ = HtmlService.createTemplateFromFile('html'); // HTML file to add 

var message = templ.evaluate().getContent()
var info = "I can generate the chart in this Modeless Dialog Box"
SpreadsheetApp.getUi().showModelessDialog(htmlOutput,info)


MailApp.sendEmail({
    to: "[email protected]", ////////// <- input your email for testing
    subject: "Hello here are your highlights for today",
    htmlBody: message,
    // bcc: "[email protected]",
    noReply:true
   });  
}

/////////////////////////////////////////////////////////////////////////////////

// Here is the HTML: ↓↓↓  

<!DOCTYPE html>
<HTML>
    <head>
    <base target="_top">
        <style type="text/css">
           div {
         text-align: center;
       }
        </style> 
    </head>
        <body>
            <h2>I would like the generated Chart below here in an email ↓↓↓:</h2>
            <p>
            I would like the generated Chart to be above here in an email ↑↑↑ 
            </p>   
        </body>
</html>```

Solution

Unfortunately, it seems that in the current stage, Gmail cannot directly include the image data as the URL. So, in your situation, how about showing the image as the inline image? When your script is modified it becomes as follows.

Modified script:

Google Apps Script side:

function sample() {
  var data = Charts.newDataTable()
    .addColumn(Charts.ColumnType.STRING, 'Month')
    .addColumn(Charts.ColumnType.NUMBER, 'In Store')
    .addColumn(Charts.ColumnType.NUMBER, 'Online')
    .addRow(['January', 10, 1])
    .addRow(['February', 12, 1])
    .addRow(['March', 20, 2])
    .addRow(['April', 25, 3])
    .addRow(['May', 30, 4])
    .build();
  var chart = Charts.newAreaChart()
    .setDataTable(data)
    .setStacked()
    .setRange(0, 40)
    .setTitle('Sales per Month')
    .build();
  var htmlOutput = HtmlService.createHtmlOutput().setTitle('My Chart').setWidth(1000).setHeight(1000);

  var blob = chart.getAs('image/png'); // Added
  var imageData = Utilities.base64Encode(blob.getBytes()); // Modified

  var imageUrl = "data:image/png;base64," + encodeURI(imageData);
  htmlOutput.append("<div style=\"text-align: center\"><strong>I can generate the chart in a Modeless Dialog below here ↓↓↓<br><br>However, I'd like to place this chart within the HTML template file and send via email with Google Apps Script<br/></strong><br></div>");
  htmlOutput.append("<div style=\"text-align: center\"> <img border=\"1\" src=\"" + imageUrl + "\"></div>");
  var templ = HtmlService.createTemplateFromFile('html'); // HTML file to add 
  var message = templ.evaluate().getContent();
  var info = "I can generate the chart in this Modeless Dialog Box"
  SpreadsheetApp.getUi().showModelessDialog(htmlOutput, info);
  MailApp.sendEmail({
    to: "[email protected]", ////////// <- input your email for testing
    subject: "Hello here are your highlights for today",
    htmlBody: message,
    // bcc: "[email protected]",
    noReply: true,

    inlineImages: {sampleImage: blob} // Added

  });
}

HTML side:

<!DOCTYPE html>
<HTML>

<head>
  <base target="_top">
  <style type="text/css">
    div {
      text-align: center;
    }
  </style>
</head>

<body>
  <h2>I would like the generated Chart below here in an email ↓↓↓:</h2>

  <img src="cid:sampleImage"> <!-- added -->

  <p>
    I would like the generated Chart to be above here in an email ↑↑↑
  </p>
</body>
</html>

Reference:

sendEmail(message)