Crypto utilities for Google Sheets

Crypto.gs

function main() {
  console.log(cryptoPrice("blockstack", "0.1"));
}

/**
* Sets a random number in P1
* @customfunction
*/

function refresh() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Live prices");
  var range = sheet.getRange("P1");
  range.setValue(Math.random());
}

/**
* F1 = selector
* B2:B = matching range
* P column matching B will get a random number refresh
* @customfunction
*/

function refreshOne() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Live prices");
  var lookup = sheet.getRange("F1").getValue();
  var rangeNames = sheet.getRange("B2:B");
  // range.setValue(Math.random());
  var data = rangeNames.getValues();
  for(var i = 0; i<data.length;i++){
    if(data[i] == lookup){ //[1] because column B
      break;
    }
  }
  var range = sheet.getRange(2+i, 16, 1, 1);
  range.setValue(Math.random());
}

/**
* Crypto price w/ cache from CoinGecko
* @param CoinGecko API ID of the crypto
* @param a number that changes to refresh
* @customfunction
*/

function cryptoPrice(id, refreshID) {
  var cache = CacheService.getDocumentCache();
  // we attempt to refresh but because of stupid rate limiting handle errors gracefully
  var cacheID = "crypto-" + id;
  var cachedValue = cache.get(cacheID);

  try {
    var result = importJSON("https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=" + id,"0.current_price")
    cache.put(cacheID, result);
  } catch (e) {
    if (e.toString().includes("429")) {
      if (!cachedValue) { throw e; }
      return Number.parseFloat(cachedValue);
    }
    throw e;
  }
  
  return result;
}

/**
* Imports JSON data to your spreadsheet
* @param url URL of your JSON data as string
* @param xpath simplified xpath as string
* @customfunction
*/

function importJSON(url, xpath) {

  // /rates/EUR
  var res = UrlFetchApp.fetch(url);
  var content = res.getContentText();
  var json = JSON.parse(content);

  var patharray = xpath.split(".");
  //Logger.log(patharray);

  for (var i = 0; i < patharray.length; i++) {
    json = json[patharray[i]];
  }

  //Logger.log(typeof(json));

  if (typeof (json) === "undefined") {
    return "Node Not Available";
  } else if (typeof (json) === "object") {
    var tempArr = [];

    for (var obj in json) {
      tempArr.push([obj, json[obj]]);
    }
    return tempArr;
  } else if (typeof (json) !== "object") {
    return json;
  }
}