How do I set the X and Y axis of a EmbeddedChartBuilder-CodePudding

I have this app script function to create a new chart:

function newChart(range, sheet) {
  var chart = sheet.newChart()
    .setChartType(Charts.ChartType.LINE)
    .addRange(range)
    .setPosition(5, 5, 0, 0)
    .setOption("title", "Model run 1")
    .setOption("pointSize", 2)
    .setOption("lineWidth", 1)
    .build();
  sheet.insertChart(chart);
}

How would I change the x and y axis, I want to be able to specify what column of my range should be series and what the x axis. How would I do this?

CodePudding user response：

In your situation, how about the following sample situation and scripts?

The sample Spreadsheet is as follows.

In the following sample scripts, this Spreadsheet is used as a sample.

Create new chart.

Sample 1

function sample1() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var colA = sheet.getRange("A1:A6");
  var colB = sheet.getRange("B1:B6");

  var chart = sheet.newChart()
    .setChartType(Charts.ChartType.LINE)
    .addRange(colA)
    .addRange(colB)
    .setOption('useFirstColumnAsDomain', true) // This might not be required to be used.
    .setPosition(5, 5, 0, 0)
    .setOption("title", "Sample 1")
    .setOption("pointSize", 2)
    .setOption("lineWidth", 1)
    .build();
  sheet.insertChart(chart);
}

In this script, the following chart is created.

Sample 2

function sample2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var colA = sheet.getRange("A1:A6");
  var colB = sheet.getRange("B1:B6");

  var chart = sheet.newChart()
    .setChartType(Charts.ChartType.LINE)
    .addRange(colB)
    .addRange(colA)
    .setOption('useFirstColumnAsDomain', true) // This might not be required to be used.
    .setPosition(5, 5, 0, 0)
    .setOption("title", "Sample 2")
    .setOption("pointSize", 2)
    .setOption("lineWidth", 1)
    .build();
  sheet.insertChart(chart);
}

In this script, the following chart is created.

Update chart.

Sample 3

function sample3() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var chart = sheet.getCharts()[0];
  var ranges = chart.getRanges();
  var c = chart.modify();
  ranges.forEach(r => c.removeRange(r));
  ranges.reverse().forEach(r => c.addRange(r));
  sheet.updateChart(c.build());
}

When this script is run, the existing chart is updated. The ranges of the x and y axes are replaced to y and x, respectively.

Note:

In these sample scripts, in order to explain the flow of the script, it supposes that there are only 2 columns for the chart. Please be careful about this.