Export Google sheet Chart as an image

Question

Would you please help on how to export a chart to a image using google script.

I wrote this code but it doesn't work. I'm worrying that the API getAs is deprecated.

function TestEmailCharts(){
    var sheet = SpreadsheetApp.getActiveSheet();

  var charts = sheet.getCharts();

  if(charts.length!=0)
  {
  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/jpeg").setName("chartBlob"+i);
    emailBody= emailBody + "<img src='cid:chart"+i+"'><br>";
    emailImages["chart"+i]= chartBlobs[i];
  }

  MailApp.sendEmail({
    to: "[email protected]",
    subject: "test2",
    htmlBody: emailBody,
    inlineImages:emailImages});
  }
}

To reproduce the problem, create a Google spread sheet Create a simple chart with some data.

Add this code to scripts. Replace [email protected] with your email

Normally you should receive an email with chart image But the problem is that you will receive a email with black image.

Best regards.

Answer 1

Exporting a Google sheet chart as an image

By Email: First create the chart and then get its blob. Then send the blob as attachment.

function createPieChart(){
  var sheet = SpreadsheetApp.openById("<ID>").getSheetByName("<SheetName>");
  var chartBuilder = sheet.newChart() 
      .asPieChart() 
      .set3D()
      .addRange(sheet.getRange("A20").getDataRegion())
      .setPosition(16, 5, 0, 0)
      .setOption('title', "Total hour split in 2020");
  var blob = chartBuilder.build().getBlob();
  sendMail(blob);
}

function sendMail(img){
  MailApp.sendEmail({
    to: "[email protected]",
    subject: "test",
    htmlBody: "fair enough",
    attachments: [img]}); //If this gives you problems, replace img with img.getAs(MimeType.JPEG)
} 

To another spreadsheet (different from where the source data for the chart is):

Similar way, create the chart and then insert the blob as an image in the destination sheet:

function createPieChart(){
  var srcSheet = SpreadsheetApp.openById("<ID>").getSheetByName("<SheetName>"); 
  var destSheet = SpreadsheetApp.openById("<ID>").getSheetByName("<SheetName>");       
   var chartBuilder = srcSheet.newChart() 
      .asPieChart() 
      .set3D()
      .addRange(sheet.getRange("A20").getDataRegion())
      .setPosition(16, 5, 0, 0)
      .setOption('title', "Total hour split in 2020");
   var blob = chartBuilder.build().getBlob();
  destSheet.insertImage(blob, 1, 1); 
}