So, I have a sheet that covers a year of data, and a bar chart that updates on a trigger to show each month's data specifically. It also sets a color for each of the bars when it updates. The problem is that the way I did the color changing, it always has the same color in each position regardless of what the label for the bar is. (ie. if it's sorted by descending value, if X has the lowest number it's blue, and Y is second and red, etc. Then the next month Y has less so it's first and blue, and X is red). I need to set the colors based on the label (so X is blue regardless of if it has more or less than Y in a given month). I can't figure out how to reference what the labels are to set the colors though.
My code to update the chart and set the colors is:
function updateRangeBar(sheet, range, chart)
{
chart = chart.modify()
.clearRanges()
.addRange(range)
.setOption('series.0.items.0.color', "red")
.setOption('series.0.items.1.color', "blue")
.setOption('series.0.items.2.color', "yellow")
.setOption('series.0.items.3.color', "green")
.setOption('series.0.items.4.color', "orange")
.setOption('series.0.items.5.color', "black")
.build()
sheet.updateChart(chart);
}
What I'm imagining the code to look like would be like
function updateRangeBar(sheet, range, chart)
{
var redIndex, blueIndex, yellowIndex, greenIndex, orangeIndex, blackIndex
for(i = 0; i < [chartBarItemsLength]; i++)
{
if([chartBarItemLabel] == "DataHeaderForRed")
{
redIndex = i
}
elseIf([chartBarItemLabel] == "DataHeaderForBlue")
{
blueIndex = i
}
}
chart = chart.modify()
.clearRanges()
.addRange(range)
.setOption('series.0.items.'+redIndex+'.color', "red")
.setOption('series.0.items.'+blueIndex'+.color', "blue")
.setOption('series.0.items.+yellowIndex'+.color', "yellow")
.setOption('series.0.items.+greenIndex'+.color', "green")
.setOption('series.0.items.+orangeIndex'+.color', "orange")
.setOption('series.0.items.+blackIndex'+.color', "black")
.build()
sheet.updateChart(chart);
}
Where [chartBarItemLabel] and [chartBarItemsLength] are what I need help finding a way to get.
Figured out an answer, though better options would always be nice.
function updateRangeBar(sheet, range, chart)
{
var subRange = range.getSheet().getRange(range.getRow(), range.getColumn(), range.getHeight(), 1)
chart = chart.modify()
.clearRanges()
.addRange(range)
.build()
for(i = 0; i < subRange.getHeight(); i++)
{
var val = range.getSheet().getRange((subRange.getRow()+(i+1)), subRange.getColumn()).getValue()
if(val.toString() == BLUE_TYPE)
{
chart = chart.modify().setOption('series.0.items.'+i+'.color', BLUE_RGB).build()
}
else if(val.toString() == RED_TYPE)
{
chart = chart.modify().setOption('series.0.items.'+i+'.color', RED_RGB).build()
}
else if(
...
}
sheet.updateChart(chart);
}