Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Setting minimum value in Google Spreadsheet Chart programmatically

I'm trying to set a fixed minimum value in a chart created programmatically in my Google Spreadsheet. The goal is that I want to create several graphs with the same limits even though their data is wildly different.

For the purposes of this example, I have the following data in my spreadsheet:

Date    Number
05.02.2017  125
06.02.2017  150
16.02.2017  21
05.02.2018  -5.333333333
06.02.2018  -57.33333333
16.02.2018  -109.3333333
05.02.2019  -161.3333333

and the following script:

function update() {
  var title = 'Last updated ' + new Date().toString();

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.AREA)
     .addRange(sheet.getRange("A1:B8"))
     .setPosition(5, 5, 0, 0)
     .setOption('title', title)
     .setOption('vAxis.minValue', -5000)
     .setOption('vAxis.viewWindow.min', -5000)
     .build();

  sheet.insertChart(chart);
}

... so, in other words, I'm trying to set the minimum value, to -5000. Setting vAxis.minValue and/or vAxis.viewWindow.min accomplishes exactly nothing. (Yes, I know that my code will create a new one every time update() is called, but that's not the point here.)

There is a minimum/maximum value option when the chart is edited. The values there are blank: enter image description here

What do I do to change these values programmatically?

Full link to sheet here: https://docs.google.com/spreadsheets/d/1dKBG8Nx5mypD2YAfOTCzo2cvIX6C7R18SCIsNB5FsT0/edit?usp=sharing

like image 891
Christian Neverdal Avatar asked Jul 05 '17 18:07

Christian Neverdal


People also ask

How do I change the minimum value in Google Sheets?

The MIN function returns the minimum value of a set of values. You can either input several values separated by a comma (e.g. =MIN(value1, value2, value3) ), or you can input a range of cells of which you want to know the minimum (e.g. =MIN(A1:A10) ).


Video Answer


1 Answers

To set the minimum value of vAxis to -5000, configure the option as setOption("vAxes", {0: { viewWindow: { min: -5000} }})

   function update() {
      var title = 'Last updated ' + new Date().toString();

      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheets()[0];

      var chart = sheet.newChart()
         .setChartType(Charts.ChartType.AREA)
         .addRange(sheet.getRange("A1:B8"))
         .setPosition(5, 5, 0, 0)
         .setOption('title', title)
         .setOption("vAxes", {0: { viewWindow: { min: -5000} }})
         .build();

      sheet.insertChart(chart);
    }
like image 74
Ritesh Nair Avatar answered Sep 28 '22 13:09

Ritesh Nair