如何在Google表格中通过Google Script优化CoinMarketCap API请求

我来帮你解决这个API请求超限和代码冗余的问题！核心思路就是对每个币种只发起一次API调用，然后把返回的所有数据分发给需要的单元格，下面给你两种实用的方案，看你更倾向哪种：

方案一：绑定刷新按钮，一次性更新整行数据（推荐） #

这个方案最适配你的场景：点击刷新按钮后，脚本会遍历表格里的所有币种，每个币种仅调用一次API，然后直接把价格、市值、供应量等数据填充到对应行的单元格里，完全避免重复请求。

步骤1：编写核心函数 #

// 获取单个币种的完整数据（单次API调用）
function getCoinFullData(coinID) {
  const apiUrl = `https://api.coinmarketcap.com/v1/ticker/${coinID}/`;
  try {
    const response = UrlFetchApp.fetch(apiUrl);
    const data = JSON.parse(response.getContentText());
    if (!data || data.length === 0) return null;
    
    // 返回需要的所有字段，按需添加更多属性
    return {
      price: Number(data[0]['price_usd']),
      marketCap: Number(data[0]['market_cap_usd']),
      totalSupply: Number(data[0]['total_supply']),
      // 可扩展：比如添加24小时涨幅、流通供应量等
    };
  } catch (error) {
    console.error(`获取${coinID}数据失败:`, error);
    return null;
  }
}

// 批量刷新所有币种数据的主函数
function refreshAllCoinData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // 假设Ticker在A列，数据从第2行开始（第1行是表头）
  const tickerRange = sheet.getRange("A2:A").getValues();
  
  // 遍历每个Ticker，遇到空行停止
  for (let i = 0; i < tickerRange.length; i++) {
    const coinID = tickerRange[i][0];
    if (!coinID) break;
    
    const coinData = getCoinFullData(coinID);
    if (!coinData) continue;
    
    // 把数据写入对应行的单元格：C列=价格，D列=市值，E列=供应量
    const row = i + 2; // 对应表格的行号（从第2行开始）
    sheet.getRange(row, 3).setValue(coinData.price);
    sheet.getRange(row, 4).setValue(coinData.marketCap);
    sheet.getRange(row, 5).setValue(coinData.totalSupply);
    // 如需写入其他字段，继续添加对应的列索引即可
  }
}

步骤2：绑定刷新按钮 #

1. 在Google Sheets菜单栏点击「插入」→「绘图」，绘制一个带“刷新”文字的按钮形状
2. 点击刚插入的绘图，右上角选择「三个点」→「分配脚本」
3. 输入refreshAllCoinData并确定

现在点击这个按钮，就会批量更新所有币种的数据，每个币种仅发起一次API请求！

方案二：带缓存的自定义函数（保留单元格函数调用） #

如果你仍然希望在单元格里用类似=getMarketCap("BTC")的方式调用，可以通过缓存API返回的数据，避免同一币种多次发起请求。这里用Google Apps Script的CacheService实现短期缓存（比如缓存5分钟，减少重复请求）。

// 带缓存的通用函数，统一处理API请求与数据返回
function getCachedCoinData(coinID, field) {
  const cache = CacheService.getScriptCache();
  const cacheKey = `coin_${coinID}`;
  let coinData = cache.get(cacheKey);
  
  // 缓存无数据时，调用API获取并缓存
  if (!coinData) {
    const apiUrl = `https://api.coinmarketcap.com/v1/ticker/${coinID}/`;
    try {
      const response = UrlFetchApp.fetch(apiUrl);
      coinData = response.getContentText();
      // 缓存5分钟（300秒），可根据需求调整时长
      cache.put(cacheKey, coinData, 300);
    } catch (error) {
      console.error(`获取${coinID}数据失败:`, error);
      return "请求失败";
    }
  }
  
  // 解析数据并返回指定字段
  const data = JSON.parse(coinData);
  if (!data || data.length === 0) return "无数据";
  
  switch(field) {
    case "price": return Number(data[0]['price_usd']);
    case "marketCap": return Number(data[0]['market_cap_usd']);
    case "totalSupply": return Number(data[0]['total_supply']);
    // 可扩展：添加其他需要的字段分支
    default: return "无效字段";
  }
}

// 单元格直接调用的简化函数
function getPrice(coinID) {
  return getCachedCoinData(coinID, "price");
}

function getMarketCap(coinID) {
  return getCachedCoinData(coinID, "marketCap");
}

function getTotalSupply(coinID) {
  return getCachedCoinData(coinID, "totalSupply");
}

使用方式 #

在单元格里直接调用：

• 价格单元格：=getPrice(A2)（A2是对应Ticker的单元格）
• 市值单元格：=getMarketCap(A2)
• 供应量单元格：=getTotalSupply(A2)

这样同一币种的多个单元格会共享一次API请求的缓存数据，大幅减少请求次数，避免触发限额。

额外提示 #

• CoinMarketCap的v1 API是旧版本，若能升级到v2/v3 API，建议使用新版本（核心逻辑和上述方案一致）
• 缓存时长可根据数据实时性需求调整，比如非高频场景可以设置10-15分钟
• 脚本中已加入异常捕获，避免单个币种请求失败导致整个刷新流程中断

内容的提问来源于stack exchange，提问作者d3wannabe