parsejson.js

parsejson.js

/**
 * Get field on JSON API
 *
 * @param {string} url API Url
 * @param {string} ...field Field names, allow nested
 * @return field
 * @customfunction
 */
function ParseJSONField(url, ...field) {
  var json = _getUrlData(url);

  // allow nested
  var fieldData = '';
  for (let i = 0; i < field.length; i++) {
    fieldData = json[field];
    json = fieldData; // for next nest
  }
  return fieldData;
  
}

function _getUrlData(url) {
  var json = _getCached(url);
  if (json) {
    return JSON.parse(json);
  }
  json = UrlFetchApp.fetch(url).getContentText();


  _addToCache(url, json); // cache

  return JSON.parse(json);
}

function _getCached(url) {
   var md5Url = _toMd5String(url);
   var cache = CacheService.getScriptCache();
   return cache.get(md5Url);
}

/**
 * The maximum amount of data that can be stored per key is 100KB
 * The cap for cached items is 1,000. If more than 1,000 items are written, the cache stores the 900 items farthest from expiration. This limit might change.
 */
function _addToCache(url, textData){
  var cache = CacheService.getScriptCache();
  var cached_list = cache.get("iexapi-cache-list");
  if (cached_list == null) {
    cached_list = '[]';
  }

  cached_list = JSON.parse(cached_list);
  var md5Url = _toMd5String(url);
  cached_list.push(md5Url);

  cache.put('iexapi-cache-list', JSON.stringify(cached_list), _getCacheExpiration());

  cache.put(md5Url, textData, _getCacheExpiration());
}

function _removeAllCache() {
  var cache = CacheService.getScriptCache();
  var cached_list = cache.get("iexapi-cache-list");
  if (cached_list != null) {
    cached_list = JSON.parse(cached_list);
    cache.removeAll(cached_list);
  }

  // no cache
  return true;
}


function _toMd5String(input){
  var digest = Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, input);
  var __ ='';
  for (i = 0; i < digest.length; i++) {
    var byte = digest[i];
    if (byte < 0) byte += 256;
    var bStr = byte.toString(16);
    if (bStr.length == 1) bStr = '0' + bStr;
    __ += bStr;
  }

  return __;
}

function _getCacheExpiration(){
  return 15; // 15 seconds
}


function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('IEX Menu')
      .addItem('Clear Cache', 'clear_cache_menu')
      .addToUi();
}

function clear_cache_menu() {
  _removeAllCache();
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
     .alert('All cache cleared, reload the sheet page now');
}