James Dickens James Dickens - 7 months ago 21
Javascript Question

Fastest Way to Populate Arrays with Columns Google Sheets

Lets suppose I have a Google Sheet with data in columns that are different lengths but have no blanks between contiguous vertical cells. I am trying to only have one call to the getValues() method of my spread sheet, say for the first column, although I would like to do this for all columns. The following code is very slow to run in my editor :

function myFunction() {
var InfoSheetIterator = DriveApp.getFilesByName("InfoSheets");
var InfoSheetFile = InfoSheetIterator.next();
var InfoSheet = SpreadsheetApp.open(InfoSheetFile);

var DataRange = InfoSheet.getDataRange().getValues();
var ChannelList = DataRange;
var ChannelListArray = new Array();
var i = 0;
while (DataRange[i][0] != "" || DataRange[i][0] != undefined || DataRange[i][0] != null) {

Is there a faster way of doing this? I have been warned against using too many getValues() calls as I am trying to optimize. The array that is returned by getValues() in this case named DataRange is such that DataRange[0] returns all values in the first row, but I want all values in the first column. Perhaps I could just invert the columns and the rows but I think that would make the original sheet larger.


When you do DataRange[0], what you get is a matrix with one line, an array of arrays:

DataRange[0] //=> [[value_row1], [value_row2], [value_row3], ...] 

I guess that what you want is to get the flattened array of values of the first column:

[value_row1, value_row2, value_row3, ...] 

For that, I use an utility function I called flatten. It turns matrixes in one dimension arrays. Here it is:

function flatten(matrix){
  return [].concat.apply([], matrix);

then you do this:


and it will output the flattened column values: [val_row1, val_row2, val_row3, ...]