Google App Script优化：批量匹配A列值并更新对应B列

Hey there, I’ve dealt with this exact headache before—those endless if checks become a nightmare once your item count starts climbing past 10 or 15. Let’s replace that clunky setup with a scalable, efficient solution that’ll work for 100+ items without breaking a sweat.

Here’s the revamped onEdit function that handles everything you need:

function onEdit(e) {
  const activeSheet = e.source.getActiveSheet();
  
  // --- Configure these to match your sheet setup ---
  const INPUT_ITEM_CELL = "C1";    // Where you enter iteminput
  const INPUT_INV_CELL = "D1";     // Where you enter invinput
  const INVENTORY_SHEET_NAME = "Inventory"; // Name of your main inventory sheet
  const LOG_SHEET_NAME = "Log";    // Name of your log sheet (adjust or remove if not needed)
  // --- End configuration ---

  // Only run the script if we're editing the input cells on the right sheet
  const editedCell = e.range.getA1Notation();
  if (activeSheet.getName() !== INVENTORY_SHEET_NAME || 
      editedCell !== INPUT_ITEM_CELL && editedCell !== INPUT_INV_CELL) {
    return;
  }

  const itemInput = activeSheet.getRange(INPUT_ITEM_CELL).getValue().trim();
  const invInput = activeSheet.getRange(INPUT_INV_CELL).getValue();
  
  // Skip if either input is empty
  if (!itemInput || invInput === "") return;

  // Fetch all items and current inventory values in bulk (way faster than cell-by-cell)
  const lastRow = activeSheet.getLastRow();
  const itemList = activeSheet.getRange(`A2:A${lastRow}`).getValues().flat(); // Flatten to 1D array
  const inventoryValues = activeSheet.getRange(`B2:B${lastRow}`).getValues().flat();

  // Find the index of the matching item in the list
  const matchIndex = itemList.findIndex(item => item.trim() === itemInput);

  if (matchIndex !== -1) {
    // Calculate and update the new inventory value
    const newInventory = inventoryValues[matchIndex] + invInput;
    activeSheet.getRange(`B${matchIndex + 2}`).setValue(newInventory); // +2 because we started at row 2

    // Add entry to log sheet (optional, adjust the log data as needed)
    const logSheet = e.source.getSheetByName(LOG_SHEET_NAME);
    if (logSheet) {
      const logEntry = [
        new Date(),          // Timestamp
        itemInput,           // Item name
        invInput,            // Quantity added
        newInventory         // Updated inventory
      ];
      logSheet.appendRow(logEntry);
    }

    // Clear the input cells after processing
    activeSheet.getRange(`${INPUT_ITEM_CELL}:${INPUT_INV_CELL}`).clearContent();
  } else {
    // Optional: Alert if the item isn't found in the list
    SpreadsheetApp.getUi().alert(`Oops! Item "${itemInput}" wasn't found in your inventory list.`);
  }
}

What Makes This Better Than Your Original Code? #

• No More Repetitive Ifs: We use findIndex to scan your entire A column in one pass—works just as well for 10 items as 1000.
• Auto-Scaling: The script uses getLastRow() to automatically include new items you add to column A, so you never have to update the code again.
• Faster Performance: Bulk data fetching with getValues() is way more efficient than reading individual cells (Google Apps Script penalizes frequent cell reads/writes).
• Cleaner Guard Clauses: We check upfront if we’re on the right sheet/editing the right cells, so the script doesn’t run unnecessarily.

Quick Customization Notes #

• Tweak the configuration variables at the top to match where your inputs are located and what your sheets are named.
• If you don’t need logging, just remove the entire log sheet section.
• Feel free to delete the alert for missing items if you don’t want it.

Why Your For Loop Might Have Failed #

Most likely, you were reading cells one by one inside the loop (like activeSheet.getRange("A" + i).getValue()). That’s slow and easy to mess up with off-by-one errors. Using bulk getValues() to pull all data into an array first is the standard best practice for Google Apps Script performance.

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