如何将独立Google Sheets文件导出为仅含值和格式的Excel？

问题背景 #

• 已基于Apps Script开发函数，用于将指定Google Sheets导出为Excel并保存到指定Drive文件夹，同时添加了导出确认弹窗。
• 当前问题：导出的Excel文件保留了原表格的公式，不符合仅导出值与格式的需求。

当前使用代码：

function makeCopy() {

 var ui = SpreadsheetApp.getUi();

var response=ui.alert("Export to Excel", "Export to Excel? File will be saved to specified Google Drive folder.", ui.ButtonSet.YES_NO);

//checking the user response

            if(response==ui.Button.NO)
            {
            return; //exit from this function
            }

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  var name = sheet.getRange('Reference!P2').getDisplayValue();
  var destination = DriveApp.getFolderById("1HRchNqQ5_0LYzfULw1hWN_ALNuijsy2q"); //1HRchNqQ5_0LYzfULw1hWN_ALNuijsy2q");

  // Added
  var sheetId = "1ztpubW5-h9myOpC9GGMfxzGoQYtM46czOqiE8tidCl8";
  var url = "https://docs.google.com/spreadsheets/d/" + sheetId + "/export?format=xlsx&access_token=" + ScriptApp.getOAuthToken();
  var blob = UrlFetchApp.fetch(url).getBlob().setName(name + ".xlsx") ; // Modified
  destination.createFile(blob);

      ss.toast("Excel file has been added to Google Drive folder!")
}

导出的Excel文件示例（包含公式）：

解决方案 #

直接导出原表格会保留公式，需先创建临时副本，将所有单元格公式替换为值后再导出，最后清理临时文件。修改后的代码如下：

function makeCopy() {
  var ui = SpreadsheetApp.getUi();
  var response = ui.alert("Export to Excel", "Export to Excel? File will be saved to specified Google Drive folder.", ui.ButtonSet.YES_NO);
  
  if(response === ui.Button.NO) {
    return;
  }

  const originalSsId = "1ztpubW5-h9myOpC9GGMfxzGoQYtM46czOqiE8tidCl8"; // 目标表格ID
  const originalSs = SpreadsheetApp.openById(originalSsId);
  var name = originalSs.getRange('Reference!P2').getDisplayValue();
  var destination = DriveApp.getFolderById("1HRchNqQ5_0LYzfULw1hWN_ALNuijsy2q");

  // 创建临时副本
  var tempSs = originalSs.copy("Temp_Export_" + name);
  var tempSsId = tempSs.getId();

  // 将所有工作表的单元格替换为值（保留格式）
  var sheets = tempSs.getSheets();
  sheets.forEach(sheet => {
    var range = sheet.getDataRange();
    range.copyTo(range, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  });

  // 导出临时副本为Excel
  var url = "https://docs.google.com/spreadsheets/d/" + tempSsId + "/export?format=xlsx&access_token=" + ScriptApp.getOAuthToken();
  var blob = UrlFetchApp.fetch(url).getBlob().setName(name + ".xlsx");
  destination.createFile(blob);

  // 删除临时副本
  DriveApp.getFileById(tempSsId).setTrashed(true);

  SpreadsheetApp.getActiveSpreadsheet().toast("Excel file has been added to Google Drive folder!");
}

关键改动说明 #

1. 操作目标表格的临时副本，避免修改原表内容。
2. 遍历临时副本的所有工作表，通过copyTo方法将单元格内容替换为值，同时保留原格式。
3. 导出完成后将临时文件移入回收站，清理冗余文件。

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