Blog Feed

Add Custom Queryable Properties to Any File in Google Drive with Apps Script

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:

Google Labels may be available for creation at the Shared Drive level in the near future
Google Labels may be available for creation at the Shared Drive level in the near future

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

      Add Drive API Advanced Service V3 to Google Apps Script Project
      Add Drive API Advanced Service V3 to Google Apps Script Project

       

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

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

  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:

     

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

 

Get Google Sheet Tab Name and ID with Google Sheets API Advanced Service with Apps Script

The Google Sheets API Advanced Service has a strange habit of switching between using Google Sheet names like 'Sheet1' and the sheet ID that you would see in the URL, like 482103711.

This can be problematic when working with complex processes in the Sheets API.

One of the first things I do when running a complex process is to make an initial call to the Sheets.Spreadsheets.get() method to generate an array of all of the sheet tabs by name and sheet ID. Yes, it is an additional call to the API but if we do it initially in conjunction with any other core requests from the spreadsheet then we only need to do it one time.

Continue reading “Get Google Sheet Tab Name and ID with Google Sheets API Advanced Service with Apps Script”

Append – Google Sheets API Advanced Service for Apps Script

In my last written tutorial, I covered how to create a Google Sheets Row Archiver with Google Sheets API Advanced Service and Apps Script and the very last task of this service was to append rows into an archive Google Sheet.

The standard approach to appending data in Google Apps Script is to use the apendRow() method inside the Sheet class using the SpreadsheetApp Service.

This approach is limited to appending a single row to the bottom of the selected sheet and the append will only occur after the last row in the entire sheet where there is text.

There are, of course, some other approaches to appending data and finding the last row. Here are some that I have covered:

Sheets API Spreadsheets.values.append()

As of the time of this tutorial, there are a number of documentation errors and bugs in the constructor classes in Google Apps Script for the append method for the Sheets API advanced service.

You can find more here on my bug reports:

The Video Tutorial

You can grab a copy of the starter sheet here!

The video:

 

The Benefits of Sheets API Append Method

Append multiple rows

With the advanced service, you can append multiple rows of data to your sheet.

Insert Rows or Overwrite Existing Rows

We can decide if we want to insert new rows after the end of our existing data or overwrite the existing empty spaces. This is particularly handy when used in conjunction with the below.

Select a target range

Say you have a dataset that you want to update that has a bunch of summary data below it. With the append method, you can select the range of the data all the way down to just above your summary data and append to it.

Insert data as Raw Data or User Formatted

You can also choose to either display the data as raw data input as is, or allow Google Sheets to format the data as if you are manually inputting it into each cell.

An Example

We will use this example to illustrate how the method works in the remainder of the tutorial.

Google Sheets API advanced service example sheet

Here we have a sheet of appendix data (Get it).

The most recent data ends on line 24. We also have a set of summary data starting on line 44.

Our doctor usually sends batches of data about the appendix of each patient they see in a week. They will append that cumulative data to the sheet at the end of each week.

Now have a quick look at the code.

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

The Append Method Parameters

The basic append method looks like this:

Sheets.Spreadsheets.Values.append({values: 2d_array}, sheet_ID, range_to_append, payload_object)

In our example, you can see that we have placed this method in a JavaScript Try…Catch statement to handle any returned errors gracefully (lines 20-31).

The ‘append’ method returns an object that looks like this:

Probably the most important bit of information that you will get back from this object is the updatedRanges property. Everything else you will be able to get before making the append request. This property will give you the range of the updated data in A1Notation. From this, you may want to apply formatting or data validation.

2d Array of Data to Append

The first parameter is a little counterintuitive in that you must first declare an object which contains the values property. This property contains the 2d array of values that will be appended.

In our example, we mix up our 2d array of data to illustrate how some of the parameters may affect the data presented in the sheet. (lines 3-8)

More on this later

Spreadsheet ID

Next, we insert the Google Sheet ID of our spreadsheet.

We set this in our ssID variable. Line 2

Range To append to

The third parameter is the range to append to. This one is a little weird too but after you understand it, you’ll see that it is super handy.

Providing this range allows the Sheets API to search for the first empty row after the last row of data only in the range that you select.

This means that it will ignore other columns not within its search range that may have an end position much further down the sheet.

It also means that you can set your append between two sets of data in the same column. I illustrate this in our example above where we have a set of data running from A4:D24 and then a set of summary data from A44:B50, but we will append only after the first set of data.

In our example, we set this range to Sheet1:A5:A45.

Payload Object

The payload object or as Google calls it, the parameters, contain an object of optional parameters that you can include in your project to alter the outcome of the append or return your data in a different way.

After some considerable tests, I found that many of the parameters do not affect how the data is displayed on the sheet. However, there are three of note.

valueInputOption (mandatory)

One mandatory parameter must be included in each append call:

valueInputOption: "USER_ENTERED" // USER_ENTERED or RAW line 12

Using the USER_ENTERED method will evaluate the 2d array and append it as if the user manually entered it into Google Sheet. So this 2d array:

… will look like this:

Google Sheets API USER_ENTERED
Google Sheets API USER_ENTERED

Here you can see that the string number has been converted to an actual number in the sheet and all of the formulas have been executed.

Whereas, if we selected the RAW option then the data would look like this:

Google Sheets API RAW
Google Sheets API RAW

Now, the data is represented almost exactly as we had it in the code. The only difference is that the numbers have been influenced by our Google Sheets format to display ‘cm’ after the number in column B when a number is found. Note that the number in B28 is in fact text because it was a string in the code.

Further, all formulas are escaped with a “'” to display them as text.

Finally, the dates are now strings and no Google Sheets Date Number Values. These too are escaped with a  “'“.

insertDataOption (optional)

One of the cool things with this ‘append’ method is that you can choose to either overwrite existing empty cells or insert whole new rows.

The insertDataOption allows us to do this by selecting between either “OVERWRITE” or “INSERT_ROWS”.

“OVERWRITE” simply appends over the current rows so our result in the example would look like this:

Google Sheets API insertDataOption OVERWRITE
Google Sheets API insertDataOption OVERWRITE

Notice that the new data is written over the empty spaces below the orange line at A25:D28.

Now, let’s run the script again with “INSERT_ROWS” this time noting where the orange line is and also where the summary down the bottom starts:

Google Sheets API insertDataOption INSERT_ROWS
Google Sheets API insertDataOption INSERT_ROWS

You can see now that 4 rows have been inserted directly below the last row of data. This has pushed the orange line down 4 rows. The data is then inserted into these new rows.

You can also see now that the summary data down the bottom has also moved down 4 rows.

includeValuesInResponse

You can also opt-in to include the values that you just added to your sheet back into your response object. So it might look like this:

If you chose the “USER_ENTERED” valueInputOption then the returned results will be transformed as you can see in the sample above.

By default, this option is set to false.

Also, note that using this option will slow down your process because it needs to bring the appended array back in its response to you.

Conclusion

As you can see the Google Sheets API Append method is a much more versatile approach to appending data in a Google Sheet in Apps Script. Of course, the API is not limited to Apps Script, the API can be used in other programming languages too.

I would love to hear how you used this ‘append’ method in your own projects. It really gives inspiration and insight for others. Feel free to make a comment below.

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

~ Yagi.

Creating a Google Sheets Row Archiver with Google Sheets API Advanced Service and Apps Script

I have a client project coming up shortly where I will be diving back into the Google Sheets API Advanced Service in Google Apps Script. As part of my refamiliarisation with the API, I was thinking about the minimum number of API calls it would take to efficiently create a Google Sheets line archiver based on a set of multi-column parameters.

Here’s the example and the starter sheet if you want to play along.

The Sample Data Google Sheet: The Space Ship Manufacturing Fleet Requests

Stardate 78183.10, it’s surprising spreadsheets have not been improved upon in the almost half millennia since their origin, but they are still everpresent while the inevitable decay of other software products is a little more than the dust of a bygone memory.

We’ve decided to create an archiving automation for our spaceship-building bids for our new space fleet. We want to remove any bids from the sheet that have been declined and are before “2025-04-30”, (Damn it! Timeline inconsistencies already!!!!).

Once, we find these rows to archive, we want to remove the selected rows and append them to our ‘Archive’ sheet.

Check out the sheet:

Archiving Script for Google Sheets in Apps Script and Sheets API advanced serviceThe Starter Sheet

So, how many API calls?

Continue reading “Creating a Google Sheets Row Archiver with Google Sheets API Advanced Service and Apps Script”

Get a Google Docs Body Text with Apps Script

Retrieving a Google Docs body text is quite easy with the help of Google Apps Script.

Well, until it isn’t. Let me explain.

Continue reading “Get a Google Docs Body Text with Apps Script”