How do I create a Range class programatically in my google spreadsheet script?

Question

So I have this google script that I want to use to create charts in my spreadsheet. I'm basically programatically creating content (with the use of spreadsheet data) that I then want to plot. The way I used to do it is by filling one of the sheets with all the data and then using that data to plot, but I was hoping to skip that step and feed the javascript arrays directly into my addRange method.

So I've got a script that creates a new chart:

  // insert the scenario chart
  var scenarioChartBuilder = sheet.newChart();
  scenarioChartBuilder.setPosition(5, 6, 5, 5)
    .setChartType(Charts.ChartType.AREA)
    .addRange(rangeObject);
  sheet.insertChart(scenarioChartBuilder.build());

The problem is; how do I make "rangeObject", given that I only have javascript arrays, and don't want to use actual spreadsheet data? Or is there another way of plotting data that isn't actually in a spreadsheet?

If you feel Tim answer is helps then you have to accept it. It will be useful to feature googlers. How to accept — Gowri

Tim van Zonneveld Tim van Zonneveld · Accepted Answer · 2012-08-22T17:12:02

Range data is actually just a multidimensional array. So a rangeObject could just be defined like;

var rangeobject = [[data, data, data],[data, data, data]];

The first array represents the row and the second array the column data. programmatically you could get the data like;

var dataFirstRowSecondColumn = rangedata[0][1]; //0 indexed array!

So, to add a range is just to pass a multidimensional array (with content data). But beware ;-) When adding to a chart i would think that you would have to mind that each column would only contain on kind of data to be valid.

In code you could directly use my first example.

How do I create a Range class programatically in my google spreadsheet script?

1 Answers