Insert a chart built with google apps script into a google spreadsheet

Question

This may sound a dumb question, but I have searched the web a lot and I didn't find what I wanted... I 've managed to build the chart I want to display, from the data on different spreadsheets, and I would like to insert the chart into a spreadsheet without having to paste the data on the sheet. Is this possible ? I can also use UIApp to display it, but then I would like to have it displayed on the spreadsheet without having to call it from a menu or a button. Thanks for your help, edelans -- Here is my code so far, it's working as I can see on the debugger tool ``` function BuildMTChart(){ var ss = SpreadsheetApp.getActiveSpreadsheet() var DataChart = MTLastMonth() // MTLastMonth() returns an array with the data I need var dSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dashboard"); var dataTable = Charts.newDataTable(); dataTable.addColumn(Charts.ColumnType['DATE'], 'Date'); dataTable.addColumn(Charts.ColumnType['NUMBER'], 'Maintenance'); dataTable.addColumn(Charts.Co…

Accepted Answer

There used to be setDataTable method for EmbeddedChartBuilder. But I couldn't make it work.

So as far as I understand, you can add charts to the spreadsheet from the script, but those need to have their data in the spreadsheet itself:

```
var chart = workingSheet.newChart()
  .setChartType(Charts.ChartType.COLUMN)
  .setPosition(8, 2, 0, 0)
  .setTitle("Sample chart")
  .setXAxisTitle("...")
  .setYAxisTitle("...")
  .addRange(range)    // This could be smth. like SpreadsheetApp.getActiveSpreadsheet().getRange("B2:C8")
  .build();

SpreadsheetApp.getActiveSpreadsheet().insertChart(chart);
```

Here it says you can set some advanced chart options. Maybe there is a way to pass a DataTable there as well?

For now I'd recommend populating the spreadsheet with your data and then adding a chart based on ranges. If you don't want this data visible in the google doc, you can hide columns or move it to a new sheet.

Insert a chart built with google apps script into a google spreadsheet

Solution

Problem