Google Apps Script删除过期行超时，求性能优化方案

Hey there! Let's fix that timeout problem you're hitting with your Google Apps Script. The root cause here is that your original code makes way too many individual calls to the spreadsheet API—things like getRange() and deleteRow() are slow when dealing with large datasets, and those add up fast against the 6-minute limit. Here's how to optimize this drastically:

• Batch Data Reads/Writes: Pull all sheet data into memory at once instead of fetching cells one by one. This cuts down API calls from hundreds/thousands to just a few per sheet.
• Avoid Incremental Row Deletions: Deleting rows individually forces the spreadsheet to reindex every time, which is a huge performance hit. Instead, collect rows to keep, clear the sheet, and rewrite only those rows.
• Faster Sheet Exclusion: Use a Set for excluded sheet names instead of multiple || checks—lookups in a Set are way quicker.
• Normalized Date Comparisons: Strip time components from dates to avoid unexpected comparisons (e.g., a 10 AM date today vs. a 5 PM date yesterday).

function approveRequests() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const pastSheet = ss.getSheetByName("Left");
  const excludedSheets = new Set([
    "ABCDEF", "ABCDEF", "ABCDEF", "ABCDEF", 
    "ABCDEF", "ABCDEF", "ABCDEF", "ABCDEF", "ABCDEF"
  ]);
  const today = new Date();
  today.setHours(0, 0, 0, 0); // Normalize to midnight for consistent date-only comparison

  // Filter out excluded sheets upfront
  const targetSheets = ss.getSheets().filter(sheet => !excludedSheets.has(sheet.getName()));

  targetSheets.forEach(sheet => {
    const allData = sheet.getDataRange().getValues();
    if (allData.length === 0) return; // Skip empty sheets to save time

    const rowsToKeep = [];
    const rowsToMove = [];

    // Process all rows in memory
    allData.forEach(row => {
      const dateCell = row[14]; // Column O is index 14 (arrays are 0-based)
      if (isValidDate(dateCell)) {
        const testDate = new Date(dateCell);
        testDate.setHours(0, 0, 0, 0); // Normalize the cell's date
        
        if (testDate < today) {
          rowsToMove.push(row);
        } else {
          rowsToKeep.push(row);
        }
      } else {
        rowsToKeep.push(row); // Keep rows with invalid dates
      }
    });

    // Batch move expired rows to "Left" sheet
    if (rowsToMove.length > 0) {
      const nextRow = pastSheet.getLastRow() + 1;
      pastSheet.getRange(nextRow, 1, rowsToMove.length, rowsToMove[0].length).setValues(rowsToMove);
    }

    // Update current sheet with only rows we want to keep
    sheet.clearContents();
    if (rowsToKeep.length > 0) {
      sheet.getRange(1, 1, rowsToKeep.length, rowsToKeep[0].length).setValues(rowsToKeep);
    }
  });
}

// Improved date validation (getTime() is more reliable than getDate())
function isValidDate(value) {
  const dateWrapper = new Date(value);
  return !isNaN(dateWrapper.getTime());
}

• Batch Processing: getDataRange().getValues() pulls all sheet data into a 2D array, so we process everything in memory instead of making repeated API calls.
• Set for Exclusions: Checking if a sheet is excluded is O(1) with a Set, compared to O(n) with multiple || checks.
• Normalized Dates: By setting both dates to midnight, we avoid edge cases where time components could make a "yesterday" date appear newer than today.
• Batch Writes/Deletes: Instead of deleting rows one by one, we clear the sheet and rewrite only the rows to keep—this is just 2 API calls per sheet instead of one per row.

• Handle Headers: If your sheets have header rows, adjust the loop to start at index 1 (e.g., allData.slice(1).forEach(...)) and make sure to add the header back to rowsToKeep.
• Chunk Processing: If you have dozens of huge sheets, split the work into chunks using time-driven triggers. For example, process 2-3 sheets per run, and let the trigger run again later to finish the rest.
• Avoid Unnecessary Calls: The script skips empty sheets to avoid wasting time on unnecessary processing.

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