Google Sheets脚本开发需求：游戏物品库存更新与文本匹配

1. Google Apps Script Runtime Limits #

• Consumer/free accounts: Maximum execution time is 6 minutes per script run.
• Google Workspace/Enterprise accounts: Up to 30 minutes per run.
• If your script hits this limit, it throws a TimeoutError. Mitigation tips:
  • Process logs in batches (e.g., handle 50 entries per run instead of all at once).
  • Use time-driven triggers to run the script periodically (e.g., every 5 minutes) to catch unprocessed entries.
  • Minimize API calls—avoid reading/writing cells one by one; use batch operations instead.

2. Efficient String Extraction, Storage & Batch Comparison #

With 800+ items, looping through every inventory row for each log entry is slow. Use a JavaScript Map (hash map) for O(1) lookups, plus batch read/write operations to optimize performance.

Step-by-Step Implementation #

a. Preprocess Inventory into a Map

Read the entire inventory sheet once, then store item names as keys, with values containing their row index and current quantity. This avoids re-scanning the inventory sheet for every log entry.

b. Process Log Entries in Batch

Read all unprocessed log entries, check each against the inventory Map, and collect updates to apply in one go.

c. Batch Updates & Error Marking

• For valid items: Collect all inventory quantity updates and write them to the sheet with a single setValues() call.
• For invalid items: Mark the entire log row red in a single batch formatting operation.

Sample Script #

function updateInventory() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const inventorySheet = ss.getSheetByName("库存");
  const logSheet = ss.getSheetByName("日志");
  
  // 1. Build inventory map for quick lookups
  const inventoryData = inventorySheet.getDataRange().getValues();
  const inventoryMap = new Map();
  
  // Assume item name = column A, quantity = column B (adjust indices as needed)
  for (let i = 1; i < inventoryData.length; i++) { // Skip header row
    const itemName = inventoryData[i][0].trim().toLowerCase();
    if (itemName) {
      inventoryMap.set(itemName, {
        row: i + 1, // Sheets rows are 1-indexed
        quantity: inventoryData[i][1] || 0
      });
    }
  }
  
  // 2. Read log data and process unprocessed entries
  const logData = logSheet.getDataRange().getValues();
  const logBackgrounds = logSheet.getDataRange().getBackgrounds();
  const processedCol = logData[0].length; // Add "Processed" column at end of log sheet
  
  // Add header for processed status if missing
  if (logData[0][processedCol] !== "Processed") {
    logSheet.getRange(1, processedCol + 1).setValue("Processed");
    logData[0][processedCol] = "Processed";
  }
  
  const inventoryUpdates = [];
  
  for (let i = 1; i < logData.length; i++) {
    const logRow = logData[i];
    const itemName = logRow[1].trim().toLowerCase(); // Assume item name = column B
    const action = logRow[2].trim(); // "存入" or "取出" = column C
    const amount = parseInt(logRow[3]); // Amount = column D
    const isProcessed = logRow[processedCol];
    
    if (isProcessed === "Yes") continue; // Skip already processed entries
    
    if (inventoryMap.has(itemName)) {
      // Calculate new quantity and track update
      const item = inventoryMap.get(itemName);
      const newQuantity = action === "存入" ? item.quantity + amount : item.quantity - amount;
      inventoryUpdates.push([newQuantity, item.row]);
      logData[i][processedCol] = "Yes";
      logBackgrounds[i].fill("#ffffff"); // Reset background to white
    } else {
      // Mark row red for invalid items
      logBackgrounds[i].fill("#ffcccc");
      logData[i][processedCol] = "Error: Item not found";
    }
  }
  
  // 3. Batch update inventory quantities
  if (inventoryUpdates.length > 0) {
    // Sort updates by row to optimize write
    inventoryUpdates.sort((a, b) => a[1] - b[1]);
    const updateRange = inventorySheet.getRange(inventoryUpdates[0][1], 2, inventoryUpdates.length, 1);
    updateRange.setValues(inventoryUpdates.map(u => [u[0]]));
  }
  
  // 4. Batch update log sheet status and formatting
  logSheet.getRange(1, 1, logBackgrounds.length, logBackgrounds[0].length).setBackgrounds(logBackgrounds);
  logSheet.getRange(1, processedCol + 1, logData.length, 1).setValues(logData.map(r => [r[processedCol]]));
}

Key Optimizations #

• Map Lookups: O(1) time per item match instead of O(n) for each log entry.
• Batch Operations: Reduces API calls (each getDataRange() or setValues() is one call, not hundreds of individual cell operations).
• Case Insensitivity: toLowerCase() ensures matches regardless of user input case.
• Processed Flag: Prevents reprocessing the same log entries multiple times.

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