Google spreadsheet function (app script) to fetch crypto exchange rate using coin api with cache

exchangeRate.js

// 1. get your coin api key, free key is limited to 100 req/day
// 2. add AppScript on google spreadsheet
// 3. save & call function `exchangeRate("BTC")` to get the current exchange rate

// refer to the previous gist for function withou cache.
const cacheHours = 6
const cacheMsecs = cacheHours * 60 * 60 * 1000

function exchangeRate(ticker) {
  const cache = PropertiesService.getScriptProperties()
  const cachedData = cache.getProperties()
  const prev = cachedData[ticker] && JSON.parse(cachedData[ticker]) || {}

  const isValidCache = prev['at'] && parseInt(prev['at']) + cacheMsecs > Date.now()
  if(isValidCache){
    return prev['rate']
  }

  const endpoint = `https://rest.coinapi.io/v1/exchangerate/${ticker}/USD`
  const options = {
    headers: {
      "X-CoinAPI-Key": "XXXXX"
    }
  }
  const response = UrlFetchApp.fetch(endpoint, options)
  const text = response.getContentText()
  const json = JSON.parse(text)
  const rate = json['rate']
  cache.setProperties({
    ...cachedData,
    [ticker]: JSON.stringify({
      rate: rate,
      at: Date.now()
    })
  });
  return json['rate']
}

Bonus for your shell (.zshrc / .bashrc)

rate(){
  curl "https://rest.coinapi.io/v1/exchangerate/$1/USD" --request GET --header "X-CoinAPI-Key: XXXXXX"
}

usage: $ rate BTC

I found it's pretty useful since you can also convert fiat <-> fiat like USD <-> JPY.