Apps Script version of changing a column chart's gridline major step spacing value?

Question

So I have a column chart that is a count of an item. The automatic spacing likes to space the gridlines at a .5 value which is confusing as the count of an item is always a whole number. I can make the spacing of them be a whole number using the GUI options by setting the step value to 1, but when my script updates the chart each day, it wipes out that value when it moves the baseline to 0. How do I do it programmatically instead of in the GUI?

Default chart after the script runs each day

Default options after the script runs each day

Option I set to correct spacing

How the chart looks with the spacing change/how it should look

My current code is:

chart = chart.modify()
    .setOption('title', "Today's date: " + dateString)
    .setOption('vAxis.minValue', '0')
    .build();
  sheet.updateChart(chart);

Trying to add

.setOption("vAxis.gridlines.minSpacing", "1")

does nothing, and I can't just change the gridline count as the number needed will be randomly different every day

Answer 1

Real answer: Currently there are no API methods to change the "Major spacing type" and "Major count", among other chart settings. You can check out the chart options documentation to see what's available. Someone also filed a report in Google's issue tracker, it was acknowledged by a Google rep and now it's marked as a feature request. You can check it out to vote on it.

Potential workaround: We can still figure out a workaround by using vAxis.gridlines.count. If you get the value of the highest column and add 1 to it, it will be the equivalent of "Major step = 1".

So for instance you can write a function getMaxValue() which will get the highest value from your columns, then just +1 it.

    highest = getMaxValue() //in this example 13 is your highest value

    chart = chart.modify()
        .setOption('title', "Today's date: " + dateString)
        .setOption('vAxis.minValue', '0')
        .setOption('vAxis.gridlines.count', highest+1) //generates 14 gridlines
        .build();
    sheet.updateChart(chart);

If you're working with bigger numbers you'll probably want to cut the amount of gridlines. You can simulate a higher step number if you divide by the step you want and add 1 again. Use Math.ceil() to round up and avoid having the highest column out of the upper bound.

    .setOption('vAxis.gridlines.count', Math.ceil(max/2)+1) //step 2

    .setOption('vAxis.gridlines.count', Math.ceil(max/5)+1) //step 5

    .setOption('vAxis.gridlines.count', Math.ceil(max/10)+1)//step 10

    .setOption('vAxis.gridlines.count', Math.ceil(max/50)+1)//step 50 and so on

I said "simulate" steps because this is not a perfect workaround. The chart calculates the steps based on the amount of gridlines, and it really doesn't like steps that are not 2, or multiples of 5 and 10. This means that you can manually set something like a step 17 in the UI, but if you try to do Math.ceil(max/17)+1, then the gridlines will probably just round up to 20. See the following example, on the left I tried to set it programmatically and on the right I just set step 17 in the UI:

Either way, unless you're looking for a very specific step number you can just set a step amount appropriate to the average item count that your chart usually handles and leave it at that.

However, if the amounts vary wildly and could go from single digits to thousands, you'll need a formula that can account for this variance. It could be something like this:

    function calc(max){
      var mag = Math.floor(Math.log10(max))
      if (max > 9){mag--}
      var divisor = Math.pow(10,mag)
      return Math.ceil(max/divisor)+1
    }
//then use it in setoption
   .setOption('vAxis.gridlines.count', calc(max))

Roughly explained, this calculates from 1 to 100 gridlines and resets every time max grows by one order of magnitude, so it will adjust the chart for any amount.

I'm not a math whiz, so I kind of improvised the formula, there may be a better way to do it. If you want to fine-tune it, it would probably be better to ask Mathematics. But at least I hope this gives you an idea on how you can work around the API limitations.