How to dynamically adjust a Google Sheets chart vertical (y) axis minimum and maximum value to data set?

5
votes

I'm struggling to adjust a Google Sheets line chart so that the vertical y-axis automatically adjusts to my data set.

For example I do not want the y-axis to start at 0 but rather the minimum value in my data set and also the maximum value.

In my sheet I have a minimum value of 0.712 and a maximum value of 0.811. The ideal is if the charts y-axis can then adjust to start (minimum) from 0.712 minus 10% and y-axis maximum value 0.811 + 10%.

This should create a neat chart for the data I have. I'm struggling to do this and it seems like I cannot enter a formula or something on the chart's customise options under Vertical axis Min and Max. Simply entering hard values will not work since the data I'm charting is very dynamic, so the chart's vertical axis needs to adjust to data.

Any help will be greatly appreciated.

1
google-apps-scriptgoogle-sheetsgoogle-visualizationgoogle-sheets-charts
I checked on Excel vertical axis automatically adjusts to the data... if the minimum values become higher the chart minimum adjusts accordingly. There must be a way with Google Sheets. – Petrus
Anyone maybe some advice..? – Petrus

1 Answers

3
votes

Below piece of code maintaining min and max of two charts on one sheet.

 function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  mit = sheet.getRange(1,12).getValue();
  mat = sheet.getRange(1,13).getValue();
  mih = sheet.getRange(1,14).getValue();
  mah = sheet.getRange(1,15).getValue();
  var chart = sheet.getCharts()[0];
  chart = chart.modify()
    .setOption('vAxes.0.viewWindow.max', mat)
    .setOption('vAxes.0.viewWindow.min', mit)
    .build();
  sheet.updateChart(chart);
  var chart1 = sheet.getCharts()[1];
  chart1 = chart1.modify()
    .setOption('vAxes.0.viewWindow.max', mah)
    .setOption('vAxes.0.viewWindow.min', mih)
    .build();
  sheet.updateChart(chart1);
}