How to set the horizontal (or vertical) axis number format for a Google Sheets chart?

user_17029899 0
Viewed 532

I'm generating a spreadsheet, several pivot tables, and charts to visualize the results. I have everything working, except I haven't found a way to set the number format for the chart horizontal/vertical axes. I want the axes to show whole numbers, without the need for me to change the number format of the backing data.

Unfortunately, the documentation provided by Google does not cover this feature: https://developers.google.com/apps-script/chart-configuration-options#line-chart-configuration-options.

It is possible to set the number format manually, however, as I'm generating a large number of charts, it would be too burdensome to do this for all charts.

Here is an example of the Apps Script code used to generate my charts:

embeddedChartBuilder.setChartType(Charts.ChartType.LINE)
    .setPosition(chartRow, chartColumn, 0, 0)
    .setNumHeaders(1)
    .setOption('backgroundColor', '#222222')
    .setOption('height', chartHeightPx)
    .setOption('width', chartWidthPx)
    .setOption('series', [
      {color:'#4ebcbb', pointSize:6, lineWidth:4},
      {color:'#cccccc', pointSize:6, lineWidth:4},
      {color:'#666666', pointSize:6, lineWidth:4},
      {color:'#34a853', pointSize:6, lineWidth:4}])
    .setOption('applyAggregateData', 0)
    .setOption('vAxis.gridlines.color', '#434343')
    .setOption('vAxis.minValue', 0)
    .setOption('vAxis.maxValue', 5)
    .setOption('vAxis.textStyle', { color: '#efefef', fontName: 'Arial', fontSize: 12, bold: false, italic: false })
    .setOption('hAxis.textStyle', { color: '#efefef', fontName: 'Arial', fontSize: 12, bold: false, italic: false });
google-apps-script google-sheets charts
1 Answers

On the Line Chart page under Configuration Options you have the hAxis.format with the following description:

A format string for numeric or date axis labels.

For number axis labels, this is a subset of the decimal formatting ICU pattern set . For instance, {format:'#,###%'} will display values "1,000%", "750%", and "50%" for values 10, 7.5, and 0.5. You can also supply any of the following:

{format: 'none'}: displays numbers with no formatting (e.g., 8000000)
{format: 'decimal'}: displays numbers with thousands separators (e.g., 8,000,000)
{format: 'scientific'}: displays numbers in scientific notation (e.g., 8e6)
{format: 'currency'}: displays numbers in the local currency (e.g., $8,000,000.00)
{format: 'percent'}: displays numbers as percentages (e.g., 800,000,000%)
{format: 'short'}: displays abbreviated numbers (e.g., 8M)
{format: 'long'}: displays numbers as full words (e.g., 8 million)
For date axis labels, this is a subset of the date formatting ICU pattern set . For instance, {format:'MMM d, y'} will display the value "Jul 1, 2011" for the date of July first in 2011.

The actual formatting applied to the label is derived from the locale the API has been loaded with. For more details, see loading charts with a specific locale .

In computing tick values and gridlines, several alternative combinations of all the relevant gridline options will be considered and alternatives will be rejected if the formatted tick labels would be duplicated or overlap. So you can specify format:"#" if you want to only show integer tick values, but be aware that if no alternative satisfies this condition, no gridlines or ticks will be shown.

This option is only supported for a continuous axis.

Type: string
Default: auto

And also a vAxis.format option with the following formats:

A format string for numeric axis labels. This is a subset of the ICU pattern set . For instance, {format:'#,###%'} will display values "1,000%", "750%", and "50%" for values 10, 7.5, and 0.5. You can also supply any of the following:

{format: 'none'}: displays numbers with no formatting (e.g., 8000000)
{format: 'decimal'}: displays numbers with thousands separators (e.g., 8,000,000)
{format: 'scientific'}: displays numbers in scientific notation (e.g., 8e6)
{format: 'currency'}: displays numbers in the local currency (e.g., $8,000,000.00)
{format: 'percent'}: displays numbers as percentages (e.g., 800,000,000%)
{format: 'short'}: displays abbreviated numbers (e.g., 8M)
{format: 'long'}: displays numbers as full words (e.g., 8 million)
The actual formatting applied to the label is derived from the locale the API has been loaded with. For more details, see loading charts with a specific locale .

In computing tick values and gridlines, several alternative combinations of all the relevant gridline options will be considered and alternatives will be rejected if the formatted tick labels would be duplicated or overlap. So you can specify format:"#" if you want to only show integer tick values, but be aware that if no alternative satisfies this condition, no gridlines or ticks will be shown.

Type: string
Default: auto
user_89962110
Related