How do I change the number format of the vertical axis? (when using the EmbeddedChartBuilder in Apps script spreadsheet service)

Question

I have a (container bound) script in one of my Google Sheets. With this script I create a chart based on the data in the sheet. See the picture:

Sheet and Chart

The apps script (SCRIPT 1) that I used, is:

/**
 * @OnlyCurrentDoc
 */

function buildChart() {
  //get the sheet
  var sheet = SpreadsheetApp.getActiveSheet()
  //create a new chart
  var chart = sheet.newChart()
    //basics
    .setChartType(Charts.ChartType.COLUMN)
    .addRange(sheet.getRange("D1:H8"))
    .setPosition(10, 10, 0, 0)
    .setOption('isStacked', 'TRUE')
    //title
    .setOption('title', 'Cost Price Development - Stage 3')
    .setOption('titleTextStyle', {fontSize: 12})
    //legend
    .setOption('legend.position', 'none') 
    //vertical axis
    .setOption('vAxes', {0: {title: '€', format: 'short', textStyle: {fontSize: 10}, titleTextStyle: {fontSize : 8}, viewWindow: {min: 79000000}}})  
    //horizontal axis
    .setOption('hAxis.title', 'cost price changes')
    .setOption('hAxis.titleTextStyle', {fontSize : 8})
    .setOption('hAxis.textStyle', {fontSize : 8})
    //chart height
    .setOption('height', 250)
    //colors series
    .setOption('colors', ['tranparant', '#659CEF', 'red', 'green'])
    .build();
  // Insert chart in sheet
  sheet.insertChart(chart);
}

Currently, (as you can see in the picture above) the numbers on the vertical axis range from 79,000,000 to 81,500,000. However, I want to change the format of these numbers, so that they are shown in millions: i.e. from 79M tot 81,5M.

With the setOptions() method I set many options succesfully. But somehow the option vAxis.format does not change the format of the vAxis numbers. As you can see in the full script, the following line defines the different vAxis options:

.setOption('vAxes', {0: {title: '€', format: 'short', textStyle: {fontSize: 10}, titleTextStyle: {fontSize : 8}, viewWindow: {min: 79000000}}})

Only format: 'short' does not seem to have an effect.

Subsequently, I changed the above script, so that vertical Axis options are defined in the same way as the the horizontal axis is defined. Below the SCRIPT 2 version:

// create waterfall chart
function waterfallChart() {
  //get the sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  //create a new chart
  var chart = sheet.newChart()
    //basics
    .setChartType(Charts.ChartType.COLUMN)
    .addRange(sheet.getRange("g1:k8"))
    .setPosition(10, 10, 0, 0)
    .setOption('isStacked', 'TRUE')
    //title
    .setOption('title', 'Cost Price Development - Stage 3')
    .setOption('titleTextStyle', {fontSize: 12})
    //legend
    .setOption('legend.position', 'none') 
    //vertical axis
    //.setOption('vAxes', {0: {title: '€', format: 'short', textStyle: {fontSize: 10}, titleTextStyle: {fontSize : 8}, viewWindow: {min: 79000000}}})
    .setOption('vAxis.title', '€')
    .setOption('vAxis.format', 'short')
    .setOption('vAxis.textStyle', {fontSize: 10})
    .setOption('vAxis.textStyle', {fontSize: 10})
    .setOption('vAxis.titleTextStyle', {fontSize: 8})
    .setOption('vAxis.viewWindow', {min: 79000000})
    //horizontal axis
    .setOption('hAxis.title', 'cost price changes')
    .setOption('hAxis.titleTextStyle', {fontSize : 8})
    .setOption('hAxis.textStyle', {fontSize : 8})
    //chart height
    .setOption('height', 250)
    //colors series
    .setOption('colors', ['tranparant', '#659CEF', 'red', 'green'])
    .build();
  // Insert chart in sheet
  sheet.insertChart(chart);
}

However, with SCRIPT 2 code all defined vertical axis options

setOption('vAxis.title', '€')
        .setOption('vAxis.format', 'short')
        .setOption('vAxis.textStyle', {fontSize: 10})
        .setOption('vAxis.textStyle', {fontSize: 10})
        .setOption('vAxis.titleTextStyle', {fontSize: 8})
        .setOption('vAxis.viewWindow', {min: 79000000})

have no effect on the chart; see screenshot:

Summarising:

SCRIPT 1 results in a chart with most options set and thus visible; only the vertical axis number format definition is not taken over in the chart.
SCRIPT 2 results in a chart in which none of the defined options are visible in the chart.

So, my question is: What code must I use so that all defined options of the vertical axis are taken over in the chart?

Could anyone please help me with this?

WhiteHat WhiteHat · Accepted Answer · 2017-09-16T17:48:53

try setting the option explicitly using vAxis, rather than all in one...

.setOption('vAxis.format', 'short')

How do I change the number format of the vertical axis? (when using the EmbeddedChartBuilder in Apps script spreadsheet service)

1 Answers