CODEWITHSUNDEEP
google-apps-scriptgoogle-visualizationgoogle-sheets
glatapoui
glatapoui

Reputation: 383

Switch rows and columns in EmbeddedChart

I am using Google Docs and Google Apps Script to make some auto generated report for our sprint.

I would like to add a Chart to my Sheet, and everything works fine with the following code:

    var lChartBuilder = SpreadsheetApp.getActiveSheet().newChart();
    lChartBuilder.addRange(SpreadsheetApp.getActive().getRange("Tasks!C39:S40"));
    lChartBuilder.setOption("title", "Task Burndown");

    var lChart = lChartBuilder.asLineChart().build();
    SpreadsheetApp.getActiveSheet().insertChart(lChart);

But my series are organized horizontally, not vertically. In the editor I have seen the option "Switch rows / columns" and the other option "Use column C for labels" I have tried many options (like lChartBuilder.setOption("reverseCategories", true); or lChartBuilder.setOption("isStacked", true);), but they seem all related to the last tab, I fear, not the Start tab.

So, is there a way (other than transposing my data) to do that or must I fire the chart editor manually to fix this each time I generate it?

Bonus question: how do I then set (through Google Apps Script as well) that the first row/column is a header and serves as legend?

Upvotes: 4

Views: 3937

Answers (3)

Seiya
Seiya

Reputation: 141

For "Switch rows / columns", use .setTransposeRowsAndColumns(true). For "Use column C for labels", use .setNumHeaders(1). Here is a complete example:

const chart = sheet
  .newChart()
  .asColumnChart()
  .setStacked()
  .addRange(range)
  .setNumHeaders(1)
  .setTransposeRowsAndColumns(true)
  .setPosition(sheet.getLastRow() + 2, 1, 0, 0)
  .build();

Upvotes: 0

Mogsdad
Mogsdad

Reputation: 45710

For an embedded chart, you will need to transpose your data, because the embedded chart uses spreadsheet data, and does not support transposition itself. However, you can handle this programmatically, and the transpose operation itself is simple.

In the code below, I'm assuming existence of a sheet named "Scratch" that will be used to store the working copy of the transposed data. You can create and then hide this sheet, so that it's not in the way.

// Uses 2D Arrays Library, see https://sites.google.com/site/scriptsexamples/custom-methods/2d-arrays-library

function buildChart() {
  var taskSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tasks");
  var lChartBuilder = taskSheet.newChart(); taskSheet.removeChart(chart)
  var srcData = taskSheet.getRange("Tasks!C39:S40").getValues();

  // Transpose the table (using 2D Array Library)
  var scratchData = ArrayLib.transpose(srcData);

  var numRows = scratchData.length;
  var numCols = scratchData[0].length; // assume all rows are same width

  // Write scratch values to scratch sheet.
  var scratchSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Scratch");
  scratchSheet.getRange(1, 1, numRows, numCols).setValues( scratchData );
  SpreadsheetApp.flush();

  lChartBuilder.addRange(scratchSheet.getDataRange());
  lChartBuilder.setOption("title", "Task Burndown");

  var lChart = lChartBuilder.asLineChart().setPosition(1, 1, 1, 1).build();
  taskSheet.insertChart(lChart);
};

For the bonus... Having the data formatted this way ensures the first row/column is a header and serves as legend.

Resulting Chart

Now, you've still got some problems to solve.

  • This code creates a new chart every time it runs, but you probably want to modify it instead. (Instead, you'll want to update the transposed data, and modify the chart to pick up the new range, if it changed.)
  • The X axis doesn't show all the task names - you may be able to control that.

You say you did not want to transpose your data. Unfortunately, there just isn't any way around that with the current incarnation of Charts - you've got to massage your data yourself. Inserting a widget with a different charting library could work, but widget support for Sheets has been deprecated. Fortunately, I had similar code already, so it was less work than it looks like. You should look at some of the other filtering capabilities in ArrayLib, you can do a lot with it.

Upvotes: 5

user2956384
user2956384

Reputation:

For the bonus question, I have your bonus answer. Use this option when creating/modifying your graph:

setOption('useFirstColumnAsDomain','true')

Upvotes: 6

Related Questions