1

I am trying to create line charts with a log scale from a google spreadsheet by using google apps scripts. I am not a developer so I use "Record Macros" function to get a starting point.

My data set looks like this https://docs.google.com/spreadsheets/d/1JKcpXtEbQj_4qzf20XWcDwDOoBI8ZlSXZdpzOKY5kt4/edit?usp=sharing

This is what is returned by Recording Macros:

function CreateChart() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRangeList(['A1:C1', 'A2:C4']).activate();
  var sheet = spreadsheet.getActiveSheet();
  var chart = sheet.newChart()
  .asLineChart()
  .addRange(spreadsheet.getRange('A1:C1'))
  .addRange(spreadsheet.getRange('A2:C4'))
  .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_ROWS)
  .setTransposeRowsAndColumns(true)
  .setNumHeaders(-1)
  .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
  .setOption('vAxes.0.gridlines.count', 10)
  .useLogScale()
  .setOption('height', 271)
  .setOption('width', 439)
  .setPosition(1, 6, 76, 16)
  .build();
  sheet.insertChart(chart);
};

However when I tried to run this script from the script editor, even though the chart was created, the .useLogScale()" setting is not respected at all. I also try .setOption('vAxes.0.scaleType', 'log') and .setOption('vAxes.0.scaleType', 'mirrorLog') and still it doesn't work.

How can I solve this problem? Thank you!

Trang Ngoc Nguyen
  • 11
  • 2
  • https://developers.google.com/apps-script/reference/spreadsheet/embedded-line-chart-builder#useLogScale() Are all ranges numbers and positive? Are you sure the vertical axis isn't logarithmic? Were you able to use logScale manually without scripts? – TheMaster Apr 25 '19 at 09:14
  • Hi! Yes all the numbers are positive, bigger than 1000 at least, and the vertical axis isn't logarithmic. I can use Log Scale manually by the chart editor, but not by scripts. I also tried all what I can found in the internet, including the link you posted above, but still cannot find out what is wrong. – Trang Ngoc Nguyen Apr 25 '19 at 09:43

1 Answers1

0

The correct option seems to be 

.setOption('vAxes.0.logScale',true)
TheMaster
  • 45,448
  • 6
  • 62
  • 85