Creating chart with Apps Script fails to be inserted into spreadsheet

2
votes

The goal here to programmatically create a chart from a pre-existing data, when a Spreadsheet has loaded.

The documentation for the EmbeddedChart class has two examples, both modifying existing charts, and, as I require, creating a new one:

function newChart(range, sheet){
   var sheet = SpreadsheetApp.getActiveSheet();
   var chartBuilder = sheet.newChart();
   chartBuilder.addRange(range)
       .setChartType(Charts.ChartType.Line)
       .setOption('title', 'My Line Chart!');
   sheet.insertChart(chartBuilder.build());
 }

Google example is missing: .setPosition()

I've tried implementing this function, as so:

function makeChart(sheet, range) {
  Logger.log('MAKING CHART');  
  var chart = sheet.newChart();
  chart.addRange(range)
    .setChartType(Charts.ChartType.BAR)
    .setOption('title','I love Google Apps Script');
  sheet.insertChart(chart.build());
}

Where sheet and range are defined as:

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("C1:D16");

makeChart() is called from onOpen(), which I can see is being executed. Despite this, the chart never appears in the spreadsheet.

I've noticed nothing will be logged after the .build() method has been called, but there's little in the docs about this method, beyond stating that it must be called after making a chart.

Edit: The script fails because .insertChart() expects an EmbeddedChart object, but is given an EmbeddedChartBuilder. The .build() method, according to the docs, should return an EmbeddedChart. But it doesn't.

2
google-apps-scriptgoogle-sheets

2 Answers

4
votes

The following code works for me, displaying the data in range A2:B8, as a bar chart embedded in the sheet.

function varSet(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var dataRange = sheet.getRange("A2:B8");

  buildChart(sheet, dataRange)

}

function buildChart(sheet, dataRange) {

  var newChart = sheet.newChart()
    .setChartType(Charts.ChartType.BAR)
    .addRange(dataRange)
    .setPosition(5, 5, 0, 0)
    .build();

  sheet.insertChart(newChart);

};

As far as I can tell the only real difference is that I have specified a position to insert the chart as well as create the chart before calling it with the insertChart() method. Hope this helps.

3
votes

Google's own example here:

https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart

It is wrong on two counts. Firstly the ChartType is 'LINE' not 'Line' and secondly it needs a call to .setPosition otherwise you get an error about 'Those columns are out of bounds'. @samScholefield's answer is correct.

Note that just chaining '.build()' on the end like this doesn't work:

var chart = sheet.newChart();
chart.addRange(range)
 .setChartType(Charts.ChartType.BAR)
 .setPosition(5,5,0,0)
 .setOption('title','I love Google Apps Script')
 .build();
sheet.insert(chart);

It needs to be like this:

var chart = sheet.newChart();
chart.addRange(range)
 .setChartType(Charts.ChartType.BAR)
 .setPosition(5,5,0,0)
 .setOption('title','I love Google Apps Script');
sheet.insert(chart.build());