Geek Answers Handbook

Switching rows and columns in EmbeddedChart

I use Google Docs and Google Apps Script to create an automatic 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 saw the option "Switch rows / columns", and the other option "Use column C for labels", I tried many options (for example, lChartBuilder.setOption("reverseCategories", true); or lChartBuilder.setOption("isStacked", true); ), but they seem to be all related to the last tab, I'm afraid, not the Start tab.

So, is there a way (other than transferring my data) to do this, or do I have to manually start the chart editor to fix this every time I create it?

Bonus question: how can I establish (via Google Apps Script) that the first row / column is the heading and serves as a legend?

+4
source share
2 answers

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

In the code below, I assume there is a sheet named "Scratch" that will be used to store a working copy of the transposed data. You can create and then hide this sheet so that it does not interfere.

 // 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 a bonus ... Having data formatted in this way, the first row / column is the heading and serves as a legend.

Resulting Chart

Now you still have problems to solve.

  • This code creates a new chart every time it runs, but you probably want to change it. (Instead, you will want to update the migrated data and change the chart to get a new range if it has changed.)
  • The X axis does not display all the task names - you can control this.

You say you don’t want to transfer your data. Unfortunately, with the current incarnation of diagrams, there is simply no way to move — you must massage your data yourself. Inserting a widget with a different graphics library may work, but widget support for sheets is deprecated. Fortunately, I already had a similar code, so the work was less than it seems. You should look at some other filtering options in ArrayLib, you can do a lot with it.

+4
source

For the bonus question, I have your bonus answer. Use this parameter when creating / changing a chart:

 setOption('useFirstColumnAsDomain','true')
+4
source

More articles:


All Articles