Blog Feed

I recently needed a way to easily search for files in Google Drive related to specific projects, the file template version, and whether it was an archived file or a live file with Google Apps Script.

In most cases, I needed to reference a bunch of Google Sheets built upon a template Google Sheet that was generated for a project, but also other Google files and non-Google file types in Google Drive.

The problem is that sometimes files can be edited by mischievous little imps 👿 that can mess with naming conventions and even administrative sections in files while the user is away 😉.

This means that titles and file data can be an unreliable way of searching for files. Further, searching within files for target text, especially over a large list of files, can be incredibly time-consuming programmatically.

There is an excellent way of managing this with Google’s Drive Labels API. Unfortunately, as of the writing of this tutorial, label creation is only available to Google Admins. However, there may be a change to this in the future with access to label creation at the Shared Drive level – take a look at this screenshot:

So what’s the alternative?

After sifting through the deepest darkest reaches of Google Drive API documentation, I stumbled across adding custom file properties to any file in your Google Drive.

Custom File Properties

Custom File Properties allow us to add our own searchable or queryable key-value pairs as metadata to any file in our Google Drive. It can only be edited programmatically, preventing these little imps mentioned above from messing with them.

There are two types of properties:

• properties: Visible to all applications.
• appProperties: Visible only to the app using the file (Great for Google Workspace Add-ons and Google Workspace Editor Add-ons for file tracking).

Custom file properties are limited to:

• A maximum of 100 custom properties per file from all sources.
• A maximum of 30 appProperties per file for per application and
• 30 properties per file.
• A maximum of 124 bytes per property. This is the combined total of both the key and value of each property.

The Example

We’ll be following an example of generating and querying a set of properties for a number of files.

To play along, you can set up your Google Apps Script file by:

1. https://script.new/  : Create a new Apps Script File
2. Add Drive API Advanced Service Version 3:
   1. Click Services
   2. Select Drive API
   3. Select version: v3
   4. Click Add
      

      

       

3. Create a file called Setup.gs.
4. Paste the following:
   Setup.gs

   /** * Creates 7 files from a template file. */ function setupDemoFiles() { const folder = DriveApp.getFolderById(REF.FOLDER_ID) const sourceFile = DriveApp.getFileById(REF.TEMPLATE_FILE_ID) for(let i = 0; i < 10; i++){ const fileNum = i + 7 sourceFile.makeCopy('Test Metadata' + fileNum, folder) } }

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   /** * Creates 7 files from a template file. */ function setupDemoFiles() {   const folder = DriveApp.getFolderById(REF.FOLDER_ID)   const sourceFile = DriveApp.getFileById(REF.TEMPLATE_FILE_ID)     for(let i = 0; i < 10; i++){     const fileNum = i + 7       sourceFile.makeCopy('Test Metadata' + fileNum, folder)       } }

5.  In your main Code.gs file add the global REF constant variable:
   Global REF constant

   const REF = { FOLDER_ID: "12P1<<YOUR_TEST_FOLDER_ID>>0yA3", TEMPLATE_FILE_ID: "14auvx<<YOUR_TEMP_FILE_ID>>XW3Y448", }

   1 2 3 4

   const REF = {   FOLDER_ID: "12P1<<YOUR_TEST_FOLDER_ID>>0yA3",   TEMPLATE_FILE_ID: "14auvx<<YOUR_TEMP_FILE_ID>>XW3Y448", }

6. Create a test folder and replace the ID in the FOLDER_ID with its ID.
7. Create a template file (I am using a blank Google Sheet here) and replace the TEMPLATE_FILE_ID with your newly minted template file ID.
8. Go back to your Setup.gs file and run the function setupDemoFiles()

This will generate 7 files that we can add our property data to.

Update & Create Custom Google Drive File Properties with Apps Script

We can update a single file in Google Drive by providing the file ID and an object of key-value pairs and passing it through the Drive Files update method.

This approach will either update or add new properties to a file. Any existing property with the same name as the new property key will be replaced.

The Test Function

Lines 1-7

In our test function, test_updateCustomFileProperties() we are providing a properties object containing 3 properties that we want to add to our file.

We are also referencing our template file global variable for this example.

updateCustomFileProperties()

Lines 13 – 36

The function

The updateCustomFileProperties() function takes a file ID string and a properties object of key-value pairs as its two arguments. Line 21

The Resource Object

Next, we create a resource object containing our properties. You may also wish to update other metadata items of the file or even the data of the file during this process.  Lines 23-25

Executing the ‘Update’ Method

In our try-catch statement, we then make a call to the Drive.Files.update() method to update our metadata properties.

The updates() method requires 4 arguments:

1. Resource: The target resource object to change any metadata items in the file, including our custom parameters.
2. File ID: The file ID of the file to update.
3. File content blob: For us, this is set to null because we do not wish to change the content of the file. However, you can create blob data to update the file contents here.
4. Optional parameters: Here, you can add optional parameters. For us, we want to see both the id and the properties of the file returned after we update the file. To do this, we use the ‘fields’ property.
   Commonly, you may need to update a shared drive and will need your optional parameters object to look a little more like this:
   
   const optionalArgs = { 'fields': 'id,properties', 'supportsAllDrives': true, 'includeItemsFromAllDrives': true, }

   1 2 3 4 5

   const optionalArgs = {     'fields': 'id,properties',     'supportsAllDrives': true,     'includeItemsFromAllDrives': true, }

   
    

If successful, the update will return the file’s ID and the full properties object, including the properties that you just added or updated.

Here is an example response:

Search Files By Custom File Properties in Google Drive with Apps Script

Now that we have created our first custom file property, we want to be able to query it. Fortunately, the Google Drive API allows us to do this with the Drive.Files.list() method.

Google Drive API Files list comes packed with a lot of options, which can make things a little confusing to set up for your needs. For this tutorial, we are going to keep things simple to focus on working with properties, but if you want to dive into queries, you should check out this tutorial:

List all files and folders in a selected folder’s directory tree in Google Drive: Apps Script

On with the code…

The Test Function

Lines 1-10

The function, test_searchFilesByCustomFileProperties(),  sets the properties to be queries. 

In the example, we are querying all properties by both their key and value pairs. However, we could just query by any one of these properties on their own. For example, we may wish to find all the files under the project, ‘metadata Test’, that have the status ‘live’ and not care about the version.

searchFilesByCustomFileProperties()

Lines 12 – 55

The Function

The searchFilesByCustomFileProperties() function takes a single object of properties to query as its argument.

You can provide some or all properties as your query to either broaden or narrow in your search query.

Generate the query string

Lines 20-33 and line 43

We can create a basic search query for the custom file properties in our Google Drive with the following query string:

properties has {key='project' and value='metadata Test'}

Here, we are searching for any file that contains the key, ‘project,’ that also contains the value ‘metadata Test’. To broaden your search query, you can also omit the and and value data to only look for files that contain the property key.

To search by multiple property queries, we need to join them with an and.

Our first task is to convert our searchQueries object to a 2d array of key-value pairs (line 20). We will also grab the length while we are here (line 21). So our example data would look like this:

Next, we will use a JavaScript reduce method to iterate over each query array item and generate a string of search queries for each one, adding AND between each query except for the last one (lines 23-35).

Finally, we should probably ensure that we are not searching for trashed files with trashed=false (line 43).

Our query string would then look like this:

Generate the Payload

lines 37-44

The Drive Files list() method takes a payload of arguments. In our example, we are including:

1. fields: Setting the fields to return the file ID, properties object and the name of the file. You can also display other file data like the creation date, edit date, the creator of the file, next page token if you expect a large array of results and a whole lot more.
2. supportAllDrives: Setting this to true allows us to access shared drives so long as we …
3. includeItemsFromAllDrives: to ensure we query data from all drives.
4. corpora: Setting this to allDrives allows us to not only see the files we created in a shared drive but also those created by others.
5. q: The query.

You can modify these arguments to better match your needs by looking at the documentation.

Executing the list request

Finally, we call Drive.Files.list() with our payload.

If successful, this will return an object that includes a files property containing an array of files that match the query.

It should look a little like this:

Keep in mind that if you expect a larger number of files, then you should also set a field to display the next page token and then make another request for the next page.

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

Batch Updating Custom File Properties for Multiple Files with Apps Script

Now, if you are setting up a bunch of files to contain custom file properties or updating the files you found from your properties search query, you will want to update them quickly.

Clearly, iterating over each target file to run Drive.Files.update() when you have thousands of files is going to take way too long.

Fortunately, the paragon of Google Apps Script development, Kanshi Tanaike, has created a helper function that allows us to batch update our update requests all in one batch or a chunk of batches at a time.

We will need to change the request type from a Drive API request to a HTML request with URL FetchApp but this is relatively simple for our purposes.

The Test Function

Lines 3-13

Our test function hasn’t changed all that much from our single-item update. We will be setting the properties that we want to add, and then updating all files in our target folder to have the same properties.

createBatchUpdateObject_CustomFileProperties()

Lines 15-58

This function will update each file in a target folder to contain the properties provided to it.

You may wish to tweak this function to do some other things:

• Change the property values based on some other factor. Say, if any files have a last edit date longer than 90 days, then set status to archive.
• Change the payload to how you draw the files. You may wish to extract all the files from an entire shared drive or traverse a particular directory.

We’ll keep things simple here and update all files in our target folder.

The function

The function, createBatchUpdateObject_CustomFileProperties(), takes two arguments:

• folderId: the folder ID of the directory that you want to get your files from. Note that this will only extract files from one level of folders.
• properties: The properties object that you will be updating.

The function will then return a string receipt of what was returned and updated from the batch request.

Extracting the Files from the Folder

Lines 24 – 26

Here we use the Drive.Files.list() method again and ensure that we are also looking in Shared Drives, just in case.

Our query this time is set to query and retrieve any file that has the parent folder of our target folder and is not a trashed file.

This will return an array of files containing the file name and ID of the found files using the fields property. Again, you may also need to get the next page token field to iterate over larger files.

Creating the Batch Request

Lines 41 – 49

Now that we have our list of files to update, we need to build our batch request HTML object.

We can build this by demoing a build in the documentation API build too to the right of the documentation reference. Line 41

First, we set our base URL and then add our optional arguments starting with a question mark, with each argument separated by an ampersand.  Line 43

Now we can iterate over our list of files and map an arrange of requests feeding in our payload. Here, we add an HTML method of PATHCH with an endpoint URL generated with our base URL, file ID and optional arguments. This will then return an array of requests for the batch update function. Line 41 – 49

Sending and retrieving the batch request

Lines 51 – 57

Finally, we can send off our batch request object. We will first create an object variable containing our batch path for Google Drive and our array of requests.

Then we will call Tanaike’s batchRequests() as the object of the argument. This object returns a URL Fetch App HTTP Response from the function, where we can check if an error occurred from the getResponseCode() method and display the receipt string from the request with get ConentText().

batchRequest()

This glorious function, created by Kanshi Tanaike, sends a batch request for any Google API type. It is best suited for post requests, and you will need to be mindful of managing large requests yourself, where you may exceed the batch request limitations:

• Batch Request Limit: Exceeding 100 calls in a single batch request may lead to errors.
• Inner Request URL Length: Each individual request URL within a batch must not exceed 8,000 characters.
• No Batch Media Operations: Google Drive does not support batch operations for uploading or downloading media files.
• No Batch File Exports: Batch processing is not available for exporting files from Google Drive.

Here is Tanaike’s guide for more information on this function, along with some benchmarks and samples.

Efficient File Management using Batch Requests with Google Apps Script

Conclusion

As you can see, using custom file properties in Google Drive with Drive API advanced service and Google Apps Script can be an extremely handy way of storing metadata for your files for better file querying and management.

I would love to see how you would use this in your own project, and it would provide a great deal of inspiration for other users. Please consider commenting below.

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

~ Yagi