Retrieving folder name for Google Docs using document identifier

Other Questions Google drive
1

I’m stuck on a project and need some guidance. Here’s what I’m trying to do:

  • Find the name of the folder containing a Google Doc
  • Use either the Doc’s ID or URL to locate it
  • Put the folder name in a column labeled ‘Folder’
  • Do this in a specific Google Sheet

I want to repeat this for every row until I’ve gone through all the IDs. I’m not sure where to start though. Any tips or pointers would be really helpful!

Here’s a basic idea of what I’m aiming for:

function getFolderNames(sheetId) {
  const sheet = SpreadsheetApp.openById(sheetId).getActiveSheet();
  const data = sheet.getDataRange().getValues();
  
  for (let i = 1; i < data.length; i++) {
    const docId = data[i][0];  // Assuming Doc ID is in first column
    const folder = DriveApp.getFileById(docId).getParents().next();
    sheet.getRange(i + 1, 2).setValue(folder.getName());  // Write to second column
  }
}

This is just a rough sketch. I’m not sure if it’s the right approach. Any advice?

2

Your approach is solid, but there are a few optimizations we can make to improve efficiency and handle edge cases. Here’s a refined version of the script:

function getFolderNames(sheetId) {
  const sheet = SpreadsheetApp.openById(sheetId).getActiveSheet();
  const [headers, ...rows] = sheet.getDataRange().getValues();
  
  rows.forEach((row, index) => {
    const docId = row[0];
    try {
      const file = DriveApp.getFileById(docId);
      const folderNames = Array.from(file.getParents(), folder => folder.getName());
      sheet.getRange(index + 2, 2).setValue(folderNames.join(', ') || 'Root');
    } catch (error) {
      sheet.getRange(index + 2, 2).setValue('Error: ' + error.message);
    }
  });
}

This version uses destructuring and forEach for cleaner code. It also handles potential errors, such as invalid document IDs, and marks files in the root of Drive. Remember to add error handling for API quota limits if you’re processing many documents, and consider batching updates to reduce API calls and improve performance.

3

hey, i think i can help u out. ur code looks good, but maybe try this:

function getFolderNames(sheetId) {
  var sheet = SpreadsheetApp.openById(sheetId).getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  for (var i = 1; i < data.length; i++) {
    var docId = data[i][0];
    var file = DriveApp.getFileById(docId);
    var folders = file.getParents();
    var folderName = folders.hasNext() ? folders.next().getName() : 'No folder';
    sheet.getRange(i + 1, 2).setValue(folderName);
  }
}

this should work for what ur trying to do. good luck!

4

I’ve worked on a similar project before, and I can share some insights that might help you out.

First off, your approach is on the right track. However, you might want to consider handling multiple parent folders, as Google Docs can exist in more than one folder at a time.

Here’s a modified version of your function that addresses this:

function getFolderNames(sheetId) {
  const sheet = SpreadsheetApp.openById(sheetId).getActiveSheet();
  const data = sheet.getDataRange().getValues();
  
  for (let i = 1; i < data.length; i++) {
    const docId = data[i][0];
    const file = DriveApp.getFileById(docId);
    const folders = file.getParents();
    let folderNames = [];
    
    while (folders.hasNext()) {
      folderNames.push(folders.next().getName());
    }
    
    sheet.getRange(i + 1, 2).setValue(folderNames.join(', '));
  }
}

This function will list all parent folders, separated by commas, in the ‘Folder’ column. It’s more robust and handles edge cases better.

One thing to keep in mind: if you’re dealing with a large number of documents, you might hit Google’s execution time limit. In that case, you’d need to implement a time-based trigger to process the documents in batches.

Hope this helps with your project!