I am trying to import the following JSON into Google sheets and have the results split into two columns. The data is coin price data from Coingecko.
https://api.coingecko.com/api/v3/coins/invictus/market_chart?vs_currency=usd&days=30&interval=daily
The JSON returns UNIX timestamp data and price in the following format:
"prices":[timestamp , price],[timestamp , price]....
I can import the JSON using the code from https://blog.fastfedora.com/projects/import-json like this: =ImportJSON("https://api.coingecko.com/api/v3/coins/invictus/market_chart?vs_currency=usd&days=30&interval=daily", "/prices", "noInherit,noTruncate", doNotDelete!$A$1)
However it returns the data as one long string in cell A:2. For example:
1638144000000,746.6771503601128,1638230400000,790.8738166468181,1638316800000,813.0212755328346,1638403200000,747.1829923418416,1638489600000,603.2418788487082,1638576000000,527.9869145332356,1638662400000,457.35694186735213,1638748800000,459.1020634102568,1638835200000,379.45955893531516,1638921600000,367.4285592864082,1639008000000,332.6569958807504,1639094400000,360.31721655542077,1639180800000,353.72371970970227,1639267200000,311.1900754529333,1639353600000,304.50307298537416,1639440000000,245.09538112807542,1639526400000,236.7967986455728,1639612800000,256.7026205778511,1639699200000,258.6393225791494,1639785600000,267.78476662611047,1639872000000,286.931173745379,1639958400000,307.5668050495342,1640044800000,326.8786486489706,1640131200000,378.46907838034076,1640217600000,398.40479671686626,1640304000000,366.45154529552616,1640390400000,316.05924359618723,1640476800000,329.5910997574541,1640563200000,305.52573964071496,1640649600000,280.05521099343133,1640688200000,265.71741103257415
I would like some help parsing this data into 2 columns, column A being UNIX timestamp, and column B being Prices
Simply way to parse the json of coingecko (note that you are limited in number of request)
function myFunction(){
url = 'https://api.coingecko.com/api/v3/coins/invictus/market_chart?vs_currency=usd&days=30&interval=daily'
var json = JSON.parse(UrlFetchApp.fetch(url).getContentText())
var data = json.prices
var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
sh.getRange(2,1,data.length,data[0].length).setValues(data)
}