如何修改Google Apps Script将大CSV拆分后写入指定现有表格

问题背景 #

长期使用Google Apps Script将CSV导入指定Google Sheets，近期遇到报错：Exception: File Sofas & Stuff.csv exceeds the maximum file size.。原脚本通过doPost实现外部调用，但无法处理大文件。找到@tanaike的拆分脚本，但该脚本会生成新的Google Sheets，重复运行产生冗余文件，需要修改为将拆分后的CSV数据写入指定现有Google Sheets并更新内容。

修改后的完整脚本 #

function splitCsvToExistingSheets(obj) {
  var accessToken = ScriptApp.getOAuthToken();
  var baseUrl = "https://www.googleapis.com/drive/v3/files/";

  // 获取CSV文件大小
  var url1 = baseUrl + obj.fileId + "?fields=size";
  var fileSize = Number(JSON.parse(UrlFetchApp.fetch(url1, {headers: {Authorization: "Bearer " + accessToken}}).getContentText()).size);

  // 计算需要拆分的份数
  if (obj.files == null) {
    obj.number = 1;
    obj.start = 0;
  }
  var start = obj.start;
  var end = start + obj.chunk;
  var useFileSize = fileSize - start;
  let f = Math.floor(useFileSize / obj.chunk);
  f = useFileSize % obj.chunk > 0 ? f + 1 : f;
  if (f < obj.files || obj.files == null) {
    obj.files = f;
  }

  // 拆分大CSV并写入指定现有表格
  var url2 = baseUrl + obj.fileId + "?alt=media";
  var targetSpreadsheet = SpreadsheetApp.openById(obj.targetSheetId); // 提前获取目标表格，避免重复调用
  var i;
  for (i = 0; i < obj.files; i++) {
    var params = {
      method: "get",
      headers: {
        Authorization: "Bearer " + accessToken,
        Range: "bytes=" + start + "-" + end,
      },
    };
    var res = UrlFetchApp.fetch(url2, params).getContentText();
    var e = res.lastIndexOf("\n");
    var csvChunk = res.substr(0, e);
    var csvData = Utilities.parseCsv(csvChunk); // 解析拆分后的CSV片段

    // 定位目标工作表并更新数据
    var sheetName = obj.sheetNamePrefix + (i + obj.number);
    var sheet = targetSpreadsheet.getSheetByName(sheetName);
    if (!sheet) throw new Error(`未找到工作表：${sheetName}，请确保目标表格中存在该工作表`);
    
    // 清空原有内容并写入新数据
    var lr = sheet.getLastRow();
    if (lr > 0) sheet.getRange('A1:Z' + lr).clearContent();
    sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

    // 更新拆分起始位置
    start += e + 1;
    end = start + obj.chunk;
  }

  // 返回剩余未拆分的起始位置（用于断点续传）
  if (start < fileSize) {
    return {nextStart: start, nextNumber: i + obj.number};
  } else {
    return null;
  }
}

// 主执行函数 - 配置参数后运行
function main() {
    var obj = {
        fileId: "#####", // 大CSV文件的Drive ID
        chunk: 10485760, // 拆分块大小（10MB，可根据需求调整）
        files: 3, // 需要拆分的份数，留空则自动计算
        start: 0, // 拆分起始字节位置，首次运行填0
        targetSheetId: "#####", // 目标Google Sheets的ID
        sheetNamePrefix: "Feed_", // 目标工作表名称前缀（如Feed_1、Feed_2）
        number: 1, // 工作表编号起始值
    };
    var nextStart = splitCsvToExistingSheets(obj);
    Logger.log(nextStart);
}

// 整合到doPost的版本（用于外部自动化调用）
function doPost(e) {
    var obj = {
        fileId: DriveApp.getFilesByName("Sofas & Stuff.csv").next().getId(), // 替换为你的CSV文件名
        chunk: 10485760,
        targetSheetId: "#####", // 目标表格ID
        sheetNamePrefix: "Feed_",
        number: 1,
    };
    splitCsvToExistingSheets(obj);
    UrlFetchApp.fetch('https://webhook.com/'); // 保留原有的回调请求
}

关键修改说明 #

• 替换新表格创建逻辑：移除原脚本中Drive.Files.insert创建新表格的代码，改为打开指定的现有Google Sheets，定位到对应工作表
• 优化表格获取性能：提前通过openById获取目标表格对象，避免循环中重复调用API
• 增加参数配置：在obj中新增targetSheetId（目标表格ID）和sheetNamePrefix（工作表名称前缀），方便指定写入位置
• 数据更新逻辑：清空目标工作表原有内容后，写入拆分后的CSV数据，确保内容是最新的
• 错误提示：如果找不到指定工作表，抛出明确错误提示

使用步骤 #

1. 确保目标Google Sheets中已创建好对应名称的工作表（如配置sheetNamePrefix: "Feed_"，则需要有Feed_1、Feed_2等工作表）
2. 在main或doPost函数中配置好所有参数：
   • fileId：大CSV文件的Drive ID（可通过文件URL获取）
   • targetSheetId：目标Google Sheets的ID（同样从URL获取）
   • chunk：根据文件大小调整拆分块大小，建议10-50MB
3. 启用Google Apps Script的Drive API：在脚本编辑器中点击「服务」→ 添加「Drive API」
4. 运行main测试，或部署为Web App使用doPost接收外部调用

内容的提问来源于stack exchange，提问作者beehive-digital