Topic: Импортируем цены с CMC в googlesheet (Read 617 times)

var scriptProperties = PropertiesService.getScriptProperties();

function JSONLOAD(url, query, options, apikey, nonce) {
  let params = {
    'headers': {
      'Accept': "application/json",
      'Accept-Charset': "utf-8",
      'Accept-Encoding': "identity",
      'Cache-Control': "no-cache,public,max-age=60,must-revalidate"
    },
    'followRedirects': false,
    'muteHttpExceptions': false
  };
  
  if (typeof apikey == "string") {
    params['headers'][apikey] = scriptProperties.getProperty(apikey);
    params['headers']['Cache-Control'] = "no-cache,private,max-age=60,must-revalidate";
  }
  
  let response = UrlFetchApp.fetch(url, params);
  let jsonObject = JSON.parse(response.getContentText());
  
  return ImportJSON.parse(jsonObject, query, options);
}

// REQUIRE: https://github.com/bradjasper/ImportJSON

/**
 * Imports a JSON feed and returns the results to be inserted
 * into a Google Spreadsheet. The JSON feed is flattened to
 * create a two-dimensional array. The first row contains the
 * headers, with each column header indicating the path to
 * that data in the JSON feed. The remaining rows contain the
 * data.
 *
 * @customfunction
 * @param {"https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest"} url
 *   A full REST API url with endpoint path and parameters.
 * @param {"/data"} query
 *   A comma-separated list of JSON paths. Any path starting
 *   with one of these paths gets imported.
 * @param {"noInherit,noTruncate,noHeaders"} options
 *   A comma-separated list of options that alter processing of
 *   the data: noInherit, noTruncate, rawHeaders, noHeaders,
 *   allHeaders, debugLocation.
 * @param {"X-CMC_PRO_API_KEY"} apikey
 *   An additional HTTP header by the script property key if
 *   needed (add appropriate key/value pair to the properties).
 * @return A two-dimensional array containing the data.
 **/
function JSONLOAD(url, query, options, apikey) {
  const timeout = 1000;
  var scriptLock = LockService.getScriptLock();
  
  while (! scriptLock.tryLock(timeout)) {
    Utilities.sleep(timeout / 10);
  }

  var params = {
    'headers': {
      'Accept': "application/json",
      'Accept-Charset': "utf-8",
      'Accept-Encoding': "identity",
      'Cache-Control': "public, max-age=60, must-revalidate"
    },
    'followRedirects': false,
    'muteHttpExceptions': false
  };
  
  if (apikey && isNaN(apikey) && apikey !== true) {
    var scriptProperties = PropertiesService.getScriptProperties();
    
    apikey = apikey.toString();
    params['headers'][apikey] = properties.getProperty(apikey);
  }
  
  var timer = new Date().getTime();
  var dataArray = ImportJSONAdvanced(url, params, query, options,
                                     includeXPath_, defaultTransform_);
  
  timer = new Date().getTime() - timer;
  Utilities.sleep(Math.max(0, timeout - timer));
  
  return dataArray;
}

=transpose(
    jsonload("/v1/cryptocurrency/quotes/latest?id="&join(",",A2:A16),
             join(",",arrayformula("/data/"&A2:A16&"/quote/USD/price")),
             "noInherit,noTruncate,noHeaders","X-CMC_PRO_API_KEY",A1))

=image("https://s2.coinmarketcap.com/static/img/coins/16x16/1027.png",3)

=image("https://s2.coinmarketcap.com/generated/sparklines/web/7d/usd/1027.png",2)

"$"#,##0.000,,"M"

--- ImportJSON-git852565b.gs	2019-05-08 17:28:29.737736133 +0300
+++ ImportJSON-git852565b-patched.gs	2019-05-08 17:31:03.965516139 +0300
@@ -377,7 +377,7 @@
         }
       }
     }
-  } else if (isObject_(value)) {
+  } else if (value != null && isObject_(value)) {
     for (key in value) {
       if (parseData_(headers, data, path + "/" + key, state, value[key], query, options, includeFunc)) {
         dataInserted = true; 

// REQUIRE: https://github.com/bradjasper/ImportJSON

/**
 * Imports a JSON feed and returns the results to be inserted
 * into a Google Spreadsheet. The JSON feed is flattened to
 * create a two-dimensional array. The first row contains the
 * headers, with each column header indicating the path to
 * that data in the JSON feed. The remaining rows contain the
 * data.
 *
 * @customfunction
 * @param {"https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest"} url
 *   A full REST API url with endpoint path and parameters.
 * @param {"/data"} query
 *   A comma-separated list of JSON paths. Any path starting
 *   with one of these paths gets imported.
 * @param {"noInherit,noTruncate,noHeaders"} options
 *   A comma-separated list of options that alter processing of
 *   the data: noInherit, noTruncate, rawHeaders, noHeaders,
 *   allHeaders, debugLocation.
 * @param {"X-CMC_PRO_API_KEY"} apikey
 *   An additional HTTP header by the script property key if
 *   needed (add appropriate key/value pair to the properties).
 * @return A two-dimensional array containing the data.
 **/
function JSONLOAD(url, query, options, apikey) {
  const timeout = 1000;
  var scriptLock = LockService.getScriptLock();
  
  while (! scriptLock.tryLock(timeout)) {
    Utilities.sleep(timeout / 10);
  }

  var params = {
    'headers': {
      'Accept': "application/json",
      'Accept-Charset': "utf-8",
      'Accept-Encoding': "identity",
      'Cache-Control': "public, max-age=60, must-revalidate"
    },
    'followRedirects': false,
    'muteHttpExceptions': false
  };
  
  if (apikey && isNaN(apikey) && apikey !== true) {
    var scriptProperties = PropertiesService.getScriptProperties();
    
    apikey = apikey.toString();
    params['headers'][apikey] = properties.getProperty(apikey);
  }
  
  var timer = new Date().getTime();
  var dataArray = ImportJSONAdvanced(url, params, query, options,
                                     includeXPath_, defaultTransform_);
  
  timer = new Date().getTime() - timer;
  Utilities.sleep(Math.max(0, timeout - timer));
  
  return dataArray;
}

=ImportJSON("https://api.coinmarketcap.com/v1/ticker/ethereum/"; "/name,/symbol,/price_usd,/percent_change_24h,/percent_change_7d"; "noHeaders")