How To Get Chart From Sheet in Google Apps Script

Question

I am using Google Apps Script with Spreadsheets. I am trying to find out a way to return a Google Chart(static or dynamic, doesn't matter). The data set that I will be using will be in a spreadsheet, and I want the Google Chart to be returned in the form of a web app(URL should start with: script.google.com/macros/idforscript). I have tried to use the official Google Apps Script tutorials, but even they don't work, leaving me, when I open the page, with a [400] error in a red rectangle. Here is the code that I am using:

  function doGet() {
  /*
  var data = Charts.newDataTable()
      .addColumn(Charts.ColumnType.STRING, 'Month')
      .addColumn(Charts.ColumnType.NUMBER, 'In Store')
      .addColumn(Charts.ColumnType.NUMBER, 'Online')
      .addRow(['January', 10, 1])
      .addRow(['February', 12, 1])
      .addRow(['March', 20, 2])
      .addRow(['April', 25, 3])
      .addRow(['May', 30, 4])
      .build();
      */
  var data = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1PPlSIiQ7fZ5AzcyB61iVmIBQQepm6vubd4S2Eo9fWOg/edit#gid=0").getSheets()[0];


  var chart = data.newChart().setChartType(Charts.ChartType.BAR).addRange(data.getRange('A1:B3')).build();
  var uiApp = UiApp.createApplication().setTitle('My Chart');
  uiApp.add(chart);
  return uiApp;

}

Do you know the solution to this problem? Any help is greatly appreciated.

Jack Brown Jack Brown · Accepted Answer · 2017-03-15T22:34:24

The reason for the error is newCharts().build() function returns a embeddedChart Object:

var chart = data.newChart().setChartType(Charts.ChartType.BAR).addRange(data.getRange('A1:B3')).build();

As mentioned here: https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart-builder#build()

However, for UiApp you need a chart Object which you should be able to get using setId() method of the embedded Chart object. Unfortunately, using that method raises this error: "Access to class "Chart" is prohibited" error. This issue can be tracked here.

Please also note UiApp has been deprecated since 2014, so not advisable to use for new projects.

Now for a solution and some good news :). Below you will find a modified code which will work with embeddedChart to give you a static chart on your webPage.

Try this:

function doGet() {
  /*
  var data = Charts.newDataTable()
      .addColumn(Charts.ColumnType.STRING, 'Month')
      .addColumn(Charts.ColumnType.NUMBER, 'In Store')
      .addColumn(Charts.ColumnType.NUMBER, 'Online')
      .addRow(['January', 10, 1])
      .addRow(['February', 12, 1])
      .addRow(['March', 20, 2])
      .addRow(['April', 25, 3])
      .addRow(['May', 30, 4])
      .build();
      */
  var data = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1PPlSIiQ7fZ5AzcyB61iVmIBQQepm6vubd4S2Eo9fWOg/edit#gid=0").getSheets()[0];


  var chart = data.newChart().setChartType(Charts.ChartType.BAR).addRange(data.getRange('A1:B3')).build();
  var blob = chart.getBlob().setContentType('image/jpeg');
  var bytes = blob.getBytes();
  var base64String = Utilities.base64Encode(bytes);
  var html = '<img src="data:' + blob.getContentType() + ';base64,' + base64String + '"/>';
  return HtmlService.createHtmlOutput(html)
}

UiApp has been deprecated since 2014. So you will have to use HTML Service to create your content. https://developers.google.com/apps-script/reference/ui/ui-app

Hope that helps.

How To Get Chart From Sheet in Google Apps Script

1 Answers