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

Google Apps Script

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

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

I’ve recently come across a tasks 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 sub folders 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 sub folder. You can have as many folder names and sub directories 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 sub folders 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 exists, 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 successful 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 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 exists 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.

 

 

 

 

 

Leave a Reply