CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Doug Fir
Doug Fir

Reputation: 21292

Sparklines stacked bar color options only allow two color variations?

I'm using the sparkline function in Google Spreadsheets. Here is an example of some data:

Feb-2015    MTD Actual  MTD Budget  Budget
Campaign 1  $41.32       $13.71   $96.00

I'd like to create a stacked bar chart sparkline as per the documentation here.

=sparkline(D9:F9,{
    "charttype","bar";
    "color1","blue";
    "color2","yellow";
    "color3","red"})

D9:F9 corresponds to $41.42: $96

Here is what the result looks like: enter image description here

I had expected a segment to be red (color3). But the sparkline only seems to customize 2 colors. Can't see anything in the documentation about this. Is this a bug? Or am I misunderstanding something?

If it's the case that I can only edit 2 colors using this function, I don't suppose it's possible to edit the functionality using Google-Apps-Script?

Upvotes: 3

Views: 6137

Answers (2)

Topalov
Topalov

Reputation: 31

There is a trick if you want to have 100% 3-shares bar. So, if you have 3 values that gives sum of 100 do as follows:

Remove 3rd value from the data cell. Set "max","100" (or "max"/"100" for Europe). Make a color background for sparkline cell.

This way the third value that fills to 100 is represented by cell background - it is a third sparkline color.

Upvotes: 3

Spencer Easton
Spencer Easton

Reputation: 5782

There is only color1 and color2 options listed for a bar graph. Sparklines are not expressly exposed to Apps Script, but can be written to a cell as a value. For example:

ss.appendRow(['==sparkline(D9:F9,{"charttype","bar";"color1","blue";"color2","yellow";})']);

From the docs: https://support.google.com/docs/answer/3093289?hl=en

For bar charts:

"max" sets the maximum value along the horizontal axis.
"color1" sets the first color used for bars in the chart.
"color2" sets the second color used for bars in the chart.
"empty" sets how to treat empty cells. Possible corresponding values include: "zero" or "ignore".
"nan" sets how to treat cells with non-numeric data. Options are: "convert" and "ignore".
"rtl" determines whether or not the chart is rendered right to left. Options are true or false.

Upvotes: 2

Related Questions