Fastest way to populate an arrays from spreadsheet columns

Question

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.

Mauricio Moraes Mauricio Moraes · Accepted Answer · 2016-06-15T16:32:46

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, ...]

Fastest way to populate an arrays from spreadsheet columns

1 Answers