修复 Google 表格中的 #REF 错误

了解 Google 表格附件问题

使用 Google 表格时，一项常见任务是通过电子邮件以 Excel 附件形式发送工作表数据。可以使用 Google Apps 脚本简化此过程，允许用户在一封电子邮件中发送多张工作表。但是，可能会出现问题，例如 #REF 错误，这通常表示导出的数据中存在引用问题。

当 Google 表格使用 QUERY() 等复杂公式时，通常会出现此问题，当工作表转换为 Excel 格式时，这些公式不能很好地转换。该错误破坏了附件中的数据完整性，给依赖这些导出进行报告或分析的用户带来了重大挑战。

命令	描述
SpreadsheetApp.getActiveSpreadsheet()	获取脚本绑定到的活动电子表格。
spreadSheet.getSheetByName(sheet).getSheetId()	返回电子表格中工作表的唯一标识符。
UrlFetchApp.fetch(url, params)	使用各种参数向指定 URL 发出请求来管理 HTTP 请求。
Utilities.sleep(milliseconds)	将脚本的执行暂停指定的毫秒数，以防止达到 API 速率限制。
ScriptApp.getOAuthToken()	检索当前用户的 OAuth 2.0 令牌以对请求进行身份验证。
getBlob()	获取从 URL 获取的文件数据（以 blob 形式），用于将文件附加到电子邮件。

脚本功能说明

提供的脚本旨在自动执行在一封电子邮件中以 Excel 附件形式发送多个 Google 表格的过程。它首先声明用于导出的工作表名称数组。该脚本检索活动电子表格并迭代工作表名称数组以生成每个工作表的下载 URL。这些 URL 经过专门格式化，可将工作表导出为 Excel 文件。使用“Utilities.sleep(10000);”在这里，在获取请求之间引入延迟至关重要，这有助于管理 Google 服务器上的负载并防止脚本达到速率限制。

每个 URL 都会以 blob 的形式获取相应的工作表，然后根据预定义的文件名数组对其进行命名。此步骤至关重要，因为它将工作表中的数据转换为适合电子邮件附件的格式。准备好所有文件 blob 后，该脚本将构造一个包含指定收件人、主题行和正文的电子邮件对象。 Blob 附加到此电子邮件中，然后使用“MailApp.sendEmail(message);”发送该电子邮件命令。此函数是 Google Apps Script 的 MailApp 服务的一部分，允许脚本发送电子邮件、邀请和通知。

function sendExcelAttachmentsInOneEmail() {
  var sheets = ['OH INV - B2B', 'OH INV - Acc', 'OH INV - B2C', 'B2B', 'ACC', 'B2C'];
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadSheetId = spreadSheet.getId();
  var urls = sheets.map(sheet => {
    var sheetId = spreadSheet.getSheetByName(sheet).getSheetId();
    return \`https://docs.google.com/spreadsheets/d/${spreadSheetId}/export?format=xlsx&gid=${sheetId}\`;
  });
  var reportName = spreadSheet.getSheetByName('IMEIS').getRange(1, 14).getValue();
  var params = {
    method: 'GET',
    headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()},
    muteHttpExceptions: true
  };
  var fileNames = ['OH INV - B2B.xlsx', 'OH INV - Acc.xlsx', 'OH INV - B2C.xlsx', 'B2B.xlsx', 'ACC.xlsx', 'B2C.xlsx'];
  var blobs = urls.map((url, index) => {
    Utilities.sleep(10000);  // Delay added to avoid hitting rate limits
    var response = UrlFetchApp.fetch(url, params);
    return response.getBlob().setName(fileNames[index]);
  });
  var message = {
    to: 'email@domain.com',
    cc: 'email@domain.com',
    subject: 'Combined REPORTS - ' + reportName,
    body: "Hi Team,\n\nPlease find attached Reports.\n\nBest Regards!",
    attachments: blobs
  }
  MailApp.sendEmail(message);
}

对 Google 表格导出问题的高级见解

使用脚本将数据从 Google Sheets 导出为 Excel 格式可能会暴露数据管理中潜在的复杂性，尤其是在使用 QUERY() 等高级函数时。此类导出中遇到的 #REF 错误通常表示 Excel 环境中存在未解析的引用，而 Google 表格本身不会出现这种情况。出现这种差异的原因通常是 Google 表格中的某些功能（例如某些 QUERY() 操作或自定义脚本）在 Excel 中不受支持或表现不同。

此问题强调了确保 Google Sheets 公式与 Excel 处理公式和数据查询之间兼容性的重要性。当从 Google 环境迁移到 Microsoft 环境时，开发人员通常必须实施额外的检查或替代方法，以确保数据完整性，特别是在自动化流程（例如电子表格数据的电子邮件附件）时。

1. 从 Google 表格导出到 Excel 时，为什么会出现 #REF 错误？
2. #REF 错误通常是因为 Google 表格中的某些引用或公式无法识别或与 Excel 的公式环境不兼容而发生。
3. 如何防止达到 Google Apps 脚本的速率限制？
4. 使用 Utilities.sleep(milliseconds) 在脚本中实现暂停可以帮助管理请求频率并避免超出 Google 的速率限制。
5. muteHttpExceptions 在 URL 获取调用中做什么？
6. 如果 HTTP 请求失败，它允许脚本继续执行而不引发异常，这对于优雅地管理错误很有用。
7. 导出Excel时可以自定义每张工作表的文件名吗？
8. 是的，您可以为从工作表转换的每个 blob 设置自定义名称，然后再将其附加到电子邮件，如脚本中所示。
9. 有没有办法直接将 Google Sheets 导出到 Excel，无需中间脚本？
10. 是的，您可以直接从 Google 表格中的“文件”菜单手动下载 Excel 格式的 Google 表格，但自动执行此操作需要编写脚本。

通过这种探索，我们发现，虽然 Google Apps 脚本提供了强大的工具来自动化和增强 Google Sheets 功能，但在与 Excel 等不同平台交互时，会出现一定的复杂性。 #REF 错误是一个常见的陷阱，尤其是在处理无法在 Google 生态系统之外很好地转换的复杂查询和数据引用时。了解这些限制并在脚本中对其进行规划可以显着减少此类问题的发生，从而使数据管理流程更加顺畅。