Little function for your Googlesheet


#1

Hey guyz, I didn’t know exactly where to put this but I think it can be useful.

I created a little function that you can use as a Google Sheet function to get the price fetch directly from the CMC api (I can change for Coinpuffs but didn’t find any api docs).

So here it is:

/**
 * Get crypto price
 */
function GET_PRICE(crypto, currency) {
  if(!currency) {
    currency = "price_eur";
  }
  if(!crypto) {
    crypto = "bitcoin";
  }
  var response = UrlFetchApp.fetch("https://api.coinmarketcap.com/v1/ticker/" + crypto + "?convert=EUR");
  var w = JSON.parse(response.getContentText())[0];
  return +w[currency];
}

Edit 2: Thanks to @Blynker I now know I can use Markdown for code :smiley:

To install it, go to tools > Script editor and copy paste the function. You can rename it and you may need to set some acces (save it and “play” it, it will ask you about the authorisation).

Now you can go back to your sheet and in a cell type: =GET_PRICE("litecoin") or =GET_PRICE("ethereum"; "price_usd") to see the magic happen \o/

Edit 3: This event trigger below doesn’t work properly, please check the the answer below to make it work if you need an automatic refresh every minutes.
Now you can go back to the script editor to set a trigger every minute and click the little clock close by the play icon. See the screeshot attached (sorry for the french, but ask me if it’s not clear enough)

Cheers!


#2
/**
 * Get crypto price
 */
function GET_PRICE(crypto, currency) {
  if(!currency) {
    currency = "price_eur";
  }
  if(!crypto) {
    crypto = "bitcoin";
  }
  var response = UrlFetchApp.fetch("https://api.coinmarketcap.com/v1/ticker/" + crypto + "?convert=EUR");
  var w = JSON.parse(response.getContentText())[0];
  return +w[currency];
}

#3
Now you can go back to your sheet and in a cell type:  =GET_PRICE("litecoin") or 
=GET_PRICE("ethereum"; "price_usd") to see the magic happen \o/

#4

@Guillaume there seems to be a bug somewhere within your code.
Image below is the execution log which shows when the script is run from the editor it is OK but fails from the 1 minute timer.

The error is this line:

var response = UrlFetchApp.fetch("https://api.coinmarketcap.com/v1/ticker/" + crypto + "?convert=EUR");

If I “hard code” it to the following the error disappears.

var response = UrlFetchApp.fetch("https://api.coinmarketcap.com/v1/ticker/bitcoin");

Obviously hard coding the ticker is not very useful and only my hack to fix the timer error.
I can’t see anything wrong with the line of code but Googlesheet scripting doesn’t like it when processed by the timer.
Any ideas?


#5

Thanks for the feedback, I learned a few things durring the process :smiley:

It appears that the timed function event just call the function on its own context so it’s not really useful to call it every minute…

Instead I added a few things to make it work

Here is the new functionS:

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Refresh",
    functionName : "refresh"
  }];
  sheet.addMenu("Refresh", entries);
};

function refresh() {
  SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange('Z3000').setValue(new Date().toTimeString());
  SpreadsheetApp.flush();
}

/**
 * Get crypto price
 * @param {sheet} refreshCell, Name of you first sheet + Z3000. i.e: SHEET1!Z3000
 * @param {string} crypto, Name of the crypto you want. i.e: bitcoin, ethereum, litecoin..
 * @param {string} currency, returned currency. Default to EUR. Can be: price_btc, price_usd, price_eur
 */
function GET_PRICE(refreshCell, crypto, currency) {
  if(!currency) {
    currency = "price_eur";
  }
  if(!crypto) {
    crypto = "bitcoin";
  }
  Logger.log(refreshCell, currency, crypto);
  var response = UrlFetchApp.fetch("https://api.coinmarketcap.com/v1/ticker/" + crypto + "?convert=EUR");
  var prices = JSON.parse(response.getContentText())[0];

  return +prices[currency];
}

The GET_PRICE function take a new first mandatory argument corresponding to the first sheet of your SpreadSheet and the cell Z3000 so for exemple: =GET_PRICE(SHEET1!Z3000; "bitcoin"; "price_usd") where SHEET1 is the name of your first Sheet.

And You can create a Time based event on the function refresh so it will update the SHEET1!Z3000 cell every 1 or 5minutes. And a refresh button is also added on top on the Speadsheet to update the price manualy on 1 click.

That was really fun and if you see a better way to do, please let me know :smiley:


#6

Thanks @Guillaume I will take a look asap.


#7

@Guillaume all working fine now, thanks.
Google%20Sheet%20Crypto


#8

These a built into Google Sheets already there’s no reason to write custom functions.


#9

@tsol so Google Sheets has a crypto api call function, wow. Let’s have the details then.


#10

yes do tell…next level google spreadsheeting is so sexy


#11

Ah, I found the built in function.


#12

AFAIK, the built-in function only support bitcoin: =GOOGLEFINANCE("BTCUSD") are you talking about another function?

(Anyway, that was interesting to do, I had no idea I could go that far with Google Sheets :smiley: )


#13

@Guillaume Google Sheets can be quite powerful in the right hands. I hacked your code to use the Binance API so it can be used to give a permanently updating portfolio value or as an aid for buy and sell triggers etc.


#14

=INDEX(IMPORTHTML("https://coinmarketcap.com/assets/bitcoin/","table",0),2,5)

You can then use the built-in Google finance to convert to other currencies.

You can basically grab any token price from coinmarketcap using this method and work around that to customize your spreadsheet without the need to add in scripts.


#15

Gives a formula parse error.


#16

Yeah, seems when copied from here it throws an error because of the quotes from the editor. Try removing the quotes and adding them back in.


#17
=INDEX(IMPORTHTML("https://coinmarketcap.com/assets/bitcoin/","table",0),2,5)

Works ok.


#18

@tsol without scripting how are you getting the data to refresh?


#19

Thanks, didn’t see edit features on my phone. There could be a delay of up to 20min but afaik it updates on price change. I only use it as a guide to check every now and then so it’s not that important to have to the minute data.


#20

No updates in the last 30 minutes and I think that’s why scripting is much more powerful, albeit a bit more technical.
The 1 minute scripting refresh is pretty good but I use a 1s refresh with an MCU and a smartphone app.
What do the 0, 2 and 5 relate to in the IMPORTHTML function?