Question 207414 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Trying to get the root folder in Google drive using file IDs

I have built an apps script add-on that looks at a column in Google sheets with Doc IDs in google drive and determines the folder path of the file using those doc IDs.

The only issue is it does not give me the root folder. So if a file is located in the following folder path:

"root folder / sub-folder / sub-folder / sub-folder"

I get the following path with my code:

"Drive / sub-folder / sub-folder / sub-folder"

It says "Drive" for every file located in a shared drive and not the name of the actual root folder.

Here is the code i have currently:

function getFolderPath(id) {

  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();
  var docIdCol = 3; // assuming doc_id is in column C
  var folderPathCol = docIdCol + 1; // insert new column beside column C
  var folderPath;
  
  // insert new column beside column C
  sheet.insertColumnBefore(folderPathCol);
  
  // set column header
  sheet.getRange(1, folderPathCol).setValue("Folder Path");
  
  // iterate through each row and get folder path for each file
  for (var i = 1; i < values.length; i++) {
    var docId = values[i][docIdCol - 1];
    try {
      var file = DriveApp.getFileById(docId);
      var folders = file.getParents();
      
      while (folders.hasNext()) {
        var folder = folders.next();
        folderPath = folder.getName() + "/" + folderPath;
        folders = folder.getParents();
        console.log(folders)
        
      }
      sheet.getRange(i + 1, folderPathCol).setValue(folderPath);
      folderPath = "";
    } catch (e) {
      sheet.getRange(i + 1, folderPathCol).setValue("My Drive");
    }
    
  }

}
Tags:

2

Answers


  1. 0 
    function getPathFromId(id) {
  try {
    var file = DriveApp.getFileById(id)
    var pA = [];
    pA.push(file.getName());
    var folder = file.getParents();
    while (folder.hasNext()) {
      var f = folder.next();
      pA.push(f.getName());
      folder = f.getParents()
    }
    var r = pA.reverse().join(' / ');
  }
  catch (e) {
    return e;
  }
  return r;
}

    Try this one also:

    function getPathAllDrivesFromId(fileid) {
  var ids = [{id:fileid,name:DriveApp.getFileById(fileid).getName()}];
  let r;
  do {
    r = Drive.Files.get(fileid,{supportsAllDrives:true,supportsTeamDrives:true});
    if(r.parents.length > 0) {
      //Logger.log(JSON.stringify(r.parents[0]));
      ids.push({id:r.parents[0].id,name:DriveApp.getFolderById(r.parents[0].id).getName()});
      fileid = r.parents[0].id
    }
  }while (r.parents.length > 0);
  if(ids[ids.length - 1].name == "Drive") {
    ids[ids.length - 1].name = Drive.Drives.get(ids[ids.length - 1].id).name;
  }
  //Logger.log(JSON.stringify(ids));
  let path = ids.map(obj => obj.name).flat().reverse().join(' / ')
  //Logger.log(path);
  return path;
}
    Login or Signup to reply.
  2. 0

    From your following reply,

    If I test it out by actually going to the company’s shared drive and look at the location of the file in drive, the root folder’s name is different. It could be anything, it’s just NOT "Drive"

    From your reply, I noticed that I have misread your question. I noticed that you have been using the shared Drive. In this case, it seems that in the current stage, the drive name and the drive ID cannot be directly retrieved with the Drive service (DriveApp).

    I guessed that the reason for your current issue is due to that in the current stage, the drive name is required to be retrieved with the method of "Method: drives.get" of Drive API. When this is reflected in your showing script, how about the following modification?

    Modified script:

    Before you test this script, please enable Drive API at Advanced Google services.

    function getFolderPath() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();
  var docIdCol = 3;
  var folderPathCol = docIdCol + 1;
  var folderPath;
  sheet.insertColumnBefore(folderPathCol);
  sheet.getRange(1, folderPathCol).setValue("Folder Path");
  for (var i = 1; i < values.length; i++) {
    var docId = values[i][docIdCol - 1];
    try {
      var file = DriveApp.getFileById(docId);
      var folders = file.getParents();
      while (folders.hasNext()) {
        var folder = folders.next();

        // --- I modified the below script.
        var { id, driveId } = Drive.Files.get(folder.getId(), { supportsAllDrives: true });
        var folderPath = ((driveId && driveId == id) ? Drive.Drives.get(id).name : folder.getName()) + "/" + folderPath;
        // ---

        folders = folder.getParents();
        console.log(folders)
      }
      sheet.getRange(i + 1, folderPathCol).setValue(folderPath);
      folderPath = "";
    } catch (e) {
      sheet.getRange(i + 1, folderPathCol).setValue("My Drive");
    }
  }
}
    • By this modification, when a drive ID is detected, the drive name is retrieved and used as the path name.

    References:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search