1

I have the following chart:

enter image description here

And a script attached to the button:

function updateChart() {
  const sheet = SpreadsheetApp.getActiveSheet()
  const chart = sheet.getCharts()[0];
  SpreadsheetApp.getUi().alert(String(chart.getOptions().get('title')));
  SpreadsheetApp.getUi().alert(String(chart.getOptions().get('series.0.color')));
  sheet.updateChart(chart.modify().build());
}

The first time I'm pressing this button it does nothing (as expected). The second time it resets line color to blue.

enter image description here

series.0.color is always null, although title is there, so I'm not even sure where to get that color to preserve it in between .modify() and .build() calls.

How do I prevent updateChart from resetting the color?

I've tried to inspect options in the published chart as suggested in comments (thanks @TheMaster), unfortunately it still doesn't help with the color issue.

The options are:

{
    "hAxis": {
        "title": "x"
    },
    "vAxes": {
        "0": {
            "title": "10x"
        }
    },
    "series": {
        "0": {
            "color": "#008000"
        }
    },
    "useFirstColumnAsDomain": true,
    "width": 600,
    "title": "10x vs \"x\"",
    "height": 371
}

And I'm using the following code to get them:

function printOption(opts, name) {
  console.log(`${name}: ${opts.get(name)}`)
}

function inspectChart(chart) {
  const options = chart.getOptions();
  printOption(options, 'title');
  printOption(options, 'hAxis.title');
  printOption(options, 'vAxes.0.title');
  printOption(options, 'series.0.color');
}

Output is:

title: 10x vs "x"
hAxis.title: x
vAxes.0.title: 10x
series.0.color: null

As you can see all properties (including nested within array) are there except color

Another interesting point is that once you try to update the color:

sheet.updateChart(sheet.getCharts()[0].modify().setOption('series', {0: {color: 'green'}}).build());

It applies, but from now you're unable to read that property at all:

sheet.getCharts()[0].getOptions().get('series.0.color')

The line above fails with Unexpected error while getting the method or property get on object Charts.ChartOptions.

Also it looks like no matter what color I choose it is always #008000 in the published chart, which makes me think I should look for the color somewhere else, but I don't know where

awesoon
  • 32,469
  • 11
  • 74
  • 99
  • See the [source and diff](https://stackoverflow.com/questions/13594839/google-apps-script-how-to-set-use-column-a-as-labels-in-chart-embedded-in-spr) – TheMaster Aug 12 '22 at 07:47
  • Thank you for the link, but it didn't help. I've updated the post with new details – awesoon Aug 12 '22 at 08:07
  • Are your sure it's `series.0.color` and not `series.color`? For eg, the [json here](https://stackoverflow.com/a/31012304/) shows `color` outside `0`. – TheMaster Aug 12 '22 at 08:32
  • Both of them are `null` when I'm trying to get them from `getOptions().get(...)`. Modifying `series.color` doesn't change the color. Modifying `series.0.color` works, but the option becomes unreadable. The json above is copypasted, so yes, it should be `series.0.color`, although I also saw some docs mentioning that `color` should be within `series`, not `series.0`, but in this case it's unclear how to set different colors for different series – awesoon Aug 12 '22 at 09:41
  • How about setting `series.0.color` directly instead of a object? `sheet.updateChart(sheet.getCharts()[0].modify().setOption('series.0.color','green'}}).build());` Also the option becoming unreadable - is it reproducible in a new sheet, new chart after modification? – TheMaster Aug 12 '22 at 09:45
  • I tried it as well, it changes color, but the property still unreadable (throws exception). Yes, it is reproducible in a new sheet, just tried – awesoon Aug 12 '22 at 09:50
  • Maybe you should create a issue in the issuetracker. See [tag info page](https://stackoverflow.com/tags/google-apps-script/info) for issuetracker. – TheMaster Aug 12 '22 at 10:12
  • Yes, will do, just hoped for a workaround as it may take some time until they fix it – awesoon Aug 12 '22 at 10:28

0 Answers0