First of all, hats off to Kevin Xu and Pablo Yablo for creating the sheets this solution is based on. You can see and copy their sheets for your own use here: Cryptocurrency Value Calculator and here: Cryptocurrencies Calculator Template respectively.
These two sheets are awesome, they use API calls within Google Sheets to query price information every few minutes (each sheet has different intervals) and use that information to keep you informed of the value of your crypto holdings as well as price movements and comparisons between different coins. They're both super customizable and I really can't recommend them enough. The only problem, which really is no fault of the authors, is that Google Sheets sets a limit for the number of calls you can make to an individual URL within a certain amount of time (I'm still not sure what that limit is, but I've hit it enough times to know it's relatively small), so sooner or later, most users have found that their sheets stop updating more than once every day or two.
Obviously that's not cool, so let's take a look at how to fix the issue and keep your sheets updating.
The underlying issue is that Google is limiting calls to the API URL, so what we need to do is trick it into thinking that new calls are being made to a different URL. This is accomplished by tacking an arbitrary string onto the end of the URL. The string has no bearing on the information retrieved, it simply makes the URL technically different from the one you called previously and therefore Google Sheets makes the call rather than returning cached data as it would otherwise. Your first response might be to try tacking something like a timestamp onto the end of the URL, but unfortunately Google is one step ahead of you on that and won't allow you to reference any cell with a NOW() or RAND() reference, so automatically generating the arbitrary string is unfortunately out of the question. What we can do is create a list of strings that we can manually cycle through when the API call limit for one URL is reached and we need a new one in order to continue getting our updates. Here are the steps to get you on your way.
Add a new field somewhere on the sheet. If you just want to use random numbers or words, I suggest you place it somewhere on the sheet that you won't be looking at frequently. I chose to use currency exchange rates because I like to look at those anyway, so my field is right at B2.
Use data validation (data -> data validation) to create a list of items. It can be 1,2,3,4,5 or your the names of your kids, or whatever you please. Mine is ticker symbols for a few currencies that I like to track.
(MOST USERS CAN SKIP THIS STEP) Because I want to get exchange rates, I added another cell (C2) with the formula =GOOGLEFINANCE(("CURRENCY:"&B2&"USD")) where B2 is the cell where I store the ticker symbols
Modify the formulas in your price column
Most formulas will look something like this:
=VALUE(ImportJSON("https://min-api.cryptocompare.com/data/price?fsym=BTC&tsyms=USD&e=Coinbase","/USD","noHeaders",$C$2))
You need to change the $C$2 (or whatever value is in that position in your formula) to the cell where you're storing your random strings. For me it's $C$2 but I could also set it to $B$2 since both will change in the future.
- Once that change has been made, assuming you got the syntax right, your formula should update with the new data. In the future, any time your sheet stops updating (once a day or so in my experience), just go to the cell you referenced and select a new value from the drop down list. Your cells should all automatically update again and you should be on your way!
If you have any questions or can't get it working, feel free to leave a reply and I'll help you get it worked out!