I'm trying to programatically modify an area chart within a Google spreadsheet. I want to read the existing chart's options like so; the string values in AREA_CHART_PROPS have been taken from Google's documentation:

let targetSheet = SpreadsheetApp
  .getActiveSpreadsheet()
  .getSheets()
  .find(s => s.getName() === "<sheet name>");

// Array shortened here for example purposes
const AREA_CHART_PROPS = [
  "backgroundColor",
  // ...
  "backgroundColor.fill",
  // ...
  "colors"
  // etc...
];

let chart = targetSheet.getCharts()[0];  
let options = chart.getOptions();

for(prop of AREA_CHART_PROPS) {
  try {
    var propValue = options.get(prop);
    Logger.log(`${prop} (${typeof propValue}): ${propValue}`);
  } catch (e) {
    Logger.log(`ERROR: ${prop}: ${e}`);
  }
}

However, doing this only produces one of two outputs:

1. null for options that have never been set programmatically (which is fair enough);

2. This error message for options that have previously been set programmatically:

   Exception: Unexpected error while getting the method or property get on object Charts.ChartOptions.
   

Notwithstanding that a chart updated using build() and modify() seems to lose all manually-set formatting anyway, is this a flaw with the Chart.ChartOptions type, or am I attempting to read the options using incorrect syntax?

I'm trying to programatically modify an area chart within a Google spreadsheet. I want to read the existing chart's options like so; the string values in AREA_CHART_PROPS have been taken from Google's documentation:

let targetSheet = SpreadsheetApp
  .getActiveSpreadsheet()
  .getSheets()
  .find(s => s.getName() === "<sheet name>");

// Array shortened here for example purposes
const AREA_CHART_PROPS = [
  "backgroundColor",
  // ...
  "backgroundColor.fill",
  // ...
  "colors"
  // etc...
];

let chart = targetSheet.getCharts()[0];  
let options = chart.getOptions();

for(prop of AREA_CHART_PROPS) {
  try {
    var propValue = options.get(prop);
    Logger.log(`${prop} (${typeof propValue}): ${propValue}`);
  } catch (e) {
    Logger.log(`ERROR: ${prop}: ${e}`);
  }
}

However, doing this only produces one of two outputs:

1. null for options that have never been set programmatically (which is fair enough);

2. This error message for options that have previously been set programmatically:

   Exception: Unexpected error while getting the method or property get on object Charts.ChartOptions.
   

Notwithstanding that a chart updated using build() and modify() seems to lose all manually-set formatting anyway, is this a flaw with the Chart.ChartOptions type, or am I attempting to read the options using incorrect syntax?

• google-sheets
• google-apps-script
• google-visualization

2 Answers 2

I can't categorically state whether there a flaw with Chart.getOptions().get("option name") but though that is my educated guess.

OTOH, I don't think that the Op is using incorrect syntax. Any errors are due, I think, to some exotic aspect of the code.

BUT I can offer a very simple workaround which has no effect on "manually-set formatting".

I found that if one aspect of the chart is modified BEFORE the Chart.getOptions().get("option name"), then the OP code will run as expected.

I chose to modify .setOption('backgroundColor', 'white')

function modifiedOpCode() {

  let targetSheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheets()
    .find(s => s.getName() === "so79461115")

  var AREA_CHART_PROPS = [
      "areaOpacity",
      "backgroundColor",
      "backgroundColor.fill",
      "chartArea",
      "chartArea.backgroundColor",
      "chartArea.height",
      "chartArea.left",
      "chartArea.top",
      "chartArea.width",
      "colors",
      "hAxis",
      "hAxis.direction",
      "hAxis.gridlines",
      "hAxis.gridlines.color",
      "hAxis.gridlines.count",
      "hAxis.logScale",
      "hAxis.maxValue",
      "hAxis.minorGridlines",
      "hAxis.minorGridlines.color",
      "hAxis.minorGridlines.count",
      "hAxis.minValue",
      "hAxis.textPosition",
      "hAxis.textStyle",
      "hAxis.title",
      "hAxis.titleTextStyle",
      "hAxis.viewWindow",
      "hAxis.viewWindow.max",
      "hAxis.viewWindow.min",
      "height",
      "interpolateNulls",
      "isStacked",
      "legend",
      "legend.position",
      "legendTextStyle",
      "lineWidth",
      "pointShape",
      "pointSize",
      "reverseCategories",
      "series",
      "subtitle",
      "subtitleTextStyle",
      "theme",
      "title",
      "titleTextStyle",
      "treatLabelsAsText",
      "useFirstColumnAsDomain",
      "vAxes",
      "vAxis",
      "vAxis.direction",
      "vAxis.gridlines",
      "vAxis.gridlines.color",
      "vAxis.gridlines.count",
      "vAxis.logScale",
      "vAxis.maxValue",
      "vAxis.minorGridlines",
      "vAxis.minorGridlines.color",
      "vAxis.minorGridlines.count",
      "vAxis.minValue",
      "vAxis.textPosition",
      "vAxis.textStyle",
      "vAxis.title",
      "vAxis.titleTextStyle",
      "vAxis.viewWindow",
      "vAxis.viewWindow.max",
      "vAxis.viewWindow.min"
    ]
  // Logger.log(AREA_CHART_PROPS) // DEBUG

  // get the chart
  let chart = targetSheet.getCharts()[0]

  // modify an EXISTING value
  chart = chart.modify()            
          .setOption('backgroundColor', 'white')
          .build()

  // get the chart options        
  let options = chart.getOptions()

  // loop through the PROPS array
  for(prop of AREA_CHART_PROPS) {
    try {
      var propValue = options.get(prop)
      if (propValue !== null){
        Logger.log(`${prop} (${typeof propValue}): ${propValue}`)
      }
    } catch (e) {
      Logger.log(`ERROR: ${prop}: ${e}`)
    }
  }  
}

SAMPLE: Output (non-null values)

backgroundColor (string): #ffffff
backgroundColor.fill (string): #ffffff
hAxis.title (string): Year
height (number): 371
title (string): Sales and Expenses
useFirstColumnAsDomain (boolean): true

SAMPLE: Chart data

SAMPLE: Chart

AppsScript charts are not the same as Google visualization charts, unfortunately. They used to be the same in the past, but AppsScript charts are now using the same internal library that sheets uses. So whatever you find in the Google visualization documentation does not necessarily apply to your AppsScript charts.