Google Sheets编辑触发器脚本重复输出及条件行复制需求

Got it, let's tackle this problem step by step. I'll provide a script that automatically copies the required data when "Not Found" is entered in column G of the last row in "Members Registration", and includes safeguards to stop duplicate entries.

1. Complete Script Code #

Open your Google Sheet, go to Extensions > Apps Script, replace any existing code with this:

function onEdit(e) {
  // Pull data from the edit event
  const editedSheet = e.source.getActiveSheet();
  const editedCell = e.range;
  const editedValue = e.value;

  // Define your target sheet names
  const sourceSheetName = "Members Registration";
  const targetSheetName = "sheet18";

  // Exit early if the edit doesn't match our criteria
  if (editedSheet.getName() !== sourceSheetName || editedCell.getColumn() !== 7 || editedValue !== "Not Found") {
    return;
  }

  // Make sure we're editing the LAST row of the source sheet
  const sourceLastRow = editedSheet.getLastRow();
  if (editedCell.getRow() !== sourceLastRow) {
    return;
  }

  // Grab Name (Column B) and Email (Column E) from the edited row
  const name = editedSheet.getRange(sourceLastRow, 2).getValue();
  const email = editedSheet.getRange(sourceLastRow, 5).getValue();

  // Skip if either field is empty (no point copying incomplete data)
  if (!name || !email) {
    return;
  }

  // Access the target sheet
  const targetSheet = e.source.getSheetByName(targetSheetName);
  if (!targetSheet) {
    console.log("Target sheet 'sheet18' not found!");
    return;
  }

  // Check for duplicates using email (unique identifier)
  const targetData = targetSheet.getRange(1, 2, targetSheet.getLastRow(), 4).getValues(); // Columns B to E
  const isDuplicate = targetData.some(row => row[3] === email); // Email lives in index 3 of this range

  if (isDuplicate) {
    console.log("Duplicate entry detected - skipping copy.");
    return;
  }

  // Find the next empty row in the target sheet
  const targetLastRow = targetSheet.getLastRow();
  const nextEmptyRow = targetLastRow === 0 ? 1 : targetLastRow + 1;

  // Paste the Name and Email into the target sheet
  targetSheet.getRange(nextEmptyRow, 2).setValue(name);
  targetSheet.getRange(nextEmptyRow, 5).setValue(email);
}

2. How It Fixes Duplicate Entries #

I built in two layers of protection to stop unwanted repeats:

• Trigger Guardrails: The script only runs if you edit the last row of "Members Registration", in column G, and the value is exactly "Not Found". This eliminates accidental triggers from edits elsewhere in the sheet.
• Duplicate Check: Before copying, the script scans "sheet18" for an existing entry with the same email (since emails are almost always unique). If a match exists, it skips the copy operation entirely.

3. How to Test #

1. Save the script (give your project a name like "Registration Auto-Copy")
2. Head back to your Google Sheet
3. In the last row of "Members Registration", type "Not Found" in column G
4. Check "sheet18" – the corresponding name and email should appear in the next empty row
5. Try entering "Not Found" again in the same row – the script will log a duplicate warning and won't copy the data a second time

Quick Adjustments #

• If you want to use name instead of email as the unique check, modify the isDuplicate line to compare row[0] === name instead.
• This uses a simple onEdit trigger, so it runs automatically without needing to set up a separate installable trigger.

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