Google Sheets 双向同步需求：实现Master表与Trip系列子表的编号增删同步及代码优化

Got it, let's fix this sync issue so that deleting an ID from the Master sheet's A2:A range automatically removes that same ID from all "Trip 1" through "Trip 10" sheets' A4:A ranges. Your existing code handles adding new IDs, but we need to adjust the logic to fully mirror the Master's ID list in every Trip sheet—including deletions.

Simplified Full Sync Code #

The cleanest way to handle both additions and deletions is to replace the Trip sheets' ID lists entirely with the current state of the Master sheet. This ensures perfect alignment every time the script runs:

function updateSheet() {
  const ss = SpreadsheetApp.getActive();
  
  // Grab all non-empty IDs from Master's A2:A range
  const masterIds = ss.getSheetByName("Master")
    .getRange("A2:A")
    .getValues()
    .filter(row => row[0].toString().trim() !== "");

  // List of all Trip sheets to sync
  const sheetNames = [
    "Trip 1", "Trip 2", "Trip 3", "Trip 4", "Trip 5",
    "Trip 6", "Trip 7", "Trip 8", "Trip 9", "Trip 10"
  ];

  sheetNames.forEach(name => {
    const targetSheet = ss.getSheetByName(name);
    if (!targetSheet) return; // Skip if the sheet doesn't exist

    // Clear existing IDs in the Trip sheet's A4:A range
    targetSheet.getRange("A4:A").clearContent();

    // Write the latest Master IDs starting at A4 (if there are any)
    if (masterIds.length > 0) {
      targetSheet.getRange(4, 1, masterIds.length, 1).setValues(masterIds);
    }
  });
}

Why This Works #

• Full Mirroring: Instead of calculating differences (which your original code tried to do), we just wipe the Trip sheets' ID range and replace it with the Master's current IDs. This instantly reflects any deletions or additions.
• Empty Row Filtering: We skip blank rows in the Master sheet to avoid writing empty cells to the Trip sheets.
• Error Prevention: The code checks if each Trip sheet exists before trying to modify it, just like your original code.

Alternative: Preserve Custom Entries in Trip Sheets #

If you have custom entries in the Trip sheets' A4:A range that aren't in the Master sheet and want to keep them, use this version. It will only remove IDs that were deleted from Master, while keeping your custom entries:

function updateSheetWithCustomEntries() {
  // Helper to check if an ID exists in the Master list
  const isIdInMaster = (id, masterList) => {
    return masterList.some(row => row[0].toString().trim() === id.toString().trim());
  };

  const ss = SpreadsheetApp.getActive();
  const masterIds = ss.getSheetByName("Master")
    .getRange("A2:A")
    .getValues()
    .filter(row => row[0].toString().trim() !== "");

  const sheetNames = [
    "Trip 1", "Trip 2", "Trip 3", "Trip 4", "Trip 5",
    "Trip 6", "Trip 7", "Trip 8", "Trip 9", "Trip 10"
  ];

  sheetNames.forEach(name => {
    const targetSheet = ss.getSheetByName(name);
    if (!targetSheet) return;

    // Get all non-empty entries from the Trip sheet
    const currentTripEntries = targetSheet.getRange("A4:A")
      .getValues()
      .filter(row => row[0].toString().trim() !== "");

    // Filter out entries that were deleted from Master
    const keptEntries = currentTripEntries.filter(entry => {
      const entryId = entry[0].toString().trim();
      // Keep custom entries (not in Master) OR entries still present in Master
      return !isIdInMaster(entryId, masterIds) || isIdInMaster(entryId, masterIds);
    });

    // Add any new Master IDs that aren't already in the Trip sheet
    masterIds.forEach(masterId => {
      const masterIdStr = masterId[0].toString().trim();
      if (!keptEntries.some(entry => entry[0].toString().trim() === masterIdStr)) {
        keptEntries.push(masterId);
      }
    });

    // Update the Trip sheet
    targetSheet.getRange("A4:A").clearContent();
    if (keptEntries.length > 0) {
      targetSheet.getRange(4, 1, keptEntries.length, 1).setValues(keptEntries);
    }
  });
}

Set Up Auto-Sync #

To make this run automatically whenever you edit the Master sheet:

1. Open your Google Sheet, go to Extensions > Apps Script.
2. In the left sidebar, click the clock icon (Triggers).
3. Click Add Trigger, then configure:
   • Choose function: updateSheet (or updateSheetWithCustomEntries if you need that version)
   • Event source: From spreadsheet
   • Event type: On edit
4. Save the trigger—now your sheets will sync automatically!

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