Hey there, let's work through the issues you're hitting with your Google Sheets + Apps Script project—concurrent onEdit triggers causing duplicate template copies and messy auto-named sheets is a super common pain point, but we've got solid fixes for this!

1. Add a Script Lock to Block Overlapping Runs #

The core problem here is rapid consecutive onEdit triggers running your script at the same time, creating race conditions. Google's LockService is made for exactly this scenario—it ensures only one instance of your script runs at a time:

function onEdit(e) {
  // Grab a script lock that stays active for up to 30 seconds
  const lock = LockService.getScriptLock();
  try {
    // Wait 10 seconds max for the lock if it's already held by another execution
    if (!lock.tryLock(10000)) {
      SpreadsheetApp.getUi().alert("Too many requests at once—please slow down and try again!");
      return;
    }

    // Your existing query handling code starts here
    const userQuery = e.value;
    if (!userQuery) return;

    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const templateSheet = ss.getSheetByName("template");
    if (!templateSheet) return;

    // Check if a sheet for this query already exists (no duplicates!)
    const existingSheet = ss.getSheetByName(userQuery);
    if (existingSheet) {
      SpreadsheetApp.getUi().alert(`A sheet for "${userQuery}" is already here!`);
      return;
    }

    // Copy the template and rename it directly to the user's query
    const newSheet = templateSheet.copyTo(ss);
    newSheet.setName(userQuery);

  } finally {
    // Always release the lock when done—even if an error pops up
    lock.releaseLock();
  }
}

2. Ditch Auto-Naming for Query-Based Sheet Names #

Instead of letting Google generate messy "Copy of template 1/2" names, use the user's search query directly as the sheet name. The check for existing sheets (in the code above) also ensures you never create duplicates for the same query. This makes your spreadsheet way more organized too!

3. Switch to an Installable Trigger (Optional but Recommended) #

Simple onEdit triggers have strict limitations (like shorter run times and restricted service access). For better control, switch to an installable onEdit trigger:

• Open your Apps Script editor > Go to Edit > Current project's triggers
• Click "Add trigger"
• Select your edit handler function, choose "From spreadsheet", and pick "On edit"

Installable triggers have fewer restrictions, and you can even add extra logic like rate-limiting (e.g., only allow one run every 5 seconds) if needed.

4. Add Input Debouncing for Accidental Rapid Edits #

If users are triggering the script by typing too fast (like backspacing and retyping), add a debounce to ignore rapid consecutive edits:

// Use PropertiesService to store the last execution time across runs
const PROPERTIES = PropertiesService.getScriptProperties();

function onEdit(e) {
  const lock = LockService.getScriptLock();
  try {
    if (!lock.tryLock(10000)) return;

    const lastRunTime = Number(PROPERTIES.getProperty("lastRunTime")) || 0;
    const currentTime = Date.now();
    // Ignore edits that happen within 3 seconds of the last run
    if (currentTime - lastRunTime < 3000) {
      return;
    }
    PROPERTIES.setProperty("lastRunTime", currentTime.toString());

    // Rest of your query handling code here...

  } finally {
    lock.releaseLock();
  }
}

This way, only spaced-out or final edits will trigger your script, cutting down on unnecessary runs.

Putting all these tweaks together should eliminate the duplicate sheets and code conflicts you're dealing with. Let me know if you need help adapting any of this to your specific script!

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