Let's break down why your trigger isn't working and fix it step by step:

What's Going Wrong? #

From your description, your copy logic works when run manually, but fails when triggered by editing cell C1. Here are the key issues:

1. Simple Trigger Permission Limits: The default onEdit() simple trigger runs without authorization, which means it can't access external spreadsheets (like your target sheet). This is a hard restriction in Google Apps Script.
2. Syntax Error: Your original code has an extra closing } at the end, which would cause the script to fail silently.
3. Misconfigured Trigger: When you tried creating a trigger via code, you bound it to the target spreadsheet instead of the source one—so edits to the source sheet never triggered the function.

Fixed Code & Step-by-Step Solution #

First, replace your code with this corrected version (it adds error handling, fixes syntax, and is built for installable triggers):

function CopyOnC1Edit(e) {
  // Exit immediately if: no event object, or edited cell isn't C1
  if (!e || e.range.getA1Notation() !== 'C1') return;

  try {
    // Source spreadsheet setup
    const sourceSSID = '1ws8yg5_1k4ZZN2amfZ-cNvY-hUMUDGhbwnNPCGJhlAY';
    const sourceSheet = SpreadsheetApp.openById(sourceSSID).getSheetByName('export');
    
    // Get data to copy (B3:C8)
    const dataRange = sourceSheet.getRange('B3:C8');
    const data = dataRange.getValues();

    // Target spreadsheet setup
    const targetSSID = '152JxdVi6Ssqz2ZZ65yDW5p5m0PnP63vApNgfP3jS_Pc';
    const targetSheet = SpreadsheetApp.openById(targetSSID).getSheetByName('import');
    
    // Write data to target sheet (starts at B1, matches data dimensions)
    targetSheet.getRange(1, 2, data.length, data[0].length).setValues(data);
  } catch (error) {
    // Show error alert for debugging
    SpreadsheetApp.getUi().alert(`Error running script: ${error.message}`);
  }
}

Now Set Up the Installable Trigger #

Since simple triggers can't handle cross-spreadsheet operations, we need an installable trigger (which has full authorization):

1. Open the source spreadsheet (where you edit cell C1) and go to Extensions > Apps Script.
2. Paste the corrected code above, replacing any existing code.
3. Manually run the function once: Click the run button ▶️ next to CopyOnC1Edit. You'll be prompted to authorize the script—follow the prompts (you may need to click "Advanced" > "Go to [Script Name]" to proceed).
4. Create the trigger:
   • Click the clock icon ⏰ in the left sidebar to open the Triggers page.
   • Click Add Trigger in the bottom right.
   • Configure these settings:
     • Choose which function to run: CopyOnC1Edit
     • Choose which deployment to run: Head
     • Select event source: From spreadsheet
     • Select event type: On edit
   • Click Save and confirm any additional authorization prompts.

Why This Works #

• The installable trigger runs with your full Google account permissions, so it can access both spreadsheets.
• We added an early exit if the edited cell isn't C1, which prevents unnecessary runs.
• Error handling will pop up an alert if something goes wrong, making debugging easier.

Test It #

Edit cell C1 in your source spreadsheet's main sheet—your target sheet's import tab should now get the data from B3:C8 of the source's export tab.

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