Emailing a chart from a Google spreadsheet with apps script

Question

I've search high and low for an answer but without an y luck. Got a Google spreadsheet which uses apps script to connect to a database to pull in some raw data into a spreadsheet. I then use various spreadsheet formulas to manipulate that data and then final create a chart.

My next challenge is that I want to be able to embed that chart into an email via apps script and send it as an HTML email..

Is this at all possible or should I start looking for some other solution?

Thanks!

Mikael

Answer 1

Here is the code I used to email charts. Please note that you need to give everyone with the link access to the spreadsheet. If not, you'll get an image in the email stating user is not signed in. (very annoying that there's no workaround to this)

function emailCharts(sheet,emails,emailSubject){
  var charts = sheet.getCharts();

  if(charts.length==0){
    MailApp.sendEmail({
      to: emails,
      subject: "ERROR:"+emailSubject,
      htmlBody: "No charts in the spreadsheet"});    
    return;
  }

  var chartBlobs=new Array(charts.length); 
  var emailBody="Charts<br>";
  var emailImages={};
  for(var i=0;i<charts.length;i++){
    chartBlobs[i]= charts[i].getAs("image/png").setName("chartBlob"+i);
    emailBody= emailBody + "<img src='cid:chart"+i+"'><br>";
    emailImages["chart"+i]= chartBlobs[i];
  }

  MailApp.sendEmail({
    to: emails,
    subject: emailSubject,
    htmlBody: emailBody,
    inlineImages:emailImages});

}

Answer 2

Make the spreadsheet Public and run the script. Modified version of the script pasted below with comments

enter code here function 
emailCharts(sheet,emails,emailSubject){
var targetspreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // Active        spreadsheet of the key file
var sheet = targetspreadsheet.getSheetByName('Sheet1'); // Change the sheet name 
var emailSubject = 'test';
var emails = 'test@test.com'; // your email ID
var charts = sheet.getCharts();


if(charts.length==0){
MailApp.sendEmail({
to: emails,
subject: "ERROR:"+emailSubject,
htmlBody: "No charts in the spreadsheet"});    
return;
}

var chartBlobs=new Array(charts.length); 
var emailBody="Charts<br>";
var emailImages={};
for(var i=0;i<charts.length;i++){
var builder = charts[i].modify();
builder.setOption('vAxis.format', '#');
var newchart = builder.build();
chartBlobs[i]= newchart.getAs('image/png');
emailBody= emailBody + "<p align='center'><img src='cid:chart"+i+"'></p>";
emailImages["chart"+i]= chartBlobs[i];
}

MailApp.sendEmail({
to: emails,
subject: emailSubject,
htmlBody: emailBody,
inlineImages:emailImages});
}

Answer 3

As the Charts Service appears to only be a partial implementation of EmbeddedChart used in SpreadsheetApp Service not all of the ChartTypes and options are implemented, meaning charts might render differently than they do in Google Sheets. The modified version of the previous answer below will mean charts are included in emails as images as they appear in Google Sheets. As this solution also uses an authentication token generated by the script the view permissions on the Google Sheet does not have to be changed.

// Based on https://stackoverflow.com/a/22200230/1027723
function emailChartUsingImageUrl(){
  const idt = SpreadsheetApp.getActive().getId();
  const sheet = SpreadsheetApp.getActiveSheet();
  const charts = sheet.getCharts();
  
  // setup some variables for our email
  const chartBlobs = new Array(); 
  const emailImages = {};
  let emailBody = "Charts<br>";
  
  // setup our call to fetch the chart image
  const token = ScriptApp.getOAuthToken(); // project requires https://www.googleapis.com/auth/spreadsheets scope
  const baseUrl = `https://docs.google.com/spreadsheets/d/${idt}/embed/oimg?access_token=${token}&disposition=ATTACHMENT&bo=false&filetype=png&oid=`;
  
  // for each chart fetch the download image as a blob and appended to our email body
  charts.forEach(function(chart, i){
    // NEW BIT
    const url = baseUrl + chart.getChartId();
    chartBlobs[i] = UrlFetchApp.fetch(url).getBlob();
    emailBody +=  "<p align='center'><img src='cid:chart"+i+"'></p>";
    emailImages["chart"+i]= chartBlobs[i];
  });
  
  // Send email with inline images
  MailApp.sendEmail({
    to: "me@example.com",
    subject: "Email Charts - get chart from image url",
    htmlBody: emailBody,
    inlineImages:emailImages});
}