How can I create a javascript array froma range in a google sheet?

Question

I want to take a range from a Google spreadsheet, create a javascript array and then pass each item, form the range, into the array.

The values in the range (A5: A10) are, for example, bitcoin-cash, litecoin, ethereum, monero, dash, quantstamp.

My code is as follows:

function appendCoins() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var r = sheet.getRange('A5:A10').getValues();
  var coins = [];
  for (var i=0;i < r.length-1;i++) {
     coins.push(r[0][i]);
    }
    Logger.log(coins)
}

The logger shows me this:

[18-01-10 18:09:07:218 GMT] [bitcoin-cash, null, null, null, null, null]

So the first one is correct, it loops the correct number of times but all the other values are undefined.

Why is this? It should be straightforward. Can anybody help? Thank you.

I'd be tempted to throw in a console.dir(r) just before "coins.push(r[0][1])". — user4490801
– user4490801, Commented Jan 10, 2018 at 18:24
@AnthonyHaffey: Doesn't Google Sheets have a proper debugger? — T.J. Crowder
– T.J. Crowder, Commented Jan 10, 2018 at 18:25
@T.J.Crowder - maybe I'm just making a n00b suggestion on how to debug in this instance. Just the way that seemed easiest to me to work why null values were coming out. — user4490801
– user4490801, Commented Jan 10, 2018 at 18:33
@AnthonyHaffey: I was really hoping you were going to say "yes, it does" and point me at it. :-D — T.J. Crowder
– T.J. Crowder, Commented Jan 10, 2018 at 18:46
@T.J.Crowder It does, see console.cloud.google.com/debug, but you need to have deployed applications to use it, just a Google Apps script seems not to show up in the drop-down list. — vinnief
– vinnief, Commented Feb 22, 2019 at 13:09

T.J. Crowder · Accepted Answer · 2018-01-10 18:24:06Z

1

getValues returns the range indexed by row, then column. Your range, A5:A10, has five rows and one column. You're treating it as though it had one row and five columns. Swap 0 and i in your code:

function appendCoins() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var r = sheet.getRange('A5:A10').getValues();
  var coins = [];
  for (var i=0;i < r.length-1;i++) {
     coins.push(r[i][0]);
  }
  Logger.log(coins)
}

answered Jan 10, 2018 at 18:24

T.J. Crowder

1.1m201 gold badges2k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1903663 Over a year ago

Thank you, correct. Silly me. Very grateful. I will accept answer in 7 minutes!

Collectives™ on Stack Overflow

How can I create a javascript array froma range in a google sheet?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related