Sheet and document IDs can be a a handful to manage, and if you want to use a different file you may need to update all the scripts that reference it. The Drive API allows you to assign custom properties to a file so you can search for it later.

Imagine that you had a script that needed to find a particular spreadsheet but the spreadsheet might be replaced or change over time. Instead of using the sheet ID, your script could find spreadsheets that has particular custom properties set instead.

In this example by script needs to use a spreadsheet which has all of these properties.

Of course if I knew its ID, I could simply open it with

But this supposes that it it’s always that ID, but you might want to use a test version, or a copy , then use the script to use the real thing, involving a change of script and ID and get involved in the business of tracking ID numbers, or it may be that you have multiple sheets with the same characteristics all of which might need to be processed by a script.

If you set up a process and convention, then a workgroup could collaborate on files using something like this to identify the test version

and this to identify the production version

Using the custom properties of Drive, we can assign values like this to files, but it can be quite fiddly using the API, so here’s a library to make things a little easier.

This class – DriveProper can be found in my cUseful library, and uses the advanced Drive service which you need to enable in your script before you can use it.

Here’s the key, and it’s also on github


Creating the custom properties

The first job for someone is to add the custom properties to a file. These will be added to any other properties or will replace the values of matching properties of the same scope (public/private .. see later).

Then anyone who is looking for a sheet with that properties can do this, and get back a list of matching IDS

and then you can open the file (or files) using the ids returned

Public or private

Each property can be public (visible for all scripts) or private (visible only for this script). Note that they are independent so you can have the same property as both public and private with different values. Which one you use depends on whether you want multiple scripts to be able to find files using these properties or not.

Other files types

Although this example is about spreadsheets, you can use the exact same approach for any kind of Drive file.

Here’s the methods available

 method  arguments  returns  purpose
constructor Drive a new instance  The cUseful library is dependency free, so you need to enable the advanced Drive service in your project and pass it to the constructor as in
 .update fileId – the fileId to apply the update to.

ob – the object with the properties to set

public – optional boolean value – whether to make the property public or private

 An array of properties Updates the file properties and returns an array of the properties that were set – eg
 .get  fileId – the id of a file

all – optional boolean value – whether to return all the properties or just the private ones

 An array of properties  The properties that are set for the given scope – eg
 .search  ob – an object with the matches required
all – optional boolean value – whether to search on just Private properties, or all.
 An array of fileids who have all the objects matching  A list of matching fileIds

 .remove fileId – the id of a file

properties – an array of property names to remove – eg

public – optional boolean value – whether to remove the public or private property
 An array of properties that are still set  Remove the properties in the list, and return the properties thar are still set – eg.

The code

The code is in the cUseful library. Here’s the key, and it’s also on github


For more like this, see Google Apps Scripts snippets. Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.

Here’s an example of a test using it

For more like this see Google Apps Scripts Snippets
Why not joining our forum, follow the blog or and follow me on Twitter to get updates when they are available