Struggling with importing JSON data to Google Spreadsheets using Google Script? I don't have much Java knowledge, so forgive me if this question seems a bit silly! However, I do have experience coding in VBA.
My current challenge involves pulling live exchange rates from a cryptocurrency exchange's API. While I was successful with a different exchange's API, the JSON structure here is slightly different.
The problematic JSON looks like this:
{"success":true,"message":"","result":{"Bid":0.00011437,"Ask":0.00011447,"Last":0.00011447}}Here's the code I'm using:
1 function pullJSON_XRP() {
2 var ss = SpreadsheetApp.getActiveSpreadsheet();
3 var sheets = ss.getSheets();
4 var sheet = ss.getActiveSheet();
5 Logger.log(sheets)
6 var url="https://bittrex.com/api/v1.1/public/getticker?market=BTC-XRP"; // JSON call URL
7
8 var response = UrlFetchApp.fetch(url);
9 var dataAll = JSON.parse(response.getContentText());
10 var dataSet = dataAll;
11 Logger.log(dataSet)
12 var rows = [],
13 data;
14 Logger.log(rows)
15
16 for (i = 0; i < dataSet.rows; i++) {
17 data = dataSet[i];
18 rows.push(data.result.Bid, data.result.Ask, data.result.Last); //JSON entities
19 }
20
21 dataRange = sheet.getRange(3, 1, rows.length, 3); // 4th Denotes total number of entites
22 dataRange.setValues(rows);
23 }
I encounter an error at row 21. The error message is still in Swedish despite changing my language settings on Google. It roughly translates to: "Range coordinates or size invalid." Initially, I thought the issue might be due to
dataRange = sheet.getRange(3, 1, rows.length, 3);rows.push(data.result.Bid, data.result.Ask, data.result.Last);Even after trying data.Bid, data.Ask, data.Last, the same error persists.
Your assistance would be highly valued! =)
Thank you for your time and consideration!
Sincerely,
Brian