How can I set the color for a single data point in a series in Google Sheets script

2
votes

I decided to create a bar chart inside my Google Sheet, read it in using the Script Editor tool & modify it in place, as I felt this would be easier than creating one from scratch & then adding it to the Sheet. Turns out, using the UI to create the Chart has been much easier than scripting it.

Now, I would like to set the color of only 1 of the data points in a single series to be red, when it's over a certain threshold. So far, I have found that the following snippet can set the color of the entire series, but I can't find anything to set the color of just one data point:

  var chart = sheet.getCharts()[0];
  chart = chart.modify()
     .setOption('series.0.color', 'red')
     .build();
  sheet.updateChart(chart);

If I try to replace setOption with setColors (.setColors(['green', 'red'])), I get an error saying

TypeError: Cannot find function setColors in object EmbeddedChartBuilder.

According to this reference, setColors can only be used when creating a new chart.

There are numerous articles online about how to change the color of a single data point in a bar chart from the UI. Any pointers on how to change the color programmatically would be much appreciated.

1
google-apps-scriptgoogle-sheetsgoogle-visualizationembedded-resource
To recreate what you're trying to do, it would be helpful to provide a minimal way to reproduce the chart you are working with. If you replace the .setColors(['green', 'red']) with .setOption('series', { 0: { color: 'green' }, 1: { color: 'red' } }) does that do what you want it to do? – dwmorrin
you can use a style column role to change the color of a single data point... – WhiteHat
@dwmorrin I believe this snippet [.setOption('series', { 0: { color: 'green' }, 1: { color: 'red' } })] will achieve the same effect as [.setOption('series.0.color', 'red')], i.e. change the color of the entire series, not a single data point. – Freakishly
@WhiteHat I checked out the link, but I believe roles are for web charts only. I'm trying to use the EmbeddedChart in Google Apps Script, which seems to be a trimmed down version of the web chart. – Freakishly
Were you ever able to figure this out? – IMTheNachoMan

1 Answers

2
votes

This is how you set the color of a specific series:

var chart = sheet.getCharts()[0];
chart = chart.modify()
    .setOption('series.0.color', 'red')
    .setOption('series.0.items.0.color', 'blue')
    .build();
sheet.updateChart(chart);