如何用Google Sheets Script写入XLSX文件并保留格式？

要直接修改现有XLSX文件并保留格式，Google Apps Script本身没有原生支持操作XLSX二进制格式的API，需要借助第三方库**SheetJS（xlsx）**来实现。以下是具体步骤：

步骤1：导入SheetJS库到你的脚本项目 #

在Google Apps Script编辑器中：

• 点击菜单栏的「扩展」→「Apps Script」（已在编辑器中可跳过）
• 点击左侧「库」图标（📚）
• 在「添加库」输入框中填入SheetJS的项目ID：1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0，点击「查找」
• 选择最新版本号，设置标识符为XLSX，点击「添加」

步骤2：编写修改XLSX文件的代码 #

以下是适配你需求的完整代码，功能是将Google Sheets中选中行的数据写入指定的现有XLSX文件，保留原XLSX的格式：

function writeToExistingXLSX() {
  // 1. 获取Google Sheets中选中的目标数据
  const activeSheet = SpreadsheetApp.getActiveSheet();
  const activeRange = activeSheet.getActiveRange();
  const startRow = activeRange.getRow();
  const rowCount = activeRange.getNumRows();
  const endRow = startRow + rowCount - 1;

  // 准备表头和数据行
  const headers = ["TL", "Location", "Shape", "Color", "Program", "Notes"];
  const data = [headers];

  for (let i = startRow; i <= endRow; i++) {
    if (!activeSheet.isRowHiddenByFilter(i)) {
      const tl = activeSheet.getRange(`T${i}`).getValue();
      const loc = activeSheet.getRange(`U${i}`).getValue();
      let shape = activeSheet.getRange(`AN${i}`).getValue().substring(0, 1);
      if (shape === ",") shape = '","';
      const color = activeSheet.getRange(`AP${i}`).getValue();
      const program = activeSheet.getRange(`AM${i}`).getValue();
      const notes = activeSheet.getRange(`E${i}`).getValue();
      
      data.push([tl, loc, shape, color, program, notes]);
    }
  }

  // 2. 读取现有XLSX文件
  const targetXlsxFileId = "1--S0-GLPwVqAj94vbPFxfzGrIT-WOGwz"; // 替换为你的XLSX文件ID
  const xlsxFile = DriveApp.getFileById(targetXlsxFileId);
  const xlsxBlob = xlsxFile.getBlob();
  const workbook = XLSX.read(xlsxBlob.getBytes(), { type: "array" });

  // 3. 修改XLSX中的工作表（这里取第一个工作表，可替换为目标表名）
  const sheetName = workbook.SheetNames[0];
  const worksheet = workbook.Sheets[sheetName];

  // 将数据写入工作表（从A1单元格开始覆盖，保留原有格式）
  XLSX.utils.sheet_add_aoa(worksheet, data, { origin: "A1", skipHeader: false });

  // 4. 生成新的XLSX Blob并覆盖原文件
  const updatedXlsxBytes = XLSX.write(workbook, { bookType: "xlsx", type: "array" });
  const updatedBlob = Utilities.newBlob(updatedXlsxBytes, xlsxBlob.getContentType(), xlsxFile.getName());
  xlsxFile.setContent(updatedBlob.getBytes());
}

关键说明 #

• SheetJS库作用：解析XLSX二进制文件为可操作对象，修改后重新生成符合标准的XLSX格式，确保Excel等应用正常读取且保留原有格式。
• 覆盖原文件：通过DriveApp.File.setContent()方法将修改后的二进制内容写入原文件，实现直接覆盖。
• 写入位置调整：sheet_add_aoa方法的origin参数可指定写入起始单元格（如"A2"表示从第二行开始追加），按需调整即可。

原有方法问题分析 #

1. setContent写入纯文本：写入的是CSV格式纯文本，但XLSX是二进制压缩格式，直接替换会破坏文件结构，导致Excel无法识别。
2. SpreadsheetApp操作XLSX：SpreadsheetApp仅支持操作Google Sheets格式（.gsheet），无法直接读取本地XLSX文件，因此抛出类型错误。
3. 导出生成新文件：该方法仅能生成XLSX副本，无法直接覆盖原文件，若要替换需额外删除+重命名步骤，还会丢失文件历史记录与权限设置，效率低于直接修改。

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