Google Apps Script导出单Sheet为xlsx出现引用错误如何解决

核心逻辑 #

导出单个工作表前，先将工作表内所有公式替换为计算完成的静态值，再生成导出文件，即可解决跨表引用导致的公式报错问题。为了不修改原表数据，我们会先创建目标工作表的临时副本，对临时副本做值转换后再导出，导出完成后自动删除临时副本。

修改后的完整代码 #

function emailWithConvertedFiles() {
  // 基础定义
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const ssId = ss.getId();
  const params = { 
    method: "get", 
    headers: { "Authorization": "Bearer " + ScriptApp.getOAuthToken() }, 
    muteHttpExceptions: true 
  };

  // 获取导出文件格式
  const fileExtention = sheet.getRange(2,7).getValue();

  // 需要导出的工作表ID列表
  const sheetIdArray = ["1152040775", "1154468777", "54839846"];
  const blobArray = [];

  for (let i = 0; i < sheetIdArray.length; i++) {
    // 1. 获取目标原工作表
    const originalSheet = ss.getSheetById(parseInt(sheetIdArray[i]));
    // 2. 复制原工作表为临时工作表，放在所有表最后
    const tempSheet = originalSheet.copyTo(ss).setName(`temp_export_${sheetIdArray[i]}`);
    // 3. 获取临时工作表所有数据范围
    const dataRange = tempSheet.getDataRange();
    // 4. 把范围里的公式全部替换为计算后的静态值，保留原有格式
    dataRange.copyTo(dataRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES_AND_FORMATTING, false);
    // 5. 刷新表格变更，确保导出时数据已经更新
    SpreadsheetApp.flush();
    // 6. 生成临时工作表的导出链接
    const url =
      "https://docs.google.com/feeds/download/spreadsheets/Export?key="
      + ssId
      + "&exportFormat="
      + fileExtention
      + "&gid="
      + tempSheet.getSheetId();
    // 7. 生成blob存入数组
    blobArray.push(UrlFetchApp.fetch(url, params).getBlob().setName(`${originalSheet.getName()}.${fileExtention}`));
    // 8. 删除临时工作表，避免污染原表格
    ss.deleteSheet(tempSheet);
    console.log(blobArray[i].getName() + " 已添加到附件列表");
  }

  // 发送邮件
  const recipient = sheet.getRange(2, 1).getValue();
  const subject = sheet.getRange(2, 2).getValue();
  const body = sheet.getRange(2, 4).getValue(); 
  MailApp.sendEmail(recipient, subject, body, { attachments: blobArray });
  console.log("带附件邮件已发送成功");
}

注意事项 #

• 代码不会修改你原工作表的任何内容，所有转换操作都在临时副本上执行，执行完成后临时副本会自动删除
• 如果你不需要保留原单元格格式，可以把SpreadsheetApp.CopyPasteType.PASTE_VALUES_AND_FORMATTING替换为SpreadsheetApp.CopyPasteType.PASTE_VALUES，仅转换数值
• 遇到超大工作表时，可根据实际需求调整数据范围，不需要全表转换可以指定具体的转换区间

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