Adding a chart in Google Sheets with Google Script

Question

I'm trying to add a chart in a sheet on Google Sheets but Google Script is saying "Cannot find method insertChart(Charts.Chart). (line 39, file "Code")". But I'm using the method that written in the documentation. What am I doing wrong ?

function doGet()
{
    var app = SpreadsheetApp;
    var ss = app.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //
    var artistsArray;
    var dataArtists;
    //
    artists = {};
    dataArtists = Charts.newDataTable()
      .addColumn(Charts.ColumnType.STRING, 'Artists')
      .addColumn(Charts.ColumnType.NUMBER, 'Count');
    //  
    for (var line = 2 ; line <= ss.getDataRange().getNumRows() ; line++)
    {
      value = sheet.getRange(line, 3).getValue();
      if (isNaN(artists[value]))
        artists[value] = 1;
      else
        artists[value]++;
    }
    //
    for(var key in artists)
    {
      console.log(key, artists[key])
      dataArtists.addRow([key, artists[key]]);
    }
    dataArtists.build()
    //
    var artistsChart = Charts.newBarChart()
      .setDataTable(dataArtists)
      .setTitle('Number of Tracks per Artist')
      .setXAxisTitle('Number of  Tracks')
      .setYAxisTitle('Artists')
      .setDimensions(600, 500)
      .build();
      //
    sheet.insertChart(artistsChart);
}

ZektorH · Accepted Answer · 2020-02-17 09:10:31Z

You're creating a Chart object using Charts service functions. To use insertChart() function, you need an EmbeddedChart object from the Spreadsheet service. To create an embedded chart use newChart() function and to edit it use EmbeddedChartBuilder and EmbeddedBarChartBuilder (as you want a bar chart) functions.

The below code it's a translation of your current code, you need to set the data range with addRange function, being "B4:C17" cells in this case:

function createChart() {
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  var artistsChart = sheet.newChart().asBarChart()
  .addRange(sheet.getRange("B4:C17"))
  .setTitle('Number of Tracks per Artist')
  .setXAxisTitle('Number of  Tracks')
  .setYAxisTitle('Artists')
  .setPosition(4, 7, 0, 0)
  .build();

  sheet.insertChart(artistsChart);
}

Notice you should change your function name because the doGet function name is used for Web Apps.

0Valt · Accepted Answer · 2020-02-04 16:11:57Z

Problem

Method insertChart(Charts.Chart) "is not found". Error messages in Google Apps Script can be quite confusing, but once explained, they become a bit easier to follow - what the error really says is that it cannot be called on a Chart instance. If you take a look at the docs again, you'll see that it accepts an instance of EmbeddedChart and that's what causing the problem.

Solution

Instead of constructing the Chart using Charts service, use newChart() method - it yields an EmbeddedChartBuilder instance which you can configure however you like, call build() on it and pass the resulting EmbeddedChart to insertChart.

References

newChart() method ref
EmbeddedChartBuilder class ref
EmbeddedChart class ref

Notes

If I knew that from the start, it would've saved me a lot of debug time - as a general rule of thumb, if you see an error msg with "cannot find method", you are trying to pass in an argument of diff type / class instance than required (e.g. passing undefined)

Collectives™ on Stack Overflow

Adding a chart in Google Sheets with Google Script

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related