如何修改Google Apps Script，在Google Sheets中显示文件所在文件夹？

Hey Alex, I’ve run into this exact issue before—let’s get that folder column working properly for you!

The Problem Breakdown #

First, why didn’t getFolder() work? Google Drive lets files live in multiple folders (plus the root), so there’s no single "parent folder" method. Your switch to getParents() was the right call, but the mistake was writing the raw FolderIterator object to the sheet instead of extracting the actual folder names from it.

The Fix: Extract Folder Names from the Iterator #

Here’s a revised version of your script that correctly fetches and displays the parent folder(s) for each file. I’ll include a helper function to keep things clean:

function listDriveFilesToSheet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Clear existing data (keep headers if needed, or rebuild them)
  sheet.clearContents();
  
  // Set up headers with the new "所在文件夹" column
  const headers = ["文件名", "文件类型", "文件URL", "所在文件夹"];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  // Replace this with your actual search query (adjust mimeType or add other filters)
  const files = DriveApp.searchFiles("mimeType contains 'google-apps'");
  
  let currentRow = 2;
  while (files.hasNext()) {
    const file = files.next();
    
    // Get basic file details
    const fileName = file.getName();
    const fileType = file.getMimeType();
    const fileUrl = file.getUrl();
    
    // Get parent folder names using our helper function
    const folderNames = getParentFolderNames(file);

    // Write all data to the sheet
    sheet.getRange(currentRow, 1).setValue(fileName);
    sheet.getRange(currentRow, 2).setValue(fileType);
    sheet.getRange(currentRow, 3).setValue(fileUrl);
    sheet.getRange(currentRow, 4).setValue(folderNames);
    
    currentRow++;
  }
}

// Helper function to fetch and format parent folder names
function getParentFolderNames(file) {
  const parentFolders = file.getParents();
  const folderNames = [];
  
  // Iterate through all parent folders (since files can be in multiple folders)
  while (parentFolders.hasNext()) {
    const folder = parentFolders.next();
    folderNames.push(folder.getName());
  }
  
  // Handle files in the root directory (no parent folders)
  return folderNames.length > 0 ? folderNames.join(", ") : "根目录";
}

Key Notes: #

• FolderIterator Handling: file.getParents() returns an iterator, not a list of folders. We have to loop through it with hasNext() and next() to get each actual Folder object, then extract its name.
• Multiple Folders: Drive allows files to exist in multiple folders, so we join all folder names with a comma (you can change this to a newline "\n" if you want each name on its own line in the cell).
• Root Directory Fallback: If a file is only in the Drive root, we return "根目录" instead of leaving the cell empty.

Quick Adjustments You Might Want: #

• Modify the DriveApp.searchFiles() query to match your specific search needs (e.g., search by file name, date, or specific mimeType).
• Adjust the header text or separator to fit your workflow.

That should fix your folder display issue—give it a test run and let me know if you need any tweaks!

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