求开发类似Merge Sheets插件的Google Apps Script：基于键列合并表格

Absolutely! I’ve built a simplified Google Apps Script that handles the bidirectional sync you need—just like the core functionality of the Merge Sheets plugin. This script will sync entire rows between Sheet1 and Sheet3 whenever you edit a row, based on a shared key column.

Script Code #

Here’s the full script. You can tweak the configuration to match your actual key column:

// Configuration: Column number of your shared key (A=1, B=2, etc.)
const KEY_COLUMN = 1;

function onEdit(e) {
  const editedSheet = e.source.getActiveSheet();
  const editedRow = e.range.getRow();
  
  // Only run for edits in Sheet1 or Sheet3
  if (!["Sheet1", "Sheet3"].includes(editedSheet.getName())) return;
  
  // Get the key value from the edited row
  const keyValue = editedSheet.getRange(editedRow, KEY_COLUMN).getValue();
  if (!keyValue) return; // Skip if key is empty
  
  // Define the target sheet (the other one)
  const targetSheetName = editedSheet.getName() === "Sheet1" ? "Sheet3" : "Sheet1";
  const targetSheet = e.source.getSheetByName(targetSheetName);
  
  // Search for the matching key in the target sheet
  const keyRange = targetSheet.getRange(1, KEY_COLUMN, targetSheet.getLastRow(), 1);
  const keyValues = keyRange.getValues().flat();
  const targetRowIndex = keyValues.indexOf(keyValue);
  
  // Get all data from the edited row
  const editedRowData = editedSheet.getRange(editedRow, 1, 1, editedSheet.getLastColumn()).getValues()[0];
  
  if (targetRowIndex !== -1) {
    // Update the matching row in target sheet
    targetSheet.getRange(targetRowIndex + 1, 1, 1, editedRowData.length).setValues([editedRowData]);
  } else {
    // Optional: Add a new row if no match is found
    targetSheet.appendRow(editedRowData);
  }
}

Setup Steps #

Follow these to get the script working:

• Open your Google Sheet, go to Extensions > Apps Script
• Delete the default myFunction() code, then paste the script above
• Adjust the KEY_COLUMN variable if your shared key isn’t in column A (e.g., set to 2 for column B)
• Save the script (click the floppy disk icon) and name it something like "SheetSyncTool"
• Set up a trigger:
  1. Click the clock icon (Triggers) on the left sidebar
  2. Click Add Trigger
  3. For "Choose which function to run", select onEdit
  4. For "Select event source", choose From spreadsheet
  5. For "Select event type", choose On edit
  6. Click Save—you’ll need to authorize the script (follow the prompts, it’s safe to allow access)

How It Works #

• When you edit any cell in a row of Sheet1 or Sheet3, the script kicks in immediately
• It grabs the key value from your designated key column in the edited row
• It searches the other sheet for a row with the same key value
• If a match is found, it updates the entire row in the target sheet to match the edited row
• If no match is found, it adds the edited row to the end of the target sheet (you can remove this part by deleting the else block if you don’t want it)

Important Notes #

• Make sure both sheets have the same column structure—otherwise, data might sync to the wrong columns
• Ensure your key column has unique values—if there are duplicate keys, the script will only update the first matching row it finds
• Test with a copy of your data first to avoid accidental overwrites
• If you only want to sync specific columns (not the whole row), modify the getRange lines to select only the columns you need

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