CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetscharts
leodegenevraye
leodegenevraye

Reputation: 15

Adding a chart in Google Sheets with Google Script

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);
}

Upvotes: 1

Views: 2052

Answers (2)

Andres Duarte
Andres Duarte

Reputation: 3350

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.

Upvotes: 1

0Valt
0Valt

Reputation: 10375

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

  1. newChart() method ref
  2. EmbeddedChartBuilder class ref
  3. 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)

Upvotes: 1

Related Questions