Get Array from Data Validated Range

Been happily grabbing data ranges from cell ranges in google sheets for ages, but never spotted that if you have a range where all the cells have data validation in them (e.g. a list of values or list from range), then even if they are blank, e.g. no selection has been made, GAS still counts them if you get the “length” of the array. This little snippet deals with that!

//gets range as array

var arr = SpreadsheetApp.getActive().getRange("A1:A10").getValues();

//if all data validated will return 10 values

var temparr = [];

for(i=0;i<arr.length;i++) {

if (arr[i] != "") { temparr.push(arr[i]); }

}

arr = temparr;

//arr now contains only an array of the cells with values in them

Some alternatives:

var myarray = SpreadsheetApp.getActive().getRange(“A1:A10”).getValues().filter(String);

or

var myarray = SpreadsheetApp.getActive().getRange(“A1:A10″).getValues().filter(Boolean);

or possibly both?

or

[”,’1′,’2′,3,,’4′,,undefined,,,’5′].join(”).split(”);

which gives

[“1″,”2″,”3″,”4″,”5”]