如何优化Google Apps Script提取指定表格数据的执行效率？

Hey there! Let's fix that slow script of yours—repeated calls to Google Sheets' services are the main culprit here. Each time you call getRange() or setValue(), it's a round-trip to Google's servers, which adds up fast when you're looping through lots of rows. Here's a much more efficient approach:

Key Optimizations We'll Make #

• Fetch all CSV data in one go: Instead of pulling each column separately, grab the entire data range once. This cuts down on dozens of unnecessary service calls.
• Collect results in an array first: Build a 2D array of all rows you want to copy, then write everything to the target sheet in a single operation. This replaces hundreds (or thousands) of individual setValue() calls with just one.
• Avoid repeated getLastRow() calls: Calculating the target sheet's last row once at the start (and updating it based on our array length later) saves more redundant server trips.

Optimized Code #

function buenosDias() {
  const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1ur1Y7KoPONxPiFBMdGR4UfJW4GfyLy4MDxTEk30mg8w/edit#gid=0");
  const buenosDiasSheet = ss.getSheetByName("Buenos días");
  const csvSheet = ss.getSheetByName("CSV");
  
  // Fetch ALL CSV data in one single call (way more efficient than column-by-column)
  const csvData = csvSheet.getDataRange().getValues();
  
  // Initialize an array to hold rows we want to copy
  const rowsToCopy = [];
  
  // Loop through CSV data (skip header row with i=1)
  for (let i = 1; i < csvData.length; i++) {
    const row = csvData[i];
    // Check if column C (index 2, since arrays are 0-based) has "Buenos días"
    if (row[2] === "Buenos días") {
      // Build the row we want to add to the target sheet
      const formattedRow = [
        row[0], // Column A from CSV
        row[5], // Column F from CSV
        row[7], // Column H from CSV
        row[7]/row[5], // H/F calculation
        row[9], // Column J from CSV
        row[9]/row[5]  // J/F calculation
      ];
      rowsToCopy.push(formattedRow);
    }
  }
  
  // If we have rows to copy, write them all at once
  if (rowsToCopy.length > 0) {
    const targetStartRow = buenosDiasSheet.getLastRow() + 1;
    // Write the entire array to the target sheet in one call
    const targetRange = buenosDiasSheet.getRange(targetStartRow, 1, rowsToCopy.length, rowsToCopy[0].length);
    targetRange.setValues(rowsToCopy);
    
    // Apply formatting in bulk (still fewer calls than formatting each cell individually)
    targetRange.setHorizontalAlignment("center");
    // Format specific columns: column 2 (F) and 3 (H) as numbers, 4 and 6 as percentages
    buenosDiasSheet.getRange(targetStartRow, 2, rowsToCopy.length).setNumberFormat("0");
    buenosDiasSheet.getRange(targetStartRow, 3, rowsToCopy.length).setNumberFormat("0");
    buenosDiasSheet.getRange(targetStartRow, 4, rowsToCopy.length).setNumberFormat("00.00%");
    buenosDiasSheet.getRange(targetStartRow, 6, rowsToCopy.length).setNumberFormat("00.00%");
  }
}

Why This Works So Much Faster #

1. Minimizes Service Calls: The original code made ~13 calls to get column data, plus 6 setValue() calls per matching row. The optimized version makes just a handful of calls total, no matter how many rows you process.
2. Bulk Operations: Writing an array with setValues() is exponentially faster than writing cell-by-cell. Same goes for applying formatting in bulk instead of per cell.
3. Cleaner Logic: By working with the full CSV data array, we avoid messy variable names for each column and make the code easier to read and maintain.

Give this a try—you should see a massive speed improvement, even with large datasets!

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