Google Sheets脚本解析Kraken API JSON遇单元格超限问题求助

我来帮你分析问题并修正代码：

问题根源拆解 #

• 多余的内层循环：你代码里的for (var j = 0; j < data[i].length; j++)完全没必要——每条data[i]就是一条完整的OHLC记录，你只需要提取其中的时间、开盘、最高、最低、收盘价这几个字段，不需要遍历它的每个元素。这个内层循环会让每条OHLC数据被重复写入多次，直接导致写入的行数远超实际的720条。
• appendRow参数错误：appendRow需要接收一维数组（比如[日期, 开盘价, 最高价, 最低价, 收盘价]），但你传入的是包含单个对象的数组mdata（[{date: ..., open: ...}]），Sheets无法正确解析对象，而且这种写法会导致数据格式完全不符合蜡烛图的要求。
• 低效的逐行写入：即使没有内层循环，每次循环都调用appendRow也会触发大量API请求，不仅慢，还容易触发Sheets的限制。

修正后的完整代码 #

function myJSON(){
  const url = "https://api.kraken.com/0/public/OHLC?pair=XXBTZEUR&since=0&interval=1";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const coinsheet = ss.getSheetByName('Sheet1');
  
  // 清空原有数据（可选，避免重复写入）
  coinsheet.clearContents();
  
  // 获取并解析API数据
  const response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  const json = JSON.parse(response.getContentText());
  const rawOHLC = json.result.XXBTZEUR;
  
  // 准备要写入的数据：先写表头
  const outputData = [['日期', '开盘价', '最高价', '最低价', '收盘价']];
  
  // 遍历每条OHLC记录，整理成数组
  for (const item of rawOHLC) {
    // 转换时间戳为日期
    const utcSeconds = item[0];
    const date = new Date(0);
    date.setUTCSeconds(utcSeconds);
    
    // 提取并转换数值（确保是数字类型）
    const open = parseFloat(item[1]);
    const high = parseFloat(item[2]);
    const low = parseFloat(item[3]);
    const close = parseFloat(item[4]);
    
    // 将这条数据加入输出数组
    outputData.push([date, open, high, low, close]);
  }
  
  // 一次性写入所有数据（效率远高于逐行append）
  if (outputData.length > 1) {
    coinsheet.getRange(1, 1, outputData.length, outputData[0].length).setValues(outputData);
  }
  
  Logger.log(`成功写入 ${outputData.length - 1} 条OHLC数据`);
}

关键改进点说明 #

• 移除冗余循环：直接遍历每条OHLC记录，避免重复写入
• 正确格式化数据：把每条记录整理成一维数组，符合Sheets的写入要求，同时先添加表头，完美匹配蜡烛图需要的列结构
• 批量写入数据：用setValues一次性写入所有数据，大幅提升效率，也避免触发不必要的行数限制
• 可选清空原有数据：避免多次运行函数导致数据重复累积

运行这个修正后的代码后，你就能在Sheet1里得到规范的OHLC数据，直接选中这些数据就能创建蜡烛图了。

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