CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Edo
Edo

Reputation: 1589

How to get an array of values from the column of a spreadsheet?

getSheetValues(1, 1, maxRows, 1)

return an array of maxRows arrays with 1 or no elements If I set maxRow to 5 and I have values in the first 3 rows (1, 2 and 3) I'll get an Object[][]

[[1.0], [2.0], [3.0], [], []]

So I wrote some test code

function stripArray(arr){
  return arr.map(function(t){if(t.length){return t[0];}else{return undefined;} }).filter(function(n){ return n != undefined });
}

function testArray(){
  var arr=[[1.0], [2.0], [3.0], [], []];
  var strippedArr = stripArray(arr);
  Logger.log(strippedArr);
}

it works like a charm and it log 

[1.0, 2.0, 3.0]

Then my troubles begins. I wrote a function to work on the sheet.

function testSheet(){
  var ssId="XXX";
  var ss = SpreadsheetApp.openById(ssId);
  var sheet = ss.getSheetByName("YYY");
  var values = sheet.getSheetValues(1, 1, maxPortalToDeQueue, 1);
  Logger.log(values);
  Logger.log(stripArray(values));
}

I get some crazy values

[[1.0], [2.0], [3.0], [], []]
[1.0, 2.0, 3.0, , ]

Someone can help me to understand this behaviour and to get the data I'm looking for ?

Upvotes: 1

Views: 5215

Answers (1)

bruce
bruce

Reputation: 1408

Although [] looks like it should have a length 0, it actually is 1, but has no content. use this instead.

function stripArray(values) {
  return values.filter(function(d) {
    return d.length && d[0] !== '';
  });
}

Upvotes: 2

Related Questions