Tag: Google apps script

If you have ever tried to get a list of all the child files and folders of a parent folder in Google Drive, you’ve no doubt discovered that it is a slow old process. Iterating over each item in a folder and then reading the metadata of that file or folder before calling the next one can take forever!

The built-in DriveApp Class for Google Apps Script is great for working on a small number of files and folders but it just doesn’t have the functionality to retrieve specific fields in your metadata, nor does the searchFiles method or searchFolders method have the ability to isolate just the fields that you want to retrieve. Subsequently, your processing time increases significantly as it ships junk data.

This tutorial is my attempt at, as the kiddies say today, creating a ‘blazingly fast’  file and folder iterator with Google’s Drive API v2 for Apps Script.

Skill level: Intermediate

What We’re Building

Our goal for this tutorial is to extract all of the child files and folders in the directory tree of a parent folder.

To save some confusion later on we will call both files and folders, items. This is the same vernacular that the Google Drive API uses.

Once we have a list of all the items, we want to store them in a Google Sheet.

Our Google Sheet will contain the following columns:

1. Icon (File or folder emoji for a quick visual reference)
2. File Name
3. File ID
4. Parent Name (Parent folder, that is)
5. Parent ID (We’ll throw in a link to the folder here too)
6. File MIME type

So your Google Sheet will look a little like this: Now, we are going to sacrifice some code simplicity in exchange for speed in this tutorial and I recommend that you follow along to get a full understanding of the code and how it works.

Strap in and let’s get started.

The Starter Sheet

Grab your own copy of the starter sheet below. Then head to Extensions > Apps Script to get started.

List all items from a directory tree with Apps Script | STARTER

In the Google Apps Script IDE you will see two files, Code.gs and Test.gs. Our main functions will be added to the Code.gs file.

Note, that the “Drive API v2” Advanced Service has been added to the project. You can now call ‘Drive’ to access it.

Next, select a parent folder (Target folder) that you want to work with.

You can grab the id from the URL.

https://drive.google.com/drive/folders/[your folder id]

The Video Tutorial

Getting All the Google Drive Files and Folders in a Parent Folder with Apps Script

My first inclination here was to get a list of all items in the parent folder and then iterate over them calling the Drive API to retrieve each one. However, I knew that was frustratingly slow.

Instead, I thought I would use the Drive.Files.list() method. On its own, this will collect your entire list of files and folders in ‘My Drive’.

Not so surprisingly though, the process is pretty quick for the amount of data it is shipping. Why? Because it is making the query server-side in one big hit and then returns everything back.

Cool. I was on the right track, but I didn’t want all of my files and folders. I only wanted to collect the items starting from a selected parent folder.

Query the Drive API files list

Fortunately, one of the optional parameters of the ‘list’ method is ‘query’ represented as (q).

Great! So how do I make a query? Well, there was no link from the query definition to any documentation on this. C’mon, Google!

Scrolling down the sidebar in the docs did eventually reveal a list of Search Query Terms. The ‘parents’ query looked promising.

After a bit more stumbling around I came across a guide in the docs called ‘Search for files & folders’. Here there was a more concrete example of how to use the query.

"q" = "'[folder ID]' in parents"

Replace [folder ID] with your folder ID, but make sure you keep it in single quotation marks.

I also discovered a while ago that it is a good idea to add 'trashed = false' to ensure that any recently trashed files are not included in our list.

Stay classy my friends. 

Cool. I think we have enough now to run our first test.

Add the following to your Test.gs file.

Update the folder ID in test_getFolderItems() and run the function.

Your results  should look a little like this:

It’s unlikely that you will be able to see everything in the returned object from the log. If you do want to see it all, you could store it in a JSON file like this:

Here you can see that the returned response contains a bunch of information about the request followed by an array with the property id, ‘items’. This will be where all of the files and folder data are stored.

Another noteworthy property is the "nextPageToken". This may not appear for you in your selected folder it only appears when the request reaches the maximum number of results it can return. By default, this is 100 results, but you can modify this to display fewer with 'maxResults' = [integer] parameter.

This will become useful for us later.

Looking at the item object metadata

Items are stored in an array of objects containing all the metadata for that object. Let’s take a look at an example. I’ll highlight all the parts that we will need to extract for our Google Sheet.

Here, you can see that we want to grab the following:

1. "mimeType": "application/vnd.google-apps.spreadsheet"
2. "id": "1UjRKjrmti0mT7_rdBusrXJ0RIGeVBGOm" – inside 'parents'
3. "title": "Basic Sheet and Notes Pages Template w/script"
4. "id": "k2hlho34IamAgoat99two"

Get only selected fields from the Google Drive item with Google Apps Script

It’s great to get all the data for each item and all but grabbing such a big hunk of information will slow down our code and, honestly, is a little confusing. Fortunately, we can request only certain fields to be displayed.

Now that we know the properties we want to extract we can include a ‘fields’ parameter into our payload of our request to the Drive API.

How do we write a ‘fields’ parameter string?

• All fields that you want to be displayed in your string are separated by a comma.
  • "id, mimeType, title"
• Any nested object fields, for example, "lastModifyingUser":{"picture": "URL"} are separated by a forward slash.
  • "lastModifyingUser/picture/url"
• Any property within an array of objects is placed between parentheses “()“.
  • "parents(id)"

In our example, our root property is items, which is an array of objects. One of those objects, ‘parents’ contains another array of objects from which we want to extract the ‘id’. Our fields parameter would then look like this:

We will abstract out the query to a query builder function and update our getFolderItems and test functions.

Add the test function to your Test.gs file and replace the  Code.gs with the two new functions. Make sure you keep the top comments in Code.gs so you know where to go to get help when you come back to it in the future, 😉.

The Test Function: test_getItemsForFolderArray()

This time around, we are going to create an array of folders.

A bit of foreshadowing here, but we will want to store a dynamic list of parent folders that we want to query. Each array contains an object with an id property and name property.

Note that we have changed the function that we are calling to getItemsForFolderArray() adding our folders as an argument.

Get Items for All Folders in the Query: getItemsForFolderArray()

First, notice that we have renamed the getFolderItem() function to getItemsForFolderArray() to more clearly explain what it does.

On line 17, we have replaced the text query with the variable, “queryString” which can be found on line 13. This variable calls the function createQueryString(folders), taking the folders variable as an argument.

The Query Builder: createQueryString()

Here we are relying on the Google Drive APIs query search term.

Line 13: We start off by creating an empty let variable called “queryString”.

We then need to check two conditions of the incoming folders.

Lines 15-17: If there is only one folder in our ‘folders’ array then we just need to input the folder id along with the ‘in parents’ query.

Lines 19-25: Alternatively, if there is more than one folder in our ‘folders’ array we need to make an ‘or’ statement.

Lines 20 & 24: All ‘or’ statements are batched within parentheses if they must be resolved before another ‘and’ query. For example:

' (id1 in parents or id2 in parents) AND trashed=false'

Line 21: Next, we start a forEach loop to iterate through each of your folders. We also check to ensure we have the index (idx) for each iteration.

Line 22: On each iteration, we first check if we are on the last folder. If we are, we don’t need to append an “OR” to the end of our ‘in parents’ query. However, if with aren’t then we add the “OR” statement.

Line 28: Next, we add any other queries we want to add to our query statement.

Line 31: Lastly, we return our built query string back to the getItemsForFolderArray() function to be added to the payload send to the Google Drive API.

Run the script

Go ahead and run test_getItemsForFolderArray() again.

Notice now that when you run the code, you will see different parent ids in your item list.

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

Building the 2d file and Folder Array for the Google Sheet and Updating the ‘folders’ array for the next search

Let’s create a final assisting function before we write our main function. This one is a bit of a doozy.

Our goal is to iterate over the newly collected items from the Drive API call and on each iteration carry out two things:

1. Create an array of data for each column of the target Google Sheet.
2. Update the ‘folders’ variable array with any new child folder that we have collected from the recent API request.

Why do these two steps in one function?

Well, you could certainly abstract them into their own functions for clarity, but both functions do need to iterate over the same data, so rather than wasting precious time with two iterations we will run one iteration and extract the necessary data for each array in one hit.

I’ll go into more detail on both of these arrays in a moment, but first, let’s take a look at the code for this function.

The createFileArrays function takes two parameters, the folderArray retrieved from the Google Drive API call and the current folder array that was used in the query of the current API call.

Setup and Loop

Lines 13-16: The first task is to set up the fileArrays object that we will return at the end of the function. This object contains:

• spreadsheetFormatted: This is the 2d array that will populate the target Google Sheet. You can see how it is structured in lines 32-37.
• childFolderIds: This is the collection of any folders found on the current call to the Drive API.

On each iteration, these properties will be updated.

Line 19: Here, we use a JavaScript forEach loop to iterate through each of the found items in the selected folders.

Line 21: We will also check the mimeType (the file type) on each iteration to see if it is a folder and if it is, set isFolder to true. This will be referenced in two places later on in the function.

Creating the spreadsheet formatted array

Line 25: As a part of our 2d array for the destination Google Sheet, we will display the parent id and parent name of the file. The tricky part is that in Google Drive each file can have multiple parents. We will need to create a simple array of all parent IDs of the file and iterate over them to compare them against the list of folder IDs.

Line 26: Next, we will use the JavaScript ‘find’ method to search for any id that is included in the array of file parent folder Ids. This will return the matching parent folder (with its id and name) if it is found or a falsy ‘undefined’ if one does not exist in the list.

The results will then be used in the spreadsheet array.

It is undoubtedly improbable for the current list of folder ids to not be in the parent list of ids for the currently iterated items. This is because they were extracted from the ‘folders’ list.

Line 28: For the parent folder id column of our Google Sheet it would be helpful to provide a hyperlink to the folder where the item exists.

Here, we use the HYPERLINK Google Sheets function applying the standard Google Drive URL with the appended parent folder ID and then using the folder ID as the label.

Line 30: Next, we create either a file emoji or folder emoji for our image cell using the isFolder boolean variable generated on line 21. This will provide a convenient visual queue to identify the nature of the item.

Lines 31-38: Now that we have all the components we need to build our array we set them into the fileData array variable.

Line 40: Finally, we concatenate the newly created array to the existing fileArrays.spreadsheetFormatted array.

Creating the child folders list

Every time we search over our Google Drive’s selected IDs it is likely that we will collect more folders to search. In this section of the function, we need to create a new array of folders from the items we have just collected.

Lines 44-51: If the currently iterated item is a folder (isFolder) is set to true, then we need to add it to our fileArrays.childFoldersIds array.

Test the function

Copy and paste the code below. You can also paste the folder array from your  test_getItemsForFolderArray() function that you ran earlier.

The first logged item will be the raw response data from the Drive API.

The next one will be the current array of 2d data destined for our Google Sheet.

The last log will be the new child folders that we will need to query on our next call to the Drive API.

Get All Files and Folders in The Parent Folder directory tree

In this section, we will create our main run function that will iterate over all files and functions in the directory tree.

Here is the main code for this function:

The Main Function Variables

Line 5: Out of curiosity, we want to check to see how long it will take for the script to run. We use the JavaScript time method with the console here. We will add the end-time method at the end of the script.

Line 8: Here we add our parent folder id. Go ahead and update the script to add your own folder id now.

Line 10: The folderData  object variable will store the response from the Drive API and will be updated after each call to the API.

Line 11: The directoryArray variable stores the total found items in the directory tree as a 2d array to be added to the destination Google Sheets. It will be concatenated on each iteration of the while loop.

Lines 12-17: We are already familiar with the folders variable in our testing of the helper functions. Here we set up the parent folder as our first folder to search. Adding its array to the id.

For the name, we call the Drive  API once using the ‘get’ method. We specify the field “title” only and then retrieve the title of the folder. Alternatively, you could paste in the folder name yourself and save a call to the API.

Note that if you are using Shared Drives you will also need to include "supportsAllDrives": true to the parameters to request the folder name. So folders.name should look like this:

Looping over the folders. What’s happening?

So as I alluded to earlier, we aren’t actually, calling the Drive API list method on each folder. Instead on each call to the API we collect a list of folders and add them to a folders list then we will run a query on all of the folders in our list (array).

 

Line 20: Starts the main while loop. Here we check if there is a least one folder item in our folders array, by checking the length of the array. If there is, then we continue.

Remember, the folders array is updated every time we make a list request to the Google Drive API. So when our array runs out of folders, then we can stop the while loop.

Line 22: Here we call the getItemsForFolderArray() function taking the folders array as an argument. This will call the Drive API and return the current list of items found in all of the folders in the folders  array and store it in the folderData object.

Line 23: Remember, that the folderData object contains data on the Drive API list process. This includes the item property which is an array of all found items from the list query. We will temporarily store these items in the items array.

Our next task is to store both our data that is going in the Spreadsheet and also update the folders array.

Line 27: First, we call the createItemsArrays() function applying the items and folders arrays as arguments. This will return the childFolderIds array and spreadsheetFormatted array assigning it to the variable itemArrays.

Line 28: Next, we replace the folders array with our new list of folders to query.

Line 29: Then, concatenate the directoryArray with our newly found list of items.

Updating the Google Sheet with our Items List

Our last major task for this main run function is to update our Google Sheet.

Line 34: First we call the active spreadsheet bound to this Apps Script project invoking the SpreadsheetApp class and calling the getActiveSpreadsheet() method.

You could also use the openById() method to add the data to another specific spreadsheet.

Next, we chain some more methods:

Line 35: This gets the sheet tab by its name using the getSheetByName() method.

Line 36: We then need to select a range of cells to add our data. This will be equal to the number of columns and rows in our 2d array. Here we call the getRange() method which has an optional 4 integer insert parameter setup:

1. Start Row: This is row 2 below our header.
2. Start Column: This is Column A or column number 1.
3. Row depth: How many rows we will be adding our data to. We can calculate this by getting the length of the main, outer, 2d array of the directoryArray.
4. Column width: The number of columns across on each row we will be adding. To get this value, we can get the length of the zeroeth item in the inner array.

Line 37: Finally, we add our array values to the Google Sheet range with the setValues() method adding the directoryArray as its argument.

Run your code

After updating your parent folder and saving your script, you should be good to go running your code and see the results.

Go ahead and check out your results in your connected Google Sheet!

What about edge cases?

By now some of you may have been vigorously rage-correcting your glasses thinking, ‘This code sucks! Yagi hasn’t even handled for huge amounts of items in a folder! What happens if you are querying a large number of folders all at once?! Will it break the query? What if the code times out?! Argh!^n.”

Well first, that’s a bit rude mate. Second, now that we have our basic structure sorted out let’s go back into our code and update it one at a time.

One thing I will leave out is handling for time-outs. It’s a big subject with many approaches. I discuss this a little more later when we get to it.

Next Page Tokens: Handling for a large item list

The Google Drive API file list is limited to returning a maximum of 100 items per request. I guess the list method is considered a batch request because it contains limitations in batch requests.

Fortunately, each list request can provide a nextPageToken property with a unique id that you can add to your next API request and get to the next page. If the total number of queries is less than the maximum number of requests then the nextPageToken property will not be present.

With this in mind, we can go back and update the getFileAndFolderIds() function and getItemsForFolderArray() function.

getItemsForFolderArray()

Our first task is to retrieve the page token from the Drive API query.

Check out the updated  getItemsForFolderArray() function:

Lines 8 & 12: You can see now we have an extra parameter. The pageToken. This will either be a token string for the next page or null.

Line 19: Next, we need to include the nextPageToken field into the fields property of the payload to ensure that it is returned when it is available.

Line 23: Lastly, we check if we have a page token, for the next page. If we do, then we know that we are looking at the next page of the current query of folders not a new query of folders. We then add the page token to our payload to be sent as a request to the Drive API.

getFileAndFolderIds()

Now we need to create a loop whenever we have a page token to iterate over the current query of folders. Check out our updated code:

Lines 21 & 33: First, we create a JavaScript do-while loop. We will need to carry out the first request to the API before we start the loop so we need to make a ‘do’ statement first. Then, while a page token exists, we need to continue to call the API until we have run out of pages.

Line 22 & 29: Next, we moved out our items array and called it before this do-while loop. This way we can append to it and build up the items array with new items as they come in from the consecutive page requests.

Lines 10 & 31: Lastly, we add a let variable, pageToken, that we update on line 31. If the page token is retrievable, it will return a token string otherwise the variable will store null.

Testing the page token iterator

We can test the page token iterator by adding the maxResults parameter to the payload variable in the getItemsForFolderArray() function.

Try setting the max results to something really small like 5 or 10. Like this:

'maxResults' = 10

Now run the main getFileAndFolderIds() script and note how long the script took. It should have taken quite a bit longer. Don’t forget to remove this line when you are done testing.

Another way of reviewing this process is to use the Apps Script IDE debugger and mark the ‘pagetoken = line‘ you can then review the debugger data to see what data was collected.

Max Query Length: Is the query too long

With the way our query works, this edge case is less likely to occur but is possible enough that it is worth checking for.

Hypothetically, a query may have a maximum string length of n characters.

This isn’t something I had a chance to personally measure but the maximum query length may be a little under 8,000 characters if we can assume our API request behaves the same way as a URL query, well, according to the Improve Performance documentation. The Stackoverflowians have mixed views on this citing a query characters length of between 29949 and 29999 for one commenter and 7340 for another.

Probably our best bet here is to just set a variable with a max number of queries for ‘[folder is] in parents‘ that we can generate, and modify it in the future should we confront an error. Or, you know, test the length properly 🤷‍♂️.

Get the max number of ‘in parent’ queries

Add the following code to your Test.gs file to get the maximum number of folder queries we should add per request.

Basically, we are setting our maximum hypothetical query string length. Then we subtract the remaining part of the query from the length before dividing that value by the average length of a ‘[folder is] in parents‘ query.

This will give us the total number of folder queries we can add to a query request before it throws a wobbly.

With this value in hand, let’s update our code.

getFilesAndFolderIds() update to handle max query string length

Line 4: First we set a maximum number of folders that we want to add to our query. You can see that I added ‘5’ above for testing, but I would return to ‘598’ for future queries.

Lines 16-18: Next, we create an array that will temporarily store any remaining folders that we did not have enough room to add to our query.

Lines 26-33: Before making our API request we check the total number of folders that we need to query. If the total number (array length) exceeds our maxNumOfFoldersPerQuery value then we need to store the remainder in foldersRemaining and query the rest.

Line 50-52: Once we have made our request to the API we update the folders array with the remaining folders and new folders that we collected with our query.

Go ahead and test the code again now, with a small number in the maxNumOfFoldersPerQuery variable. Then run the function.

You could also console log the remaining folders array to see what is left over.

Rest the maxNumOfFoldersPerQuery variable with the larger value in the comments or your own custom value.

Some Edgier Edge Cases

So this is about as far as I want to go in this tutorial, but if you want to go further into the weeds you might want to consider a few other edge cases.

Timeout

Roughly, your script will time out after about 6 minutes of running this script. So far, I have yet to have a problem with the script timing out on large directories due to its solid performance. However, on a less performant version, I did and had to handle for it.

Some alternatives and workaround you might want to consider are:

• Monitoring the time and triggering a clock trigger to run the remaining folder iterations just before the time runs out.
• Run multiple instances of a API request to your own custom Google Apps Script web app. This means that you request the webapp/s to run the query relatively asynchronously using a UrlFetchApp request from the FetchAll class.
• Try a different runtime like Node.js or Python. Alternatively, run the code through something like Google Cloud Run which has a much larger runtime.

QUOTAs and limitations

According to the docs, Google Drive API requests are limited to:

• 20,000 queries per 100 seconds.
• 20,000 Per 100 seconds per user.

That is an awful lot of queries.

Conclusion

“…blazingly fast…”, Jeff Delaney, FireShip.io

As you can see the code we build together is a, dare I say, ‘blazingly fast’, approach to a glacial problem of retrieving files and folders from a Google Drive.

I really love to hear how you used the code in your own project. Did you extend it further to run bulk updates or deletes? Did you add new fields? Did you use it to duplicate a directory? Let me know in the comments below!

Need help with Google Workspace development?

My team of experts can help you with all of your needs, from custom app development to integrations and security. We have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

Changelog

• 2023-07-03
  • Removed “_v2” artifact from createFileArrays() function.
  • Special thanks to Jes for the find.
• 2023-06-22
  • Updated Shared Drives section to include ‘includeItemsFromAllDrives’: true.
  • Added a note to The Main Function Variables to ensure that sharedDrives:true is added to the Drive.File.get request.
  • Special thanks to Edward for bringing this up.