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



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
constructorDrivea 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);
 .updatefileId - 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 propertiesUpdates 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"]
 .removefileId - 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 forumfollow 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"])));

  
}

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.



Comments