Parsing Error trying to import Coinbase Pro API into Google Sheets

2
votes

New to APIs in Google sheets, but I feel like I'm 95% of the way to where I'm trying to go.

I'm trying to pull crypto data into my spreadsheet to do a simple 24 hour price comparison and gauge whether the price has gone up or down, maybe use some conditional code to change the background to green or red. Simple enough. Most of the sites that offer APIs have given me various errors, though, so coinbase pro (and weirdly the deprecated gdax) have been most reliable (although I haven't ruled out that it started breaking because I'm now putting in too many call requests).

Found this as a way to get the current price of ETH, for instance:

=VALUE(SUBSTITUTE(SUBSTITUTE(INDEX(IMPORTDATA("https://api.gdax.com/products/ETH-USD/ticker"),0,2), "price:",""), """", ""))

Works like a charm. So I changed the request to target different info, specifically the 24hr stats listed on the API doc, and the first value in that section, "open" for opening price (this ensures I get the price exactly 24hrs earlier). But I got a weird parsing error using the request, which is here:

=VALUE(SUBSTITUTE(SUBSTITUTE(INDEX(IMPORTDATA("https://api.pro.coinbase.com/products/ETH-USD/stats"),0,1), "open:",""), """", ""))

I've figured out the issue, but not the solution. Google Sheets says I am pulling in text. Because the "open" (opening price) value is the first listed in the JSON code, it is pulling in the code bracket from the nested HTML/JSON code. For instance, it says I can't parse "{open" into a number. And I get the same problem in reverse when I pull the last value listed in the stats section, which is "volume_30day:"

=VALUE(SUBSTITUTE(SUBSTITUTE(INDEX(IMPORTDATA("https://api.pro.coinbase.com/products/ETH-USD/stats"),0,6), "volume_30day:",""), """", ""))

This returns an error saying "volume_30day: #}" can't be parsed, so now it is the closing bracket of the JSON code. So I can't use "open" the first item in the API 24hr stats section, or Volume_30day, which is the sixth item on that list, but items 2-5 work just fine. Seems super weird to me, but I've tested it and it is seems to be what's going on.

There must be something stupid I need to tweak here, but I don't know what it is.

1
google-sheetsgoogle-sheets-api

1 Answers

1
votes

Answer 1:

About =VALUE(SUBSTITUTE(SUBSTITUTE(INDEX(IMPORTDATA("https://api.pro.coinbase.com/products/ETH-USD/stats"),0,1), "open:",""), """", ""))

  • When I checked =SUBSTITUTE(SUBSTITUTE(INDEX(IMPORTDATA("https://api.pro.coinbase.com/products/ETH-USD/stats"),0,1), "open:",""), """", ""), the value is {open:617. I think that when by this, when VALUE is used for the value, the error occurs.

In order to retrieve the values you expect, I would like to propose to use REGEXREPLACE instead of SUBSTITUTE. The modified formula is as follows.

=VALUE(REGEXREPLACE(INDEX(IMPORTDATA("https://api.pro.coinbase.com/products/ETH-USD/stats"),0,1), "open|""|{|}|:",""))
  • In this modified formula, open|""|{|}|: is used as the regex. These are replaced with "".
  • In this case, I think that =VALUE(REGEXEXTRACT(INDEX(IMPORTDATA("https://api.pro.coinbase.com/products/ETH-USD/stats"),0,1), "\d+")) can be also used. But when I thought about your 2nd question, I thought that above formula might be useful.

Result:

enter image description here

Answer 2:

About =VALUE(SUBSTITUTE(SUBSTITUTE(INDEX(IMPORTDATA("https://api.pro.coinbase.com/products/ETH-USD/stats"),0,6), "volume_30day:",""), """", ""))

  • When I checked =SUBSTITUTE(SUBSTITUTE(INDEX(IMPORTDATA("https://api.pro.coinbase.com/products/ETH-USD/stats"),0,6), "volume_30day:",""), """", ""), the value is 7101445.64098932}. I think that when by this, when VALUE is used for the value, the error occurs.

In order to retrieve the values you expect, I would like to propose to use REGEXREPLACE instead of SUBSTITUTE. The modified formula is as follows.

=VALUE(REGEXREPLACE(INDEX(IMPORTDATA("https://api.pro.coinbase.com/products/ETH-USD/stats"),0,6), "volume_30day|""|{|}|:",""))
  • In this modified formula, volume_30day|""|{|}|: is used as the regex. These are replaced with "".
  • In this regex, it can use by replacing open of open|""|{|}|: to volume_30day at above regex.

Result:

enter image description here

Other pattern 1:

As other pattern using the built-in formula, how about the following modified formulas?

=VALUE(TEXTJOIN("",TRUE,ARRAYFORMULA(IFERROR(VALUE(REGEXREPLACE(IMPORTDATA("https://api.pro.coinbase.com/products/ETH-USD/stats"), "open|""|{|}|:","")),""))))

=VALUE(TEXTJOIN("",TRUE,ARRAYFORMULA(IFERROR(VALUE(REGEXREPLACE(IMPORTDATA("https://api.pro.coinbase.com/products/ETH-USD/stats"), "volume_30day|""|{|}|:","")),""))))

In these formulas, the values can be retrieved by replacing KEY of KEY|""|{|}|: of the regex.

Other pattern 2:

The returned value from https://api.pro.coinbase.com/products/ETH-USD/stats is the JSON value. So in this case, when the custom function created by Google Apps Script can be also used.

The Google Apps Script is as follows.

const SAMPLE = (url, key) => JSON.parse(UrlFetchApp.fetch(url).getContentText())[key] || "no value";

When you use this script, please copy and paste the above script to the script editor of Spreadsheet and save it. And please put the custom function like =SAMPLE("https://api.pro.coinbase.com/products/ETH-USD/stats","open") and =SAMPLE("https://api.gdax.com/products/ETH-USD/ticker","price") to a cell. By this, the value can be retrieved.

References: