使用Google Apps Script转换PDF至Docs后，Sheet无法正确获取对应PDF链接

Hey there! Let's break down why you're seeing duplicate PDF IDs in your sheet and fix things to get full, direct PDF links instead of just IDs.

What's Going Wrong? #

1. Variable Scope Mix-Up: The pdf variable you're using in the second loop (writing to the sheet) is set in the first loop that converts PDFs to Docs. Since it gets overwritten every time the first loop runs, by the time you reach the second loop, it only holds the ID of the last PDF you processed—so every row ends up with that same value.
2. No Link Between Docs and PDFs: You aren't storing any connection between each generated Google Doc and its original PDF, so when you go to process the Docs later, you can't pull the correct link for each one.
3. You Don't Need to Build the Link: Instead of grabbing the PDF ID and using a formula to make the link, you can just get the full direct URL straight from the PDF file itself.

Fixed Script #

Here's the revised code that addresses all these issues:

function extractTextOnOpen() {
  var folderName = "OCR TESTS";
  var sheetId = "SHEET'S ID HERE";
  
  // Define Project folder
  var folder = DriveApp.getFoldersByName(folderName).next();
  var folderId = folder.getId();
  
  // Track PDF links by their corresponding Doc's name
  var pdfLinksByDocName = {};
  var pdfFiles = folder.getFilesByType("application/pdf");
  
  while (pdfFiles.hasNext()) {
    var pdfFile = pdfFiles.next();
    var pdfName = pdfFile.getName();
    var docName = pdfName.split("\.")[0];
    // Get the full direct link to the PDF
    var pdfFullUrl = pdfFile.getUrl();
    
    // Store the link mapped to the Doc name we'll use later
    pdfLinksByDocName[docName] = pdfFullUrl;
    
    // Create the Google Doc DIRECTLY in the target folder (no need to move it later)
    var docMetadata = {
      title: docName,
      parents: [{id: folderId}]
    };
    Drive.Files.insert(docMetadata, pdfFile, {ocr: true});
  }
  
  // Set up the spreadsheet
  var ss = SpreadsheetApp.openById(sheetId);
  var sheet = ss.getSheetByName('Sheet1');
  sheet.getRange("A1:C").clear();
  var headers = [["File Name", "Test Paper Scanned", "PDF Link"]];
  sheet.getRange("A1:C1").setValues(headers);
  
  // Process Docs and match with their PDF links
  var output = [];
  var docs = folder.getFilesByType("application/vnd.google-apps.document");
  
  while (docs.hasNext()) {
    var docFile = docs.next();
    var doc = DocumentApp.openById(docFile.getId());
    var docName = doc.getName();
    var bodyText = doc.getBody().getText();
    // Get the matching PDF link (fallback if no match exists)
    var pdfLink = pdfLinksByDocName[docName] || "No matching PDF found";
    
    output.push([docName, bodyText, pdfLink]);
  }
  
  // Write all rows to the sheet at once (more efficient than row-by-row writes)
  if (output.length > 0) {
    sheet.getRange(2, 1, output.length, 3).setValues(output);
  }
}

Key Changes Explained #

• Track Links with a Mapping Object: The pdfLinksByDocName object stores each PDF's full URL linked to the name of the Google Doc we'll generate from it. This creates the critical connection between each Doc and its source PDF.
• Create Docs Directly in Target Folder: We added the parents property to the Doc metadata, so the new Google Doc is created right in your target folder instead of the root. This removes the need to move files later and avoids potential errors.
• Full PDF URL Instead of ID: Using pdfFile.getUrl() gives you the direct, usable link to the PDF—no need to build it with a formula later.
• Batch Write to Sheet: We collect all rows first and write them to the sheet in one go, which is much more efficient (Google Apps Script has quotas for sheet operations, so this is a best practice).
• Fallback for Missing Matches: The || "No matching PDF found" ensures you don't get blank cells if a Doc in the folder doesn't have a corresponding PDF (handy for manual additions to the folder).

Quick Tip #

If you have multiple PDFs with the same name (before the .pdf extension), this script will overwrite the link in the mapping object. To handle duplicates, you could modify the Doc name to include a unique identifier (like the PDF's ID) during conversion, or use the Doc's ID as the mapping key instead of the name.

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