使用Google Apps Script发送Excel附件时出现#REF数据错误

问题情况 #

用Google Apps Script批量导出指定工作表为Excel附件发送邮件，之前正常运行，但近期导出的Excel里，那些用QUERY函数跨表引用数据的单元格全部显示#REF错误，而在Google Sheets里数据显示完全正常。

涉及的关键公式示例：

=IF(Sheet11!H1<0,"-VE Stock Qty", query('INV SHEET'!A3:E,"SELECT * WHERE A IS NOT NULL",0))

问题原因 #

直接导出单个工作表时，Excel无法识别Google Sheets特有的QUERY函数，而且导出的工作表不包含它依赖的其他数据源表，导致跨表引用失效，最终出现#REF错误。

解决方案 #

修改脚本逻辑：先将目标工作表的公式临时转换成值，导出完成后再恢复原公式，这样导出的Excel里就是实际计算后的数值，不会出现引用错误。

修改后的完整代码：

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 originalFormulas = {};

  // 第一步：将目标工作表的内容转为值
  sheets.forEach(sheetName => {
    var sheet = spreadSheet.getSheetByName(sheetName);
    var dataRange = sheet.getDataRange();
    // 保存原始公式
    originalFormulas[sheetName] = dataRange.getFormulas();
    // 将公式转为计算后的值
    dataRange.setValues(dataRange.getValues());
  });

  var urls = sheets.map(sheet => {
    var sheetId = spreadSheet.getSheetByName(sheet).getSheetId();
    return `https://docs.google.com/feeds/download/spreadsheets/Export?key=${spreadSheetId}&gid=${sheetId}&exportFormat=xlsx`;
  });

  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);
    return UrlFetchApp.fetch(url, params).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);

  // 第二步：恢复工作表的原始公式
  sheets.forEach(sheetName => {
    var sheet = spreadSheet.getSheetByName(sheetName);
    var dataRange = sheet.getDataRange();
    dataRange.setFormulas(originalFormulas[sheetName]);
  });
}

注意事项 #

• 操作过程中会临时修改工作表内容，但导出后会立即恢复，不会影响原表的数据和公式
• 如果工作表数据量很大，getDataRange()可以调整为具体的范围，提升运行效率
• 若仍出现流量问题，可适当延长Utilities.sleep()的时间（单位：毫秒）

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