James Dickens James Dickens - 5 months ago 19
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) {
ChannelListArray.push(DataRange[i][0]);
}
Logger.Log(ChannelListArray.toString());
}
}


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.

Answer

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:

flatten(DataRange[0]) 

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

Comments