如何使用onEdit触发器将数据复制到另一个Google Sheet并实现同ID覆盖？附现有脚本问题求助

Hey there! Let's break down and solve your two issues step by step to get your script working as expected:

1. Why Your onEdit Trigger Isn't Running #

The main culprit here is that simple onEdit() triggers don’t have permission to access external Google Sheets files. When your script tries to call SpreadsheetApp.openById() to open the target sheet, the simple trigger hits an authorization block and stops working.

To fix this:

• Replace the simple onEdit() with an installable onEdit trigger:
  1. Open your Google Sheet's script editor
  2. Click the clock icon (Triggers) in the left sidebar
  3. Add a new trigger: select your function name, set event source to "From spreadsheet", event type to "On edit"
• Use e.source.getActiveSheet() instead of SpreadsheetApp.getActiveSheet() to reliably reference the sheet that triggered the edit (this avoids unexpected behavior if multiple sheets are open)

2. Adding the ID Check Logic #

To implement the "check for existing ID, overwrite or add new column" logic, we’ll need to:

• Extract the ID from your source data (assuming this is cell C4 from your source range C4:C14)
• Fetch all IDs from row 2 of the target sheet
• Loop through these IDs to find a match:
  • If a match exists: overwrite the entire column’s data (rows 3-13) with your source data
  • If no match exists: find the first empty column in row 2 and paste the source data there

Full Modified Script #

function handleEdit(e) {
  // Only run if the edit is on cell C16 and the value is 'Submit'
  if (e.range.columnStart !== 3 || e.range.rowStart !== 16 || e.value !== 'Submit') {
    return;
  }

  try {
    // Get source sheet and data
    const sourceSheet = e.source.getActiveSheet();
    const sourceRange = sourceSheet.getRange("C4:C14");
    const sourceData = sourceRange.getValues();
    const sourceId = sourceData[0][0]; // ID is the first value in the source range (C4)

    // Access target sheet
    const targetFile = SpreadsheetApp.openById("11tpC8SNZ5XB35n7GON0St3ZQ37dIbM8UbXRjmkVAeJQ");
    const targetSheet = targetFile.getSheetByName("Sheet1");
    
    // Get all IDs from row 2 of target sheet
    const targetIdRow = targetSheet.getRange(2, 1, 1, targetSheet.getLastColumn()).getValues()[0];
    let targetColumn = -1;

    // Check for existing ID
    for (let i = 0; i < targetIdRow.length; i++) {
      if (targetIdRow[i] === sourceId) {
        targetColumn = i + 1; // Convert array index to column number
        break;
      }
    }

    // If no existing ID, find the first empty column in row 2
    if (targetColumn === -1) {
      targetColumn = targetSheet.getLastColumn() + 1;
      // Set the ID in row 2 of the new column
      targetSheet.getRange(2, targetColumn).setValue(sourceId);
    }

    // Paste source data into the target column (rows 3-13)
    const targetRange = targetSheet.getRange(3, targetColumn, sourceData.length, 1);
    targetRange.setValues(sourceData);

    // Clear the 'Submit' cell
    e.range.clearContent();
  } catch (error) {
    // Log any errors for debugging
    console.error("Script error:", error);
    // Optional: Show an alert to the user
    SpreadsheetApp.getUi().alert("An error occurred while processing your request: " + error.message);
  }
}

Key Details to Note #

• Installable Trigger: This grants the script permission to access external sheets, fixing your first issue
• Efficient Data Handling: Using getValues() and setValues() is faster and more reliable than copyTo() for direct data manipulation
• Error Handling: The try/catch block helps catch and debug issues, plus it gives clear feedback to you if something goes wrong
• ID Matching: We explicitly check row 2 of the target sheet for existing IDs, ensuring we either overwrite the correct column or add a new one cleanly

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