求助：使用Google App Script跨表格更新指定单元格值

Hey there! Let's get this Google Sheets automation working for you. I'll share a complete, tested script and break down the key parts so you can integrate it with your existing code (or use it as a full solution).

This script uses an onEdit trigger to react when someone selects "Restocked" in Column A of Sheet X, then updates the corresponding row in Sheet Y.

function onEdit(e) {
  // Replace with your actual Sheet X and Sheet Y IDs (found in the spreadsheet URL)
  const SHEET_X_ID = "YOUR_SHEET_X_ID";
  const SHEET_Y_ID = "YOUR_SHEET_Y_ID";
  
  // Get the edited cell and its value
  const editedCell = e.range;
  const editedValue = e.value;
  
  // 1. Check if the edit is in Column A of Sheet X, and the value is "Restocked"
  if (editedCell.getSheet().getParent().getId() === SHEET_X_ID && 
      editedCell.getColumn() === 1 && 
      editedValue === "Restocked") {
    
    // 2. Extract the ID from Column B of the same row
    const targetRow = editedCell.getRow();
    const idValue = editedCell.getSheet().getRange(targetRow, 2).getValue();
    
    // 3. Open Sheet Y and get all data to find the matching ID
    const sheetY = SpreadsheetApp.openById(SHEET_Y_ID).getActiveSheet(); // Or specify a sheet name with getSheetByName("SheetName")
    const allData = sheetY.getDataRange().getValues();
    
    // 4. Loop through Sheet Y to find the row with the matching ID
    for (let i = 0; i < allData.length; i++) {
      const rowId = allData[i][1]; // Assuming ID is in Column B of Sheet Y (adjust index if needed)
      if (rowId === idValue) {
        // Choose either to update the LAST COLUMN or fixed Column AZ (52nd column)
        const columnToUpdate = sheetY.getLastColumn(); // Use this for dynamic last column
        // const columnToUpdate = 52; // Uncomment this line to use fixed Column AZ instead
        
        // Update the cell value to "Restocked"
        sheetY.getRange(i + 1, columnToUpdate).setValue("Restocked");
        break; // Exit loop once we find the matching row
      }
    }
  }
}

Here are common pitfalls to watch for (and fixes if your existing code was stuck):

• Permission Setup: The first time you run this script, you'll need to authorize it. Go to the Apps Script editor, click the run button once, and follow the prompts to grant access (you may need to click "Advanced" > "Go to [Script Name]" to bypass the warning).
• ID Matching: Ensure the ID in Sheet X Column B matches the ID format in Sheet Y (e.g., if it's a number, don't compare it as a string). If IDs are stored as text, use String(rowId) === String(idValue) to avoid type mismatches.
• Sheet Specificity: If Sheet Y has multiple tabs, replace getActiveSheet() with getSheetByName("YourTabName") to target the correct sheet.
• Efficiency for Large Datasets: If Sheet Y has thousands of rows, using findIndex instead of a loop can be faster. Replace the for loop with:

  const matchRowIndex = allData.findIndex(row => row[1] === idValue);
  if (matchRowIndex !== -1) {
    sheetY.getRange(matchRowIndex + 1, columnToUpdate).setValue("Restocked");
  }
  

• Trigger Reliability: The onEdit trigger runs automatically when a user edits the sheet, but it won't trigger if the edit is done via another script or API. For those cases, you'd need to set up an installable trigger.

If you already have partial code, check these areas:

• Did you correctly use the e event object to get the edited cell? Hardcoding cell ranges will break the automation when different rows are edited.
• Are you opening Sheet Y with the correct ID? Double-check the URL string—spreadsheet IDs are 44-character strings between /d/ and /edit in the URL.
• Is your ID comparison logic correct? A common issue is comparing a number to a string (e.g., 123 vs "123"), which won't match.

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