JSON import - Cannot read property 'length' of undefined

Question

I'm trying to import JSON via API into Google sheets but get the error

Cannot read property 'length' of undefined

Here is my code

function importRank(){
  url = 'https://public-api.solscan.io/token/holders?tokenAddress=sinjBMHhAuvywW3o87uXHswuRXb3c7TfqgAdocedtDj&offset=0&limit=max'
  var json = JSON.parse(UrlFetchApp.fetch(url).getContentText())
  var data = json.data.owner
  var data2 = json.data.rank
  var data3 = json.data.total
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('WalletRank')
  sh.getRange(2,1,data.length,data[0].length).setValues(data)
  sh.getRange(2,2,data2.length,data2[0].length).setValues(data2)
  sh.getRange(2,3,data3.length,data3[0].length).setValues(data3)
}

Does this answer your question? [How can I test a trigger function in GAS?](https://stackoverflow.com/questions/16089041/how-can-i-test-a-trigger-function-in-gas) — Mr Shane, Jan 01 '22 at 11:11
You want to retrieve all 24159 data from the API and put them to the Spreadsheet. Is my understanding correct? — Tanaike, Jan 02 '22 at 00:42
Yes - OR look up a value in my spreadsheet and return that row from the API — gold, Jan 02 '22 at 20:44
Thank you for replying. I have to apologize for my poor English skill. Unfortunately, I couldn't understand `OR look up a value in my spreadsheet and return that row from the API`. But I understood that your issue has already been resolved. I'm glad about it. — Tanaike, Jan 03 '22 at 00:07

Mike Steelson · Accepted Answer · 2022-01-01T17:02:13.490

0

Try

function importRank(){
  url = 'https://public-api.solscan.io/token/holders?tokenAddress=sinjBMHhAuvywW3o87uXHswuRXb3c7TfqgAdocedtDj&offset=0&limit=max'
  var json = JSON.parse(UrlFetchApp.fetch(url).getContentText())
  var data=[]
  Logger.log(json.total)
  json.data.forEach(function(x){
    data.push([x.owner,x.rank,x.amount])
  })
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('WalletRank')
  sh.getRange(2,1,data.length,data[0].length).setValues(data)
  sh.getRange(2,4).setValue(json.total)
}

Explanation

the structure is as follows

{"data":[
{"address":"__________","amount":________,"decimals":__,"owner":"___","rank":_},
...
],"total":_____}

that means that data is an array [] of multi elements which contains each of them {} address, amount, decimals, owner and rank

Reference

forEach

edited Jan 01 '22 at 17:02

answered Jan 01 '22 at 12:11

Mike Steelson

14,650
2
5
20

Thank you! Almost perfect - but there are 2 issues: 1. Data import is limited to 5000 rows, whereas the JSON has over 20K rows. I suspect this is a pagination issue. 2. Missing data for "total" which should appear in cell D2. – gold Jan 01 '22 at 14:27
In fact given the pagination issues it might be easier to log the JSON data and then perform a lookup of a given "owner" (found in cell Index!C9 in my sheet) for the corresponding"rank". – gold Jan 01 '22 at 14:36
total can be fetch by `json.total` I have amended my answer – Mike Steelson Jan 01 '22 at 17:03
if you can give me url other pages i can put them all together – Mike Steelson Jan 01 '22 at 17:04
That is very helpful thankyou! There are no other URLs - if you look at the API data at that URL in a browser you will see it has +20K rows. – gold Jan 01 '22 at 19:36
I have downloaded the file with IE, Edge, and Chrome, and the last item is the rank 5000 owner FmWtbvHAnwpnFytVnxagDa3vG1HZdyM9DeQ65DkJB4LG. I can't see anything else. Please provide a screenshot in the request. – Mike Steelson Jan 01 '22 at 22:52
even if you try to fetch with &offset=0&limit=20000 parameters, the site will return only 5000 – Mike Steelson Jan 01 '22 at 22:58
Thank you - you are correct! – gold Jan 02 '22 at 21:20

JSON import - Cannot read property 'length' of undefined

1 Answers1

Explanation

Reference