CODEWITHSUNDEEP
google-apps-script
edelans
edelans

Reputation: 9088

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

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.ColumnType['NUMBER'], 'Troubleshooting');
          for ( var i = 0; i < DataChart.length; i++) {
            dataTable.addRow([DataChart[i][0], DataChart[i][1], DataChart[i][2]]);
          }
          dataTable.build();        

    var chart = Charts.newAreaChart()
        .setDataTable(dataTable)         
        .setDimensions(800, 400)         
        .setYAxisTitle("Hours")
        .setXAxisTitle("Date")
        .build();

Upvotes: 3

Views: 10731

Answers (2)

Srikanth
Srikanth

Reputation: 836

May be you can add this to UiApp, size it properly and setup a trigger, so that the UI app is displayed every time the spreadsheet is opened? Something like this.

function onOpen() {
  return myUiApp();
}

function myUiApp() {
  //All your code to build the chart and add it to the app
}

Upvotes: 0

Demetroid
Demetroid

Reputation: 78

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.

Upvotes: 5

Related Questions