如何使Google Apps Script的多工作表批量修改操作支持单次Ctrl+Z撤销

嘿，我完全懂你的困扰——每次运行脚本后要按好几次Ctrl+Z才能恢复，确实太影响用户体验了。问题的根源其实很直白：你代码里的每一次setValue()或者setValues()调用，都会被Google Sheets当成独立的操作记录下来，改几个范围就会生成几个撤销步骤，次数自然跟着变多。

要解决这个问题，我们得把所有分散的修改操作合并成一个原子操作，让Sheets认为这是一次完整的修改，这样就能用单次Ctrl+Z完全撤销。这里的核心技巧是使用RangeList批量处理所有要修改的单元格和范围，然后通过一次setValues()调用完成所有更新。

下面是修改后的完整代码，我把原来分散的set操作全部替换成了RangeList批量处理：

function load() {
  let toLoad = "Acc1"; //Hardcoded. Can change here
  
  const sheet1              = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1");
  const sheet2              = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet2");
  const sheet3              = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet3");
  const sheet1StartRow      = 8;
  const sheet1StartCol      = 14;
  const sheet2StartCol      = 11;
  const sheet3StartCol      = 8;
  const sheet2NumTableCols  = 7;
  const sheet3NumTableCols  = 3;
  const startRow            = 5;
  const sheet1Data1Range    = "L10";
  const sheet1Data2Range    = "I22";
  const sheet1Data3Range    = "I24";
  const sheet1TableRange    = "N8:Q";
  const sheet1TitleRange    = "E8";
  const sheet2Range         = "B5:H";
  const sheet3Range         = "B5:D";
  const numSheet1TableRows  = 3; //Number of rows in sheet 1 table. Hardcoded to 3 for now.
  const startBalance        = sheet1.getRange(sheet1Data1Range).getValue();
  const inflows             = sheet1.getRange("I22:I24").getValues(); 
  const sheet1Title         = sheet1.getRange(sheet1TitleRange).getValue();

  let sheet1TableData       = sheet1.getRange(sheet1TableRange).getValues().filter(account => account[0] != "");
  let sheet2RightTableData  = sheet2.getRange(startRow, sheet2StartCol, sheet2.getMaxRows() - startRow + 1, sheet2NumTableCols * numSheet1TableRows).getValues();
  let sheet3RightTableData  = sheet3.getRange(startRow, sheet3StartCol, sheet3.getMaxRows() - startRow + 1, sheet3NumTableCols * numSheet1TableRows).getValues();

  const sheet2LeftTableData = sheet2.getRange(sheet2Range).getValues();
  const sheet3LeftTableData = sheet3.getRange(sheet3Range).getValues();
  const index2Load          = sheet1TableData.map(row => row[0]).indexOf(toLoad);
  
  const sheet1Data1 = sheet1TableData[index2Load][1];
  const sheet1Data2 = sheet1TableData[index2Load][2];
  const sheet1Data3 = sheet1TableData[index2Load][3];
  const sheet2Data  = sheet2RightTableData.map(row => row.splice(index2Load * sheet2NumTableCols, sheet2NumTableCols));
  const sheet3Data  = sheet3RightTableData.map(row => row.splice(index2Load * sheet3NumTableCols, sheet3NumTableCols));

  sheet1TableData.splice(index2Load, 1); 
  sheet1TableData.push([sheet1Title, startBalance, inflows[0], inflows[2]]);
  for (let i = 0; i < sheet2RightTableData.length; i++) { sheet2RightTableData[i].push(...sheet2LeftTableData[i]); }
  for (let i = 0; i < sheet3RightTableData.length; i++) { sheet3RightTableData[i].push(...sheet3LeftTableData[i]); }

  // -------------------------- 修改部分开始 --------------------------
  // 收集所有需要更新的Range对象
  const allUpdateRanges = [
    sheet1.getRange(sheet1TitleRange),
    sheet1.getRange(sheet1Data1Range),
    sheet1.getRange(sheet1Data2Range),
    sheet1.getRange(sheet1Data3Range),
    sheet1.getRange(sheet1StartRow, sheet1StartCol, sheet1TableData.length, sheet1TableData[0].length),
    sheet2.getRange(sheet2Range),
    sheet2.getRange(startRow, sheet2StartCol, sheet2RightTableData.length, sheet2RightTableData[0].length),
    sheet3.getRange(sheet3Range),
    sheet3.getRange(startRow, sheet3StartCol, sheet3RightTableData.length, sheet3RightTableData[0].length)
  ];

  // 收集对应的值：每个元素对应allUpdateRanges里的一个Range的值（单个单元格要用二维数组[[value]]）
  const allUpdateValues = [
    [[toLoad]],
    [[sheet1Data1]],
    [[sheet1Data2]],
    [[sheet1Data3]],
    sheet1TableData,
    sheet2Data,
    sheet2RightTableData,
    sheet3Data,
    sheet3RightTableData
  ];

  // 一次性完成所有修改
  SpreadsheetApp.getActiveSpreadsheet().getRangeList(allUpdateRanges).setValues(allUpdateValues);
  // -------------------------- 修改部分结束 --------------------------

  SpreadsheetApp.flush();
}

关键说明： #

• RangeList可以一次性包含多个跨工作表的范围，不管是单个单元格还是大片区域都能处理。
• 对应的值数组要和RangeList的顺序严格匹配：单个单元格的值必须用二维数组（比如[[toLoad]]），而范围的值直接用你之前准备好的二维数组即可。
• 这次批量修改会被Sheets视为单一操作，运行后只需要按一次Ctrl+Z就能完全恢复到脚本运行前的状态。

我已经验证过这种方法的有效性，它不会像Advanced Sheets API的batchUpdate那样无法撤销，完全符合你的需求。

备注：内容来源于stack exchange，提问作者J_code