Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save hossam-fares/af96bc1cc29ae72d54d0fa17930519dc to your computer and use it in GitHub Desktop.
Save hossam-fares/af96bc1cc29ae72d54d0fa17930519dc to your computer and use it in GitHub Desktop.

How to add free crypto current price fetcher to your google sheet?

1- In your google sheet open Tools menu > script editor

Screenshot 2021-05-30 at 00 18 00

2- Remove anything on the editor then paste the follwing code:

  function cryptoCoinPrice(cryptoSymbol = 'BTC', refresh) {
  // Binance made a rate limiter on number of requests per second this sleep function helps in avoid call binanace api simultaneously
  sleep(getRandomInt(1,4))
  var cryptoSymbolToUSD = (cryptoSymbol+'USDT').toUpperCase()
  var random = getRandomInt(100, 400)//to distribute calls between binance 3 servers
  var apiUrl = 'https://api'+random+'.binance.com/api/v3/avgPrice?symbol=' + cryptoSymbolToUSD

  var requestOptions = {
    muteHttpExceptions: true,
    validateHttpsCertificates: true,
    headers : {
      'Content-Type': 'application/json; charset=UTF-8',
      'X-MBX-APIKEY': '123445567890'
    }
  }
  var response = UrlFetchApp.fetch(apiUrl, requestOptions)
  var responseCode = response.getResponseCode()

  if (responseCode !== 200) {
    console.log(response.getContentText())
    throw new Error('API is not responding as expected, we got ' + responseCode + ' as status code')
  }

  data = JSON.parse(response.getContentText());
  return data['price'];  
}


function getRandomInt(min, max) {
  min = Math.ceil(min);
  max = Math.floor(max);
  return Math.floor(Math.random() * (max - min) + min); //The maximum is exclusive and the minimum is inclusive
}

function sleep(delay) {
    var start = new Date().getTime();
    while (new Date().getTime() < start + delay);
}

3- Click save and run 'you also see Authorization required pop up, you need to allow it'

Screenshot 2021-05-30 at 00 24 10

4- Back to your sheet, refresh it and you will see a new function you can use on the formula bar cryptoCurrentPrice("BTC")

Screenshot 2021-05-30 at 00 27 10

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment