我在其中一个有趣的项目的Google表格中导入JSON时遇到问题。

这些单元格给我：“一天中调用了太多次服务：urlfetch。（第127行）。”

从我看到的问题来看，我调用API的次数太多，但是我找不到将Apis调用限制为每天一次或通过Google表格中的明确请求的方法。要导入json，我使用的是我在网上找到的脚本（工具->脚本编辑器）

文件->电子表格设置->计算似乎无法解决我的问题

我正在使用的api是这样的： https://api.scryfall.com/cards/search?order=name&q=-is%3Afunny+%28rarity%3Ac+OR+rarity%3Au%29+color%3DW+cmc%3D1+type%3Acreature

apis文档是这样的： https://scryfall.com/docs/api

Google表格是这样的： https://docs.google.com/spreadsheets/d/1OVuFGJVpAxTO3LNo_fhAKtZ1BR4SE1yVpRbLCrhMVXo/edit?usp=sharing

这是ImportJson脚本：

function readRows() {
  var sheet = Spreadsheetapp.getactiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getvalues();

  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    Logger.log(row);
  }
};


function onOpen() {
  var sheet = Spreadsheetapp.getactiveSpreadsheet();
  var entries = [{
    name : "Read Data",functionName : "readRows"
  }];
  sheet.addMenu("Script Center Menu",entries);
};

function ImportJSON(url,query,options) {
  return ImportJSONAdvanced(url,options,includeXPath_,defaultTransform_);
}

function ImportJSONAdvanced(url,includeFunc,transformFunc) {
  var jsondata = UrlFetchApp.fetch(url);
  var object   = JSON.parse(jsondata.getcontentText());

  return parseJSONObject_(object,transformFunc);
}


function URLEncode(value) {
  return encodeURIComponent(value.toString());  
}

function parseJSONObject_(object,transformFunc) {
  var headers = new Array();
  var data    = new Array();

  if (query && !Array.isArray(query) && query.toString().indexOf(",") != -1) {
    query = query.toString().split(",");
  }

  if (options) {
    options = options.toString().split(",");
  }

  parseData_(headers,data,"",1,object,includeFunc);
  parseHeaders_(headers,data);
  transformData_(data,transformFunc);

  return hasOption_(options,"noHeaders") ? (data.length > 1 ? data.slice(1) : new Array()) : data;
}


function parseData_(headers,path,rowIndex,value,includeFunc) {
  var dataInserted = false;

  if (isObject_(value)) {
    for (key in value) {
      if (parseData_(headers,path + "/" + key,value[key],includeFunc)) {
        dataInserted = true; 
      }
    }
  } else if (Array.isArray(value) && isObjectArray_(value)) {
    for (var i = 0; i < value.length; i++) {
      if (parseData_(headers,value[i],includeFunc)) {
        dataInserted = true;
        rowIndex++;
      }
    }
  } else if (!includeFunc || includeFunc(query,options)) {
    // Handle arrays containing only scalar values
    if (Array.isArray(value)) {
      value = value.join(); 
    }

    // Insert new row if one doesn't already exist
    if (!data[rowIndex]) {
      data[rowIndex] = new Array();
    }

    // Add a new header if one doesn't exist
    if (!headers[path] && headers[path] != 0) {
      headers[path] = Object.keys(headers).length;
    }

    // Insert the data
    data[rowIndex][headers[path]] = value;
    dataInserted = true;
  }

  return dataInserted;
}

function parseHeaders_(headers,data) {
  data[0] = new Array();

  for (key in headers) {
    data[0][headers[key]] = key;
  }
}

function transformData_(data,transformFunc) {
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      transformFunc(data,i,j,options);
    }
  }
}

function isObject_(test) {
  return Object.prototype.toString.call(test) === '[object Object]';
}

function isObjectArray_(test) {
  for (var i = 0; i < test.length; i++) {
    if (isObject_(test[i])) {
      return true; 
    }
  }  

  return false;
}

function includeXPath_(query,options) {
  if (!query) {
    return true; 
  } else if (Array.isArray(query)) {
    for (var i = 0; i < query.length; i++) {
      if (applyXPathRule_(query[i],options)) {
        return true; 
      }
    }  
  } else {
    return applyXPathRule_(query,options);
  }

  return false; 
};

function applyXPathRule_(rule,options) {
  return path.indexOf(rule) == 0; 
}

function defaultTransform_(data,row,column,options) {
  if (!data[row][column]) {
    if (row < 2 || hasOption_(options,"noInherit")) {
      data[row][column] = "";
    } else {
      data[row][column] = data[row-1][column];
    }
  } 

  if (!hasOption_(options,"rawHeaders") && row == 0) {
    if (column == 0 && data[row].length > 1) {
      removeCommonPrefixes_(data,row);  
    }

    data[row][column] = toTitleCase_(data[row][column].toString().replace(/[\/\_]/g," "));
  }

  if (!hasOption_(options,"noTruncate") && data[row][column]) {
    data[row][column] = data[row][column].toString().substr(0,256);
  }

  if (hasOption_(options,"debugLocation")) {
    data[row][column] = "[" + row + "," + column + "]" + data[row][column];
  }
}

function removeCommonPrefixes_(data,row) {
  var matchIndex = data[row][0].length;

  for (var i = 1; i < data[row].length; i++) {
    matchIndex = findEqualityEndpoint_(data[row][i-1],data[row][i],matchIndex);

    if (matchIndex == 0) {
      return;
    }
  }

  for (var i = 0; i < data[row].length; i++) {
    data[row][i] = data[row][i].substring(matchIndex,data[row][i].length);
  }
}

function findEqualityEndpoint_(string1,string2,stopAt) {
  if (!string1 || !string2) {
    return -1; 
  }

  var maxEndpoint = Math.min(stopAt,string1.length,string2.length);

  for (var i = 0; i < maxEndpoint; i++) {
    if (string1.charAt(i) != string2.charAt(i)) {
      return i;
    }
  }

  return maxEndpoint;
}


function toTitleCase_(text) {
  if (text == null) {
    return null;
  }

  return text.replace(/\w\S*/g,function(word) { return word.charAt(0).toUpperCase() + word.substr(1).toLowerCase(); });
}

function hasOption_(options,option) {
  return options && options.indexOf(option) >= 0;
}