Advertisement

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

gmail google-apps-script google-sheets javascript

Tanaike

edited 03 May, 2022

matt_jones_1990

asked 02 May, 2022

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: "example@domain.com", ////////// <- input your email for testing
    subject: "Hello here are your highlights for today",
    htmlBody: message,
    // bcc: "example@domain.com",
    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>```

JavaScript
​x
 
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: "example@domain.com", ////////// <- input your email for testing
    subject: "Hello here are your highlights for today",
    htmlBody: message,
    // bcc: "example@domain.com",
    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>```
​

Advertisement

Answer

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: "example@domain.com", ////////// <- input your email for testing
    subject: "Hello here are your highlights for today",
    htmlBody: message,
    // bcc: "example@domain.com",
    noReply: true,

    inlineImages: {sampleImage: blob} // Added

  });
}

JavaScript
 
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: "example@domain.com", ////////// <- input your email for testing
    subject: "Hello here are your highlights for today",
    htmlBody: message,
    // bcc: "example@domain.com",
    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>

JavaScript
 
<!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)

Advertisement