Google Apps Script脚本优化：批量导出缺失的表格为PDF

Hey there! I see you're working on converting Google Sheets to PDFs in batches and running into timeout issues plus inefficient looping. Let's fix this up with a script that's faster, skips already converted files, and can pick up where it left off if it gets interrupted.

First, let's break down the issues in your original code: #

• Nested loop inefficiency: You're looping through every PDF file for each Sheet file, which creates O(n*m) operations—way too slow for 100+ files.
• Incorrect skip logic: Your else branch creates a PDF every time a single PDF name doesn't match, meaning you'd end up creating duplicate PDFs even if the file already exists.
• No resumption: If the script times out halfway, you have to start over from scratch.

Here's the optimized script: #

function convertSheetsToPdf() {
  // Replace these with your actual folder IDs
  const DASH_FOLDER_ID = "YOUR_DASH_FOLDER_ID";
  const PDF_FOLDER_ID = "YOUR_PDF_FOLDER_ID";
  
  const dashFolder = DriveApp.getFolderById(DASH_FOLDER_ID);
  const pdfFolder = DriveApp.getFolderById(PDF_FOLDER_ID);
  
  // 1. Create a lookup object for existing PDF filenames (O(1) lookup speed)
  const existingPdfs = {};
  const pdfFiles = pdfFolder.getFiles();
  while (pdfFiles.hasNext()) {
    const pdfFile = pdfFiles.next();
    existingPdfs[pdfFile.getName()] = true;
  }
  
  // 2. Get already processed Sheet IDs from PropertiesService (for resumption)
  const scriptProps = PropertiesService.getScriptProperties();
  const processedSheetIds = new Set(scriptProps.getProperty("processedSheetIds")?.split(",") || []);
  
  // 3. Process each Sheet file
  const sheetFiles = dashFolder.getFilesByType(MimeType.GOOGLE_SHEETS); // Only target Sheets files
  let processedCount = 0;
  
  while (sheetFiles.hasNext()) {
    const sheetFile = sheetFiles.next();
    const sheetId = sheetFile.getId();
    
    // Skip if already processed or PDF exists
    if (processedSheetIds.has(sheetId)) {
      Logger.log(`Skipping already processed sheet: ${sheetFile.getName()}`);
      continue;
    }
    
    const targetPdfName = `${sheetFile.getName()}.pdf`;
    if (existingPdfs[targetPdfName]) {
      Logger.log(`Skipping, PDF already exists: ${targetPdfName}`);
      // Mark as processed so we don't check again next time
      processedSheetIds.add(sheetId);
      continue;
    }
    
    // Convert Sheet to PDF
    try {
      const pdfBlob = sheetFile.getAs(MimeType.PDF);
      pdfBlob.setName(targetPdfName);
      pdfFolder.createFile(pdfBlob);
      Logger.log(`Successfully created PDF: ${targetPdfName}`);
      
      // Mark as processed
      processedSheetIds.add(sheetId);
      processedCount++;
      
      // Optional: Add a small delay to avoid hitting rate limits (adjust as needed)
      Utilities.sleep(100);
    } catch (error) {
      Logger.log(`Failed to convert ${sheetFile.getName()}: ${error.message}`);
    }
  }
  
  // Save processed IDs back to PropertiesService for resumption
  scriptProps.setProperty("processedSheetIds", Array.from(processedSheetIds).join(","));
  Logger.log(`Batch complete! Processed ${processedCount} new files.`);
}

// Optional: Reset processed IDs if you need to start over
function resetProcessedIds() {
  const scriptProps = PropertiesService.getScriptProperties();
  scriptProps.deleteProperty("processedSheetIds");
  Logger.log("Processed IDs reset.");
}

Key improvements: #

• Fast filename lookup: Using an object (existingPdfs) to store PDF names means checking if a PDF exists takes just 1 operation instead of looping through all PDFs every time.
• Resumable processing: We use PropertiesService to track which Sheet files have been processed. If the script times out, running it again will skip all files already handled.
• Targeted file selection: Using getFilesByType(MimeType.GOOGLE_SHEETS) ensures we only process Sheet files, avoiding any other file types in the folder.
• Error handling: Wraps the conversion in a try/catch block so one failed file doesn't break the entire batch.
• Clean skip logic: We skip files that are already processed OR have an existing PDF, and mark them as processed to avoid re-checking later.

How to use: #

1. Replace YOUR_DASH_FOLDER_ID and YOUR_PDF_FOLDER_ID with your actual folder IDs.
2. Run convertSheetsToPdf()—it will process all unhandled Sheets and skip existing PDFs.
3. If the script times out, just run it again—it will pick up where it left off.
4. If you need to start over from scratch, run resetProcessedIds().

This should handle your 100+ files without hitting the 6-minute timeout, and it won't waste time re-converting files you already have.

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