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

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
Why not joining our forum, follow the blog or and follow me on Twitter to get updates when they are available