CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
dereks
dereks

Reputation: 564

Autoscale the Vertical Axis in Google Sheets Chart

Is it possible to automatically adjust the vertical axis boundaries to the changing data? enter image description here

I know that I can manually change the lower bound: enter image description here
But I do not know how to do it automatically using google sheets or using google apps script.

Upvotes: 3

Views: 3271

Answers (1)

NightEye
NightEye

Reputation: 11184

You can try this one in Apps Script:

Script:

function createEmbeddedLineChart() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var yAxis = sheet.getRange('Sheet1!A2:A4');
  var xAxis = sheet.getRange('Sheet1!B2:B4');
  var yAxisValues = yAxis.getValues().flat();

  // set lower and higher bound to be 100 away from max and min values (e.g. 100)
  var offset = 100;
  var minVal = Math.min.apply(Math, yAxisValues) - offset;
  var maxVal = Math.max.apply(Math, yAxisValues) + offset;

  // apply calculated bounds to vAxis min and max values
  var vAxisOptions = {
    minValue: minVal,
    maxValue: maxVal
  }
  
  var lineChartBuilder = sheet.newChart().asLineChart();
  var chart = lineChartBuilder
    .addRange(xAxis)
    .addRange(yAxis)
    .setPosition(3, 3, 0, 0)
    .setOption('vAxis', vAxisOptions)
    .build();
 
  sheet.insertChart(chart);  
}

Output:

output

Note:

  • Lower and upper bounds will automatically adjust 100 away from the minimum and maximum value of yAxis values respectively.
  • You can adjust the distance (offset) from the minimum and maximum values depending on what you need. I set it to 100 to mimic the sample you gave above.
  • To automatically update the chart's upper/lower bounds upon modifying the yAxis values, you might need to use triggers and recreate the chart whenever the yAxis range is updated.

Upvotes: 2

Related Questions