解决Google Sheets中导入数据与手动数据不匹配问题

What's Wrong With Your Current Script #

• Simple Trigger Limits: The default onEdit trigger can't access external spreadsheets (via openById) and has restricted permissions—this is why it fails to detect edits on your source sheet.
• Wrong Trigger Binding: Your trigger is attached to the active spreadsheet, not the source one. It will never fire when you edit the source sheet.
• Logic Gaps: The loop doesn't actually re-map your manual data to new rows; it just copies data back to the same row instead of matching idLookup values to updated showIDs in column A.

Corrected Script #

This script must be bound to your source spreadsheet (the one you edit to trigger updates), not the active one. Replace your code with this:

function syncManualData() {
  // Update these values to match your setup
  const SOURCE_SHEET_NAME = "Shows";
  const ACTIVE_SPREADSHEET_ID = "YOUR_ACTIVE_SPREADSHEET_ID"; // Replace with your active sheet's ID (from URL)
  const ACTIVE_SHEET_NAME = "Shows";
  
  // Get the source sheet (this script lives in the source spreadsheet)
  const sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SOURCE_SHEET_NAME);
  const editedCell = SpreadsheetApp.getActiveRange();
  
  // Check if edit is in column N (14) and set to "confirmed"
  if (editedCell.getColumn() !== 14 || editedCell.getValue().toLowerCase() !== "confirmed") return;
  
  // Skip if column K starts with "Sold"
  const kValue = editedCell.offset(0, -3).getValue().toString();
  if (kValue.slice(0,4) === "Sold") return;
  
  // Access your active spreadsheet and sheet
  const activeSpreadsheet = SpreadsheetApp.openById(ACTIVE_SPREADSHEET_ID);
  const activeSheet = activeSpreadsheet.getSheetByName(ACTIVE_SHEET_NAME);
  
  // Pull all data from active sheet in one go (faster than individual cell calls)
  const allActiveData = activeSheet.getDataRange().getValues();
  const headerRowCount = 1;
  const dataRows = allActiveData.slice(headerRowCount);
  
  // Create a map of idLookup (column I) to manual data (columns I-U)
  const manualDataMap = new Map();
  dataRows.forEach((row, index) => {
    const rowNumber = index + headerRowCount + 1;
    const idLookup = row[8]; // Column I is index 8 (0-based)
    if (idLookup) {
      // Grab columns I to U (indices 8 through 20)
      const manualData = row.slice(8, 21);
      manualDataMap.set(idLookup, manualData);
    }
  });
  
  // Update active sheet: match idLookup to showID (column A) and restore manual data
  const updatedShowIDs = activeSheet.getRange(2, 1, activeSheet.getLastRow()-1, 1).getValues().flat();
  updatedShowIDs.forEach((showID, index) => {
    const rowNumber = index + 2;
    const savedManualData = manualDataMap.get(showID);
    if (savedManualData) {
      // Paste the saved manual data back to columns I-U
      activeSheet.getRange(rowNumber, 9, 1, savedManualData.length).setValues([savedManualData]);
    } else {
      // For new rows, set column I to the showID
      activeSheet.getRange(rowNumber, 9).setValue(showID);
    }
  });
  
  // Confirm success
  SpreadsheetApp.getUi().alert("Shows updated - manual data re-aligned correctly");
}

Step-by-Step Setup #

1. Attach the Script to Your Source Spreadsheet #

• Open your source spreadsheet (the one you edit that causes the active sheet to update).
• Click Extensions > Apps Script to open the code editor.
• Delete any existing code and paste the script above.
• Replace YOUR_ACTIVE_SPREADSHEET_ID with the ID from your active sheet's URL (it's the string between /d/ and /edit).
• Save the script (name it something like SyncManualData).

2. Create an Installable Trigger #

• In the Apps Script editor, click the clock icon (Triggers) on the left sidebar.
• Click Add Trigger at the bottom right.
• Configure the trigger as follows:
  • Function to run: syncManualData
  • Deployment: Head
  • Event source: From spreadsheet
  • Event type: On edit
• Click Save. You'll need to authorize the script—follow the prompts, select your Google account, and allow permissions (you may need to click "Advanced" then "Go to [Script Name]" to proceed).

3. Test the Setup #

• Go to your source sheet, edit a cell in column N to "confirmed" (ensure column K's value doesn't start with "Sold").
• Check your active sheet: your manual data in columns I-U should now line up with the updated showIDs in column A.
• A pop-up will confirm the update worked.

Why This Fixes the Problem #

• Installable Trigger: Bypasses simple trigger restrictions, allowing access to your active spreadsheet and firing when you edit the source sheet.
• Data Mapping: Stores manual data linked to idLookup values, then matches those to new showID rows in the active sheet to re-align data correctly.
• Faster Performance: Pulls all data at once instead of looping through individual cells, avoiding slowdowns with large datasets.

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