复制参考工作表后如何更新Sheet公式？脚本合并电子表格公式报错求助

Hey there! Let's work through this frustrating issue you're hitting. When you copy the "Materials" sheet from Spreadsheet B to A via script, the formulas in "Calculations" still throw a "sheet not found" error—this is actually a common quirk with how Google Sheets handles formula references during script-based sheet copies. Here's the breakdown and fixes to get it sorted:

When you use copyTo() to move the sheet from B to A, the copied sheet has the right name, but two things might be going wrong:

• The formulas in "Calculations" could still be silently referencing the original "Materials" sheet in Spreadsheet B (even if the formula bar doesn't show this at first).
• Script-based sheet copies don't always trigger the automatic formula refresh that happens when you copy a sheet manually through the UI.

1. Force a Formula Refresh Programmatically #

After copying the sheet, reapply all formulas in the "Calculations" sheet. This forces Google Sheets to re-resolve references to the newly copied local "Materials" sheet. Here's a script snippet to do this:

function copyMaterialsAndFixFormulas() {
  const spreadSheetA = SpreadsheetApp.openById("YOUR_SPREADSHEET_A_ID");
  const spreadSheetB = SpreadsheetApp.openById("YOUR_SPREADSHEET_B_ID");
  
  // Clear existing Materials sheet in A (if any) to avoid name conflicts
  const existingMaterials = spreadSheetA.getSheetByName("Materials");
  if (existingMaterials) {
    spreadSheetA.deleteSheet(existingMaterials);
  }
  
  // Copy Materials from B to A and ensure exact name
  spreadSheetB.getSheetByName("Materials").copyTo(spreadSheetA).setName("Materials");
  
  // Refresh all formulas in Calculations
  const calculationsSheet = spreadSheetA.getSheetByName("Calculations");
  const formulaData = calculationsSheet.getDataRange().getFormulas();
  calculationsSheet.getDataRange().setFormulas(formulaData);
}

2. Clean Up Hidden External References #

Sometimes formulas retain a hidden reference to Spreadsheet B even after you copy the sheet. To fix this, run a find-and-replace to strip out any external workbook references from the formulas:

function removeExternalSheetReferences() {
  const spreadSheetA = SpreadsheetApp.openById("YOUR_SPREADSHEET_A_ID");
  const calculationsSheet = spreadSheetA.getSheetByName("Calculations");
  
  // Replace any external Materials sheet references with local ones
  calculationsSheet.createTextFinder("'\\[.*\\]Materials'!")
    .useRegularExpression(true)
    .replaceAllWith("'Materials'!");
}

Run this right after copying the sheet to ensure all formulas point to the local "Materials" sheet in A.

3. Verify Sheet Name Exactness #

Google Sheets sheet names are case-sensitive, and if a previous copy left a sheet like "Materials (2)" in A, your formulas won't match. The first fix already handles this by deleting existing "Materials" sheets before copying, but double-check the sheet tab name in A after running your script to confirm it's exactly "Materials".

If you want to confirm the root cause, after running your script, edit a broken formula cell (just type a space and delete it), then press Enter. If the error disappears, that means the formula just needed a manual refresh—so the first script fix will automate that for you.

内容的提问来源于stack exchange，提问作者J. Doe