Google Apps Script Macro Error Formatting Y Axis Min/Max Values

Viewed 382

I'm new to Apps Script, I apologize if this is a dumb question but I haven't been able to find an answer. I'm using google sheets to chart the temp of my PSU. I've created macros that select a data range, insert a chart and modifies the formatting of anything in the chart except for one item, the Y Axis Min Max value, which ironically is the only thing I am interested in changing.

Below is the macro, it does one thing, change the Min Max value to 40/90. For the first entry it enters NaN instead of the value. I tried changing this but didn't help.

I read something about changing the data format but I'm not really sure how to do that. I've tried doing that via the GUI while creating the macro but it didn't help.

Any guidance you can provide is greatly appreciated.

function FormatYAxis() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('B:B').activate();
  var sheet = spreadsheet.getActiveSheet();
  var charts = sheet.getCharts();
  var chart = charts[charts.length - 1];
  sheet.removeChart(chart);
  chart = sheet.newChart()
  .asLineChart()
  .addRange(spreadsheet.getRange('B1:B552'))
  .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
  .setTransposeRowsAndColumns(false)
  .setNumHeaders(1)
  .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
  .setOption('useFirstColumnAsDomain', false)
  .setOption('curveType', 'none')
  .setOption('domainAxis.direction', 1)
  .setOption('isStacked', 'false')
  .setOption('title', 'GPU Temp')
  .setRange(40, NaN)
  .setOption('vAxes.0.viewWindowMode', 'explicit')
  .setOption('series.0.labelInLegend', 'GPU Temp')
  .setPosition(3, 4, 58, 1)
  .build();
  sheet.insertChart(chart);
  charts = sheet.getCharts();
  chart = charts[charts.length - 1];
  sheet.removeChart(chart);
  chart = sheet.newChart()
  .asLineChart()
  .addRange(spreadsheet.getRange('B1:B552'))
  .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
  .setTransposeRowsAndColumns(false)
  .setNumHeaders(1)
  .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
  .setOption('useFirstColumnAsDomain', false)
  .setOption('curveType', 'none')
  .setOption('domainAxis.direction', 1)
  .setOption('isStacked', 'false')
  .setOption('title', 'GPU Temp')
  .setRange(40, 90)
  .setOption('vAxes.0.viewWindowMode', 'explicit')
  .setOption('series.0.labelInLegend', 'GPU Temp')
  .setPosition(3, 4, 58, 1)
  .build();
  sheet.insertChart(chart);
};
1 Answers

the following options will set the min and max values on the y-axis.
however, the values being plotted on the y-axis must be numeric, not strings...

.setOption('vAxis.viewWindow.min', 40)
.setOption('vAxis.viewWindow.max', 90)
Related