时间戳

首页 > 解决方案 > Coinmarketcap 在拉动价格时给出 #N/A 和 Loading.. 错误

javascript - Coinmarketcap 在拉动价格时给出 #N/A 和 Loading.. 错误

问题描述

直到大约一周前，我的 Google 表格代码还在毫无问题地拉动 coinmarketcap 价格。然后随机停止并显示错误；#N/A 或加载中..

将鼠标悬停在错误单元格上时，它会显示“错误，导入的内容为空”。

我的研究使我了解到，现在有新的 coinmarketcap pro API 取代了旧的。很好奇这是否是导致问题的原因？

但我还不确定是否是这样。请帮忙。

拉动价格的公式是（示例）： =importxml("https://coinmarketcap.com/currencies/bitcoin/", "//*[@id=" & CHAR(34) & "quote_price" & CHAR(34) &"]/span[1]")

然后我附上了一个相关的脚本，如下所示。 

  function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().getRangeByName("TodayRng").setValue(new Date())
}

function getPriceStatic(coinName, currency) 
{
  try {

    var request_url = "https://api.coinmarketcap.com/v1/ticker/" + coinName + "?convert=" + currency
    /*var request_url = "https://pro-api.coinmarketcap.com/v1/ticker/" + coinName + "?convert=" + currency*/

    var response = UrlFetchApp.fetch(request_url);

    var json = response.getContentText();

    var data = JSON.parse(json);

    var priceval = {"EUR" : data[0].price_eur, "USD" : data[0].price_usd, 
                    "BTC" : data[0].price_btc, "AUD" : data[0].price_aud, 
                    "BRL" : data[0].price_brl, "CAD" : data[0].price_cad, 
                    "CHF" : data[0].price_chf, "CNY" : data[0].price_cny, 
                    "RUB" : data[0].price_rub, "GBP" : data[0].price_gbp, 
                    "HKD" : data[0].price_hkd, "IDR" : data[0].price_idr, 
                    "INR" : data[0].price_inr, "JPY" : data[0].price_jpy, 
                    "KRW" : data[0].price_krw, "MXN" : data[0].price_mxn, 

                    "24h_volume_usd" : data[0]["24h_volume_usd"], 
                    "market_cap_usd" : data[0].market_cap_usd, 
                    "available_supply" : data[0].available_supply, 
                    "total_supply" : data[0].total_supply,
                    "max_supply" : data[0].max_supply,/*added by me*/
                    "percent_change_1h" : data[0].percent_change_1h, 
                    "percent_change_24h" : data[0].percent_change_24h, 
                    "percent_change_7d" : data[0].percent_change_7d, 
                    "last_updated" : data[0].last_updated }

    var price = parseFloat(priceval[currency])

    return price
  }
  catch(err) {
      getPriceStatic(coinName, currency)
  }
}

function getPriceUpdatable(coinName, currency, timestamp) 
{
  try {
    var request_url = "https://api.coinmarketcap.com/v1/ticker/" + coinName + "?convert=" + currency
    /*var request_url = "https://pro-api.coinmarketcap.com/v1/ticker/" + coinName + "?convert=" + currency*/

    var response = UrlFetchApp.fetch(request_url);

    var json = response.getContentText();

    var data = JSON.parse(json);

    var priceval = {"EUR" : data[0].price_eur, "USD" : data[0].price_usd, 
                    "BTC" : data[0].price_btc, "AUD" : data[0].price_aud, 
                    "BRL" : data[0].price_brl, "CAD" : data[0].price_cad, 
                    "CHF" : data[0].price_chf, "CNY" : data[0].price_cny, 
                    "RUB" : data[0].price_rub, "GBP" : data[0].price_gbp, 
                    "HKD" : data[0].price_hkd, "IDR" : data[0].price_idr, 
                    "INR" : data[0].price_inr, "JPY" : data[0].price_jpy, 
                    "KRW" : data[0].price_krw, "MXN" : data[0].price_mxn, 

                    "24h_volume_usd" : data[0]["24h_volume_usd"], 
                    "market_cap_usd" : data[0].market_cap_usd, 
                    "available_supply" : data[0].available_supply, 
                    "total_supply" : data[0].total_supply,
                    "max_supply" : data[0].max_supply,/*added by me*/
                    "percent_change_1h" : data[0].percent_change_1h, 
                    "percent_change_24h" : data[0].percent_change_24h, 
                    "percent_change_7d" : data[0].percent_change_7d, 
                    "last_updated" : data[0].last_updated }

    var price = parseFloat(priceval[currency])

    return price
  }
  catch(err) {
      getPriceStatic(coinName, currency, timestamp)
  }
}

function test_onEdit() {
  onEdit({
    range : SpreadsheetApp.getActiveSpreadsheet().getActiveCell(),
});
}

function onEdit(e)
{
  e.range = SpreadsheetApp.getActiveSpreadsheet().getActiveCell();

  if (e.range.getSheet().getName() != "Transactions") return;

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ws = ss.getSheetByName("Transactions") 

  var currency = ss.getRangeByName("Currency").getValue();

  var row = e.range.getRow();

  var feeCurrColumn = ss.getRangeByName("FeeCurrencyColumn").getColumn();
  var acqCurrColumn = ss.getRangeByName("AcquiredCurrencyColumn").getColumn();

  var bookPriceEurCol = ss.getRangeByName("BookPriceEURCol").getColumn();
  var bookPriceBtcCol = ss.getRangeByName("BookPriceBTCCol").getColumn();
  var feeBookPriceEurCol = ss.getRangeByName("FeeBookPriceEURCol").getColumn();
  var feeBookPriceBtcCol = ss.getRangeByName("FeeBookPriceBTCCol").getColumn();

  var acqCurrNameCol = ss.getRangeByName("AcquiredCurrencyNameColumn").getColumn();
  var feeCurrNameCol = ss.getRangeByName("FeeCurrencyNameColumn").getColumn();

  var acqIsCryptoCol = ss.getRangeByName("AcquiredIsCryptoCol").getColumn();
  var feeIsCryptoCol = ss.getRangeByName("FeeIsCryptoCol").getColumn();

  var acqAmountCol = ss.getRangeByName("AcquiredAmountCol").getColumn();
  var feeAmountCol = ss.getRangeByName("FeeAmountCol").getColumn();

  var transDateColumn = ss.getRangeByName("TransactionDateColumn").getColumn();

  var a = e.range.getColumn();        

  if (e.range.getColumn() == feeAmountCol || e.range.getColumn() == acqAmountCol || e.range.getColumn() == feeCurrColumn || e.range.getColumn() == acqCurrColumn)
  {

    if (e.range.getColumn() == acqAmountCol || e.range.getColumn() == acqCurrColumn) {
      if (e.range.getValue().length == 0) {
        ws.getRange(row, bookPriceEurCol).setValue("");
        ws.getRange(row, bookPriceBtcCol).setValue("");
        ws.getRange(row, transDateColumn).setValue("");
      } else {
        updateBookPrice(ws , row, bookPriceEurCol, bookPriceBtcCol, currency, acqIsCryptoCol, acqCurrNameCol) 
        updateFeeBookPrice(ws , row, feeBookPriceEurCol, feeBookPriceBtcCol, currency, feeIsCryptoCol, feeCurrNameCol, feeAmountCol, acqAmountCol)
        ws.getRange(row, transDateColumn).setValue(new Date());
      }
    }

    if (e.range.getColumn() == feeAmountCol || e.range.getColumn() == feeCurrColumn ) {
      if (e.range.getValue().length == 0) {
        ws.getRange(row, feeBookPriceEurCol).setValue("");
        ws.getRange(row, feeBookPriceBtcCol).setValue("");
      } else {
        updateFeeBookPrice(ws , row, feeBookPriceEurCol, feeBookPriceBtcCol, currency, feeIsCryptoCol, feeCurrNameCol, feeAmountCol, acqAmountCol)
        ws.getRange(row, transDateColumn).setValue(new Date());
      }
    }    
  }
  Logger.log("exit")
}

function updateBookPrice(ss, row, bookPriceEurCol, bookPriceBtcCol, currency, acqIsCryptoCol, acqCurrNameCol) 
{
  if (ss.getRange(row, acqIsCryptoCol).getValue() == true) {
    var coinName = ss.getRange(row, acqCurrNameCol).getValue();
    var val = getPriceStatic(coinName, currency);
    ss.getRange(row, bookPriceEurCol).setValue(val);

    val = getPriceStatic(coinName, "BTC");
    ss.getRange(row, bookPriceBtcCol).setValue(val);

  } else {
    ss.getRange(row, bookPriceEurCol).setValue(0);
    ss.getRange(row, bookPriceBtcCol).setValue(0);
  }  
}

function updateFeeBookPrice(ss, row, feeBookPriceEurCol, feeBookPriceBtcCol, currency, feeIsCryptoCol, feeCurrNameCol, feeAmountCol, acqAmountCol)
{
  var acqAmount = ss.getRange(row, acqAmountCol).getValue();
  var feeAmount = ss.getRange(row, feeAmountCol).getValue();

  var finalEurVal = 0;

  if (ss.getRange(row, feeIsCryptoCol).getValue() == true) {
    var coinName = ss.getRange(row, feeCurrNameCol).getValue();

    finalEurVal = feeAmount * getPriceStatic(coinName, currency) / acqAmount;
    ss.getRange(row, feeBookPriceEurCol).setValue(finalEurVal);

  } else {
    finalEurVal = feeAmount/acqAmount;
    ss.getRange(row, feeBookPriceEurCol).setValue(finalEurVal);
  }  

  ss.getRange(row, feeBookPriceBtcCol).setValue(finalEurVal/getPriceStatic("bitcoin",currency));
}

标签： javascriptjqueryjsonapigoogle-apps-script

解决方案

价格对我来说这很有效，但昨天停止了，现在又开始了

=importxml("https://coinmarketcap.com/currencies/ethereum/", "//span[@class='cmc-details-panel-price__price']")

推荐阅读