CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-apps
Crouzilles
Crouzilles

Reputation: 813

Error inserting chart in google sheet

I have the following code which allows me to create a bar chart

var chart = Charts.newBarChart()
.setDataTable(dataTable)
.setTitle("Rain per month");

sheet.insertChart(chart.build());

Unfortunately, I get an error saying "Cannot find method insertChart(Chart)".

It seems that the sheet.insertChart function must take an embeddedChart.

How can I turn my chart into an embedded chart so the insertChart function will take it?

Regards Crouz

Upvotes: 1

Views: 1007

Answers (1)

Tovly Deutsch
Tovly Deutsch

Reputation: 663

The methods you are using are for adding charts to the UiApp. To add a chart to a google sheet you would use this syntax:

 var chart = sheet.newChart()
 .setChartType(Charts.ChartType.BAR)
 .addRange(sheet.getRange("A1:B4"))
 .setPosition(5, 5, 0, 0)
 .setOption("title", "Dynamic Chart")
 .build();
 sheet.insertChart(chart);

This code was taken from here on the Google App Scripts Documentation.

Also, The DataTable class is used to create charts when adding charts to html or the UiApp. If you wanted to add a chart to the spreadsheet as if the user had clicked insert > chart, you would have to use getRange to draw data. If you're drawing data from multiple sources I would suggest combining the data and adding it somewhere in the spreadsheet using sheet.getRange().setValue().

If you wanted to draw data from a seperate google spreadsheet then you could do something like this:

//SpreadsheetApp.openByUrl.openById(inset appropriate id here) would work as well
var externalSheets = SpreadsheetApp.openByUrl(insert appropriate url here).getSheets();
  var chart = sheet.newChart()
 .setChartType(Charts.ChartType.BAR)
 .addRange(sheets[insert appropriate sheet index here].getRange("A1:B4"))
 .setPosition(5, 5, 0, 0)
 .setOption("title", "Dynamic Chart")
 .build();
 sheet.insertChart(chart);

Upvotes: 3

Related Questions