Google Apps Script – How to Find the Folder ID of a Non-Unique Folder Using File Path Names

Google Apps Script – DriveApp

sighisoara Google Apps Script Tutorial
This tutorial comes from a comfy spot in the centre of Sighisoara.

Well, that title is such a mouthful. So I think it needs some explaining…

I’ve recently come across a task that requires me to access a particular folder in a directory based on the information in a Google Sheet. This means:

  1. I don’t have the folder or the sub-folder ID.
  2. I can get the directory path information from information supplied in a Google Sheet.

Basically what I needed to do was create a Report maker and store it in the following directory path:

My Drive(root) >> Year >>  Quarter >> Unit+ "Report"

For example:

MyDrive >> 2018 >> Q4  >> Unit 4 Report

Boring!!!! Take me to the code!!!

The Problem

Unfortunately, I could not simply change the last folder name from say, Unit 4 Report to Q4 Unit 4 Report 2018 so it is easily searchable and unique. The other problem is that there are other Unit 4 Reports in other years and quarters so I did not want to accidentally call them instead of the exact one I wanted.

So, what to do..?

The Solution

Fortunately, the directories of the drive were standard. For example, all year folders had four numbers like 2016, 2017, 2018. Also the year, quarter and unit were available pieces of information in the Google Sheet I was to create a report generator from.

With this in mind, I could create an array of all the subfolders from the root, My Drive, and search each one individually for the correct folder. Then find that folder ID and use it to search it’s child folders.

The Example

Let’s look at an example, say I needed to generate a report for 2018, Quarter 2, Unit 3. First, I needed to search the root, My Drive, directory for the 2018 folder. Then in the 2018 folder, I want to search for the Q2 folder. Finally, in the Q2folder I want to search for the Unit 3 Reportfolder.

Google Drive Directory

All clear?

The Code

findDirectory(list)

The findDirectory(list) takes one argument list which is an array of folder names in order from the root, My Drive.  We create this list in the start() function down in line 51 as the variable dirList. This dirList will be iterated through from left to right. with the zeroeth(e.g. “2018”) folder name starting at the root directory and then moving down each subfolder. You can have as many folder names and subdirectories as you want in the array.

Next, we set up the folderID variable which contains the id and the error objects (Line 3). They are preset to “None”.

Now it’s time to iterate through our list of folder names (Line 6).

i indicates which item position in the array, list, we are looking at starting at zero. We first ask, if (line 8) i is greater than zero then do something otherwise (else – line 26) do something different with the zeroeth item.

The First List Item

Let’s take a look at what the else statement does.

First, we create the root variable (line 28). Here we call Google Apps Script’s DriveApp Class. Then we get the root, My Drive directory with getRootFolder(). Using the root variable we get a generator of folders in that directory with getFolders(). If you have iterated through the drive before you might be wondering why we don’t just do something like:

var folders = DriveApp.getFolders()

This would generate a list of folders in not only the root but all of the subfolders that are children of the root, My Drive. Not only does this create a time-costly search but you are in danger of searching for the wrong folder.

The final variable is count. We’ll use count to count how many times the folder will appear with the chosen name  in the selected directory. If a folder with the selected name ( in our case “2018“) is not present the count will be zero(0) and we will throw an error that you could use in, say, a dialog popup to warn your users. We’ll then set the folderId.id to “None” and return the function (Line 39-45).

However, if the count is 1 then we will start a while loop that says, so long as our folders generator has the next (hasNext()) folder in the list do something with it (line 32). We then take each individual folder with next() and get it’s name and check it against the item in the list(line 34). If a folder with that name in the root exists, then we get the folder’s ID (line 35) and add to the count.

The Following List Items

Once we have the id for the first folder found in the root My Drive, we can use that id to Search the folder. In other words, now that I found ‘2018‘ in ‘My Drive‘ and can now search for ‘Q4‘ in ‘2018‘.

Line 9 sets the parent folder we are going to look for the next folder name in. To do this, we get the folder id of our newly stored folderID.id. We then use that to get our folders, line 10. Finally, we add a count to help us determine if a file by that name exists.

We then iterate through the generator, folders and for each folder we check if it matches the folder name on our list. If it does, we get it’s folderID and we add it to our folderID.id object. We then add 1 to the count(lines 15-17).

Again, if a folder with that name doesn’t exist, we store  an error in the folderID.error object and return the function (lines 21-24).

Returning findDirectory Information to the start() Function

To use the id and error data we return from findDirectory(), it’s best for us to first check to ensure there are no errors – like a faulty directory path or missing folder locations.

The variable dir is used to take the values from the findDirectory() function (Line 52). On line 55, the dir.id is checked. If it is “None” then we couldn’t find the folder in the list. For the purposes of this tutorial, we will just log the error if this is the case.

Alternatively, if all folders exists in the list, then we should have the id for the last folder in the directory(line 57). You can now use this folder id in your code. In my case, I use this id to add the newly generated report. In the example above, we will just log the id.

Finally, for shits and giggles, we log all of the data returned from findDirectory.

Running and Logging

Running start() in your Google App Script UI will set the directory list, run the findDirectory() function and return it so we can log the results.

A successfully logged result would look like this. <ctrl>+<enter> to check the log:

Successful Directory Search

You can see on the first line of the Logs that the final folder, “Unit 3 Report” exists in the directory, My Drive>>2018>>Q4. It also displays the ID for the “Unit 3 Report” folder. The second line confirms this with a valid id and no error.

Okay, what if our first search item is wrong. Let’s change the dirList on line 51 to:

var dirList = ["bananas", "Q4","Unit 3 Report"]

Failure to find the folder in the root.

Here you can see that ‘bananas’ does not exist in the ‘root‘, My Drive directory. Line 1 logs an error. In line 2 we can see there is no id.

Okay, finally let’s say that our first file location in My Drive is good but we search for bananas in the Q4 directory.

var dirList = ["2018", "Q4","bananas"]

No folder with name in child directories.

Now on line 1 of the Logs it shows that no folder with the name “bananas” exists in the folder name “Q4“. On line two we can see that the id is also “None”.

Conclusion

In your Google Suite automation project where you want to find a folder location but do not have the id, you can accurately find the id of the last folder in the directory path by search through a list of known folder names.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro!

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

2 thoughts on “Google Apps Script – How to Find the Folder ID of a Non-Unique Folder Using File Path Names”

  1. I have a personal project that I want to simplify. The project involves a Google Sheet that links to several hundred Google Docs. Each G-Doc is a separate post in one of 3 different play-by-mail type games. What I want to simplify is the placing of each Post Name into the G-Sheet. I cannot find a formula in google to do this automatically and believe a custom formula would be needed but do not know a lick of Java and cannot, therefore, write the script myself.

    Below is what I am looking for. I do know some programming (C++, HTML) and so do have a basic understanding of what can and cannot be done. As such, I tried to be as detailed as possible. Based on a couple of your tutorials, I believe what I want is possible. So my question is, how would I accomplish it?

    Custom Formula placed in F4:FN and does the following:
    Search root/parent, [folder.name], for folder with value of [SUBFOLDER 1]
    Note: [folder.id] could also be used if the FOLDER ID is retrieved
    Search in folder Current Worksheet Name [TEXT STRING] for [SUBFOLDER 2]
    Search in [SUBFOLDER 2] for [SUBFOLDER 3]
    Search in [SUBFOLDER 3] for [CONTAINING FOLDER] with value [CELL REF 1]
    Search in [CONTAINING FOLDER] for [file.name] that begins with value [CELL REF 2]
    Return the end of [file.name] as [TEXT STRING] (Everything to the right of the 2nd “- “)
    Display [TEXT STRING] in cell or applicable error message

    Example/Values:
    Location of Formula: “F10”
    folder.name: “Star Trek SIMs”
    SUBFOLDER 1: “Starship Odysseus” [Current Worksheet Name]
    SUBFOLDER 2: “Step 1) Scripts”
    SUBFOLDER 3: “2) Edits”
    CELL REF 1: “Breathing In The Bindings” [Value in H10]
    CONTAINING FOLDER: “Breathing in the Bindings
    CELL REF 2: “003E” [ Value in C10]
    file.name: “003E – [Odysseus] Breathing in the Bindings – Provisions and Crew”
    TEXT STRING: “Provisions and Crew”
    Formula resolves with:
    If TRUE: “Provisions and Crew”
    If FALSE: Applicable Error
    #Error_Path: “Invalid Folder Path. Folder [folder.name] not found.”
    #Error_F.Name: “Invalid File Name. File [file.name] not found in specified folder.”
    Any other standard errors a formula could result in.

    The total file path for the above example is as follows:
    /Star Trek SIMs/Starship Odysseus/Step 1) Scripts/2) Edits/01) Breathing in the Bindings/

    The names of SUBFOLDERs 2 and 3 are static. However, there are 3 different SUBFOLDER 1s, each of which have their own copy of SUBFOLDERs 2 and 3. Although, CONTAINING FOLDERs are unique… so far.

    The folder “Star Trek SIMs” is located 2 levels down from the Root, My Drive. Does this matter where the script is concerned?

    Thank you for any assistance you can provide in this endeavor.

    P.S. While I would appreciate it greatly if you wrote the complete custom formula, I do understand if you do not want to take the time out of your day to do so. In that case, if you would be so kind as to point me in the right direction, it would still be appreciated.

    1. Hello Drackeye,

      If you are referencing values on your sheet to index files and folders, then perhaps a custom formula would be useful. Otherwise I would recommend straight GAS with say a custom menu item or button.

      You can iterate through your folders using nested for loops for each directory. In the past I have basically store an array of all the folders I have have completed my search in and compare that against the remaining folders. If there is a folder I haven’t searched, then I will look inside that and run the same tracking process. So if folder has not been searched, then 1 search for files and get file id and name vals and store for display in sheet, 2. register folder as has been searched 3. search sub folders by repeating initial loop but in new directory.

      You can use split and substr to get the selected item titles with out all the extra text.

      The drive details and the DriveApp docs should set you straight on the rest.

Leave a Reply