Skip to content

Getting specific charts from array in google script

I’m having trouble getting specific charts from a spreadsheet using an array. The code below is an extract of a function that sends an email which includes charts:

var sheetSummary = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Summary");
var charts = sheetSummary.getCharts();
var chartBlobs = new Array(charts.length);
var emailImages = {};
for(var i=0; i < charts.length; i++){
  chartBlobs[i] = charts[i].getAs("image/png").setName("chartBlob"+i);
  var emailChart = "<img src='cid:chart" + i + "'> test<br> "
  emailImages["chart"+i] = chartBlobs[i]
  }

The only values of chartBlobs in the final line of this extract that sends an image of a chart in the email are

chartBlobs[i]

and

chartBlobs[0]

even though there are 4 charts [0,1,2,3] in the charts.length array and on the sheet.

How can I change this to send e.g. chart [2] or [3], and how can I change this to send e.g. chart [2] and [3] together as two separate images in the email?

Thanks a lot for your help.

Answer

I believe your goal as follows.

  • You want to send an email with the inline images.
  • You want to use the images by giving the index of chart.
    • As a sample, you want to use the index of 1 and 2 from [0,1,2,3] of the retrieved charts.

Modification points:

  • In this answer, at first, var chartIndex = [2, 3]; is declared for retrieving the charts by the index. From your question, in this sample, 2 and 3 are used.
  • In this case, I think that emailChart is required to be added "<img src='cid:chart" + i + "'> test<br> " for each image.

When above points are reflected to your script, it becomes as follows.

Modified script:

Please set the index of chart you want to use to var chartIndex = [2, 3];.

var chartIndex = [2, 3]; // Please set the index of chart. Here, from your question, 2 and 3 are used.
var sheetSummary = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Summary");
var charts = sheetSummary.getCharts();
var emailImages = {};
var emailChart = "";
chartIndex.forEach(i => {
  emailChart += "<img src='cid:chart" + i + "'> test<br> ";
  emailImages["chart" + i] = charts[i].getAs("image/png").setName("chartBlob" + i);
});

// This is a sample for sending the email with the inline images.
MailApp.sendEmail({to: '###', subject: '###', htmlBody: emailChart, inlineImages: emailImages});
  • In this script, when var chartIndex = [2, 3]; is modified to var chartIndex = [2];, the chart of the index 2 is used.

References: