Autoscale the Vertical Axis in Google Sheets Chart
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:
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.