如何实现高效搜索功能？现有遍历工作表代码需性能优化

Absolutely! Your current code is hitting a classic Google Apps Script performance bottleneck—every time you call getRange() inside that loop, you’re making a slow round-trip to Google’s servers. For large sheets, this gets really sluggish. Let’s fix this with a far more efficient approach.

Key Optimization: Batch Read All Data First #

Instead of fetching cells one by one, we’ll pull the entire dataset into memory in a single operation. This cuts down on server calls drastically, which is the biggest win for performance here.

Here’s the refactored code:

// Fetch all sheet data in one go (way faster than repeated getRange calls)
var data = sheetUserCalls.getDataRange().getValues();
var flag = 0;

// Loop through the in-memory array (no more slow server trips!)
for (var i = 0; i < data.length; i++) {
  // Note: Array indices start at 0 — G column is index 6, I column is index 8
  var rowUserID = data[i][6];
  var rowCallID = data[i][8];
  
  if (rowCallID === getCallID && rowUserID === userID) {
    sheet.getRange(cellToEdit).setValue("closed");
    sendText(userID, `${getCallID} was closed`);
    flag = 1;
    break; // Exit loop early once we find the match
  }
}

if (flag === 0) {
  sendText(userID, 'error: you cannot close another person\'s call');
} else {
  // Adjusted the extra else from your original code to avoid syntax issues
  // Tweak this section if you need to handle additional request validation
}

Why This Works Better: #

• Minimize Server Interactions: getDataRange().getValues() grabs all sheet data in one request, instead of hundreds/thousands of individual getRange() calls.
• Faster Access: Reading from an in-memory array is orders of magnitude faster than fetching cells one at a time.
• Cleaner Logic: The loop is easier to read once you’re working with array indices instead of cell references.

If your sheet has 10k+ rows, you could even use filter() to find the matching row in a more concise way, but the batch read approach already gives you 90% of the performance gain.

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