Hey there! Let's fix that slow script of yours—those repeated calls to Google Sheets' services inside the loop are killing your performance. Here's an optimized version that'll run way faster, plus a breakdown of the key improvements:

Optimized Code #

function copyCompletedOrders() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = spreadsheet.getSheetByName('book');
  const destSheet = spreadsheet.getSheetByName('Completed_Orders');

  // Clear only used rows in destination sheet (more efficient than clearing a huge range)
  const destLastRow = destSheet.getLastRow();
  if (destLastRow >= 2) {
    destSheet.getRange(2, 1, destLastRow - 1, 24).clear();
  }

  // Fetch ALL source data in one single server call (this is the biggest speed boost!)
  const sourceLastRow = sourceSheet.getLastRow();
  const sourceData = sourceSheet.getRange(3, 1, sourceLastRow - 2, 24).getValues();

  // Filter rows where Column Q (17th column, 0-indexed as 16) is "Completed"
  const completedRows = sourceData.filter(row => row[16] === "Completed");

  // Write all filtered rows to destination in one go
  if (completedRows.length > 0) {
    destSheet.getRange(2, 1, completedRows.length, 24).setValues(completedRows);
  }
}

Key Improvements Explained #

• Batch all data operations: The original script made a separate getRange().getValues() call for every matching row—each of these is a slow round-trip to Google's servers. We instead grab the entire source dataset in one call, filter it locally, then write all results in a single setValues() call. This reduces server interactions from potentially hundreds/thousands to just 2-3.
• Targeted clearing: Instead of clearing the entire A2:X range (which includes empty rows at the bottom), we calculate the actual last used row in the destination sheet and only clear those rows. This cuts down on unnecessary work.
• Simplified filtering: Using JavaScript's filter() method is cleaner and more efficient than a manual for loop with push.apply(). It's also easier to read and maintain.
• Eliminated redundant code: The manual last-row calculation in your original script is unnecessary—we already know the length of our filtered completedRows array, so we can use that directly when writing to the destination.

Extra Speed Tips #

• Make sure you're using the V8 runtime (it's enabled by default in new scripts, but check under Run > Enable new Apps Script runtime (V8) if you're unsure—it's significantly faster than the old Rhino engine).
• If you don't need all 24 columns, adjust the range to fetch only the columns you need—less data to process means faster execution.
• For extremely large sheets (10,000+ rows), you might want to add SpreadsheetApp.flush() after clearing the destination sheet to ensure the operation completes before writing new data, but in most cases, the batch operations will handle this smoothly.

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