使用Apps Script复制大尺寸表格至另一表格时执行失败的技术求助

Ah, I’ve run into this exact problem with big spreadsheets in Apps Script before! When you try to copy 8000 rows × 40 columns (320,000 cells) all at once, you’re hitting memory limits or execution timeouts—Google Apps Script has strict constraints on both, and full-data-range operations can easily exceed them. Let’s break down the solutions.

Why Your Original Code Fails #

Your original script uses getDataRange() to grab all data in one go. For small datasets this works, but for large ones:

• The script loads every cell value into memory at once, which can trigger out-of-memory errors.
• The total execution time might surpass the 6-minute limit for Apps Script functions.

Solution 1: Use copyTo() (Most Efficient) #

If you don’t need to merge data into an existing target sheet (just replace or add the full sheet), use Google’s built-in copyTo() method. This is way faster because it’s handled server-side, bypassing script-level memory limits.

function doACopyEfficiently() {
  const sourceSSId = 'SOURCE SHEET ID'; // Replace with your actual ID
  const targetSSId = 'TARGET SHEET ID'; // Replace with your actual ID
  
  const sourceSS = SpreadsheetApp.openById(sourceSSId);
  const targetSS = SpreadsheetApp.openById(targetSSId);
  
  const sourceSheet = sourceSS.getSheetByName('Links Added');
  
  // Option 1: Add the copied sheet to the target spreadsheet
  sourceSheet.copyTo(targetSS).setName('Links Added');
  
  // Option 2: Replace an existing "Links Added" sheet (uncomment if needed)
  // const existingTargetSheet = targetSS.getSheetByName('Links Added');
  // if (existingTargetSheet) {
  //   targetSS.deleteSheet(existingTargetSheet);
  //   sourceSheet.copyTo(targetSS).setName('Links Added');
  // }
}

Solution 2: Chunked Copy (For Existing Target Sheets) #

If you need to write data into an already-existing target sheet (e.g., to preserve other content or custom formatting), split the copy into smaller chunks. This reduces memory usage and keeps execution time within limits.

function doChunkedCopy() {
  const sourceSSId = 'SOURCE SHEET ID';
  const targetSSId = 'TARGET SHEET ID';
  
  const sourceSheet = SpreadsheetApp.openById(sourceSSId).getSheetByName('Links Added');
  const targetSheet = SpreadsheetApp.openById(targetSSId).getSheetByName('Links Added');
  
  const totalRows = sourceSheet.getLastRow();
  const totalCols = sourceSheet.getLastColumn();
  const chunkSize = 1000; // Adjust this (500-2000 rows work well for most cases)
  
  // Clear existing content in target (optional—remove if you want to append)
  targetSheet.clearContents();
  
  for (let startRow = 1; startRow <= totalRows; startRow += chunkSize) {
    const endRow = Math.min(startRow + chunkSize - 1, totalRows);
    const rowCount = endRow - startRow + 1;
    
    // Read chunk from source
    const dataChunk = sourceSheet.getRange(startRow, 1, rowCount, totalCols).getValues();
    // Write chunk to target
    targetSheet.getRange(startRow, 1, dataChunk.length, dataChunk[0].length).setValues(dataChunk);
    
    // Optional: Force save to avoid data loss if the script is interrupted
    SpreadsheetApp.flush();
    
    // Optional: Log progress (check in View > Logs)
    console.log(`Processed rows ${startRow} to ${endRow}`);
  }
}

Pro Tips for Success #

• Adjust chunk size: If you still get timeouts, lower the chunk size (e.g., to 500 rows). If it’s too slow, increase it (up to 2000 is usually safe).
• Minimize API calls: Every getRange() or setValues() is an API call—chunking reduces the number of calls compared to row-by-row processing.
• Skip formatting if possible: Use clearContents() instead of clear() to avoid copying unnecessary formatting data, which saves time and memory.

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