Google-sheets – How to access a gauge chart in Google Spreadsheets

google sheetsgoogle-apps-scriptgoogle-sheets-charts

I have a gauge chart in my spreadsheet. I am looking for a line for accessing it. 

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Gauge");
  var charts = sheet.getCharts();
  var crt = charts[0];

  return(charts.length);
}

The problem is that sheet.getCharts() does not seem to encompass gauge charts. I have tested that by inserting a line chart into the same sheet. return(charts.length); then returned 1.

gauge chart and line chart

How to access the gauge chart?

Best Answer

This is a known issue with Google Spreadsheets, first reported a year ago. You can star the issue in the hope of increasing its importance. So far, the response from Google is

Thank you for your report. We have been able to reproduce the problem and have filed it internally.

Since getCharts is the only Sheet method returning embedded charts, Apps Scripts cannot access the charts that getCharts does not support. Gauge is not the only type that lacks such support: same issue exists with some other types listed under "Other", e.g., Polar and Sparkline types.

My impression is that Google shifted resources toward Google Charts API, which is not tied to Spreadsheets. It does support Gauge charts as well as many other types. If you require programmatic access to gauge charts and are okay with them not being in a spreadsheet, this may be your best bet. A standalone script can still get data from a published spreadsheet, but of course this process will involve a bit more coding than with a spreadsheet-bound script.

Related: Embedding a Google Visualization Chart inside a Google Spreadsheet

Related Topic