jsonparsinggoogle-sheets

Parse JSON import data string into two columns


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


Solution

  • 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)
    }
    

    enter image description here