Use Drive properties to find app files

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.

{
type:"financials",
region:"eu",
value:2017
}

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

var ss = SpreadsheetApp.openById ('1181bwZspoKoP98o4KuzO0S11IsvE59qCwiw4la9kL4o');

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

{
type:"financials",
region:"eu",
year:2017,
mode:"test"
}

and this to identify the production version

{
type:"financials",
region:"eu",
year:2017,
mode:"production"
}

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

Mcbr-v4SsYKJP7JMohttAZyz3TLx7pV4j

Page Content hide

1 Creating the custom properties

2 Public or private

3 Other files types

4 The code

5 Share with your network

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).

var fileId = '1181bwZspoKoP98o4KuzO0S11IsvE59qCwiw4la9kL4o';
var DriveProper = new cUseful.DriveProper(Drive);

// set some properties
var result = DriveProper.update (fileId, {
type:"financials",
region:"eu",
year:2017
});

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

var ids = DriveProper.search ({type:"financials",region:'eu',year:2017});

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

var ss = SpreadsheetApp.openById (ids[0]);

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 var DriveProper = new cUseful.DriveProper(Drive);
.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 [{"key":"type","value":"financials","visibility":"PRIVATE"},{"key":"region","value":"eu","visibility":"PRIVATE"},{"key":"year","value":2017,"visibility":"PRIVATE"}]
.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 [{"key":"type","value":"financials","visibility":"PRIVATE"},{"key":"region","value":"eu","visibility":"PRIVATE"},{"key":"year","value":"2017","visibility":"PRIVATE"}]
.search	ob – an object with the matches required eg DriveProper.search ({type:"financials",region:'eu',year:2016}); 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 ["1181bwZspoKoP98o4KuzO0S11IsvE59qCwiw4la9kL4o"]
.remove	fileId – the id of a file properties – an array of property names to remove – eg DriveProper.remove (fileId, ["year"])) 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. [{"key":"type","value":"financials","visibility":"PRIVATE"},{"key":"region","value":"eu","visibility":"PRIVATE"},{"key":"region","value":"eu","visibility":"PUBLIC"},{"key":"type","value":"financials","visibility":"PUBLIC"}]

The code

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

Mcbr-v4SsYKJP7JMohttAZyz3TLx7pV4j

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

function testDriveProper () {

  var fileId = '1181bwZspoKoP98o4KuzO0S11IsvE59qCwiw4la9kL4o';
  var DriveProper = new cUseful.DriveProper(Drive);

  // set some properties
  var result = DriveProper.update (fileId, {
    type:"financials",
    region:"eu",
    year:2017
  });
 
  Logger.log (JSON.stringify(result));
  
  // read the again
  Logger.log (JSON.stringify (DriveProper.get (fileId )));
  
  Logger.log (JSON.stringify (DriveProper.search ({type:"financials",region:'eu',year:2017})));
  
  Logger.log (JSON.stringify (DriveProper.remove (fileId, ["year"])));
  
}

For more like this see Google Apps Scripts Snippets