kballenegger · April 14, 2021 06:02 · Apr 14, 2021
diff --git a/Crypto.gs b/Crypto.gs
@@ -0,0 +1,100 @@
+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;
+  }
+}