Use full listings

This means don’t use =CRYPTOFINANCE("BTC/USD") and other similar calls, but call a full listing like =CRYPTOFINANCE("COINMARKETCAP") and reference from there the data you want.

Each call to CRYPTOFINANCE will do 1 API request. Google Sheets imposes a 20,000 API calls quota per day per user and per spreadsheet file. So using full listings will save you from hitting the quota and seeing urlfetch errors.

Here is how to use a full listing:

  1. Create a new sheet, name it cryptodata, then on cell A1 type the formula =CRYPTOFINANCE("COINMARKETCAP"). This will create as many rows as there are coins tracked on CoinMarketCap, over 1,600 as of now.
  2. From there you can just reference these data from your portfolio sheet like this =cryptodata!E2. This will show Bitcoin price as Bitcoin is on row 2 and price data are on column E.

As the row order may change at each refresh (they are sorted by marketcap), here is how to make your cell references sticky.

Note that the order of coins may change at each refresh, as they are sorted by marketcap. Here is how to make your cell references sticky:

=INDEX(cryptodata!A1:Z, MATCH("bitcoin",cryptodata!A1:A,0), 5)

Here is how it works, in order:

  1. MATCH will search for bitcoin in the sheet cryptodata on the column A and return the matching row number.
  2. INDEX will look at the cryptodata sheet and return the value that is at the row where bitcoin is, and at the column 5. Column 5 is where the price is, you can change it to 8 for marketcap info, and so on.

Improve formatting

Wrap you cell references with VALUE() to be able to format the cell.

So your references will look like, if you reference cell B4: =VALUE(B4).

If you’re using the full listing (you should), and use the sticky reference explained above, your reference looks like:

=VALUE(INDEX(cryptodata!A1:Z, MATCH("bitcoin",cryptodata!A1:A,0), 5))

Avoid using time-driven trigger

You might be tempted to code a small function, say to refresh the rates, and use a time-driven trigger to get your data refreshed each 5 minutes. The issue is that the rates will keep being refreshed even when the sheet is closed, and will make you hit the 20,000 API call quota too fast.

Many users, even with a small number of calls to CRYPTOFINANCE, are being blocked by the API call quota, even though simple math shows they should be from the quota limit.

Next step

Check out how to refresh the rates so that your sheet show the latest rates and data.