优化Google Sheets中散列单元格变更脚本的执行速度

Your current approach works but is slow because every individual cell operation (like getCell(), setValue(), setBackground()) makes a separate call to the Google Sheets service. These network round-trips add up quickly, especially for larger ranges. Let's fix this with bulk operations and in-memory processing—here's how:

Key Optimizations to Speed Up Your Script #

1. Bulk Read All Values at Once #

Instead of fetching cell values one by one, grab the entire range's display values in a single call using getDisplayValues(). This reduces service calls from hundreds/thousands to just 2 (one for the summary sheet, one for the copy sheet).

2. Compare Values in Memory #

Loop through the 2D arrays of values directly in your script (no more getCell() calls). Track which cells have changed and build two things:

• A 2D array for background colors (to reset or highlight cells in bulk)
• The updated values to write back to the copy sheet

3. Bulk Write Updates and Backgrounds #

Use setDisplayValues() to update the copy sheet in one go, and setBackgrounds() to apply all color changes at once. If you only need to highlight scattered cells, you can also build a RangeList of changed cells and set their backgrounds in a single call.

Optimized Script Example #

function highlightChanges() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const summarySheet = ss.getSheetByName("Summary"); // Replace with your sheet name
  const copySheet = ss.getSheetByName("Summary Copy"); // Replace with your sheet name
  
  // Define your target range (adjust rows/cols as needed)
  const srcRange = summarySheet.getRange(1, 1, summarySheet.getLastRow(), summarySheet.getLastColumn());
  const dstRange = copySheet.getRange(1, 1, copySheet.getLastRow(), copySheet.getLastColumn());
  
  // Bulk read display values (1 service call each)
  const srcValues = srcRange.getDisplayValues();
  const dstValues = dstRange.getDisplayValues();
  
  // Initialize background array (default to white)
  const backgroundColors = srcValues.map(row => row.map(() => "white"));
  const updatedDstValues = [...srcValues]; // Prepare to update copy sheet with new values
  
  // In-memory comparison (no service calls here)
  for (let row = 0; row < srcValues.length; row++) {
    for (let col = 0; col < srcValues[row].length; col++) {
      if (srcValues[row][col] !== dstValues[row][col]) {
        backgroundColors[row][col] = "gray"; // Mark cell to highlight
      }
    }
  }
  
  // Bulk write operations (2 service calls total)
  srcRange.setBackgrounds(backgroundColors); // Apply all color changes
  dstRange.setDisplayValues(updatedDstValues); // Sync copy sheet with latest values
}

Even Faster: Target Only Changed Cells (For Scattered Updates) #

If your changes are truly scattered and you don't want to reset the entire range's background every time, you can collect the A1 notation of changed cells and use RangeList to update only those cells:

function highlightScatteredChanges() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const summarySheet = ss.getSheetByName("Summary");
  const copySheet = ss.getSheetByName("Summary Copy");
  
  const srcRange = summarySheet.getRange(1, 1, summarySheet.getLastRow(), summarySheet.getLastColumn());
  const dstRange = copySheet.getRange(1, 1, copySheet.getLastRow(), copySheet.getLastColumn());
  
  const srcValues = srcRange.getDisplayValues();
  const dstValues = dstRange.getDisplayValues();
  
  const changedCells = [];
  const updatedDstValues = [...srcValues];
  
  for (let row = 0; row < srcValues.length; row++) {
    for (let col = 0; col < srcValues[row].length; col++) {
      if (srcValues[row][col] !== dstValues[row][col]) {
        // Convert row/col (0-indexed) to A1 notation (1-indexed)
        const a1Notation = summarySheet.getRange(row + 1, col + 1).getA1Notation();
        changedCells.push(a1Notation);
      }
    }
  }
  
  // Reset and highlight only changed cells
  if (changedCells.length > 0) {
    summarySheet.getRangeList(changedCells).setBackground("white");
    summarySheet.getRangeList(changedCells).setBackground("gray");
    // Sync copy sheet with latest values
    dstRange.setDisplayValues(updatedDstValues);
  }
}

Additional Tips #

• Trigger Optimization: Use an onChange trigger instead of onEdit if your summary sheet updates via formula changes (since onEdit only triggers on manual edits). This ensures the script runs only when the summary sheet's values actually change.
• Limit the Range: Don't process the entire sheet—define a specific range that contains your summary data (e.g., getRange(2, 1, 100, 10) instead of using getLastRow() if you know your data doesn't go beyond row 100).
• Avoid Unnecessary Calls: If you don't need to reset all backgrounds every time, skip the bulk white reset and only update the changed cells' colors.

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