Page Content hide

1.1 Benefits of using Microsoft OneDrive

2.1 OAUTH2

2.2 The plugin

2.7 Fingerprint optimization

OneDrive as Cache platform

In Apps script library with plugins for multiple backend cache platforms I covered a way to get more into cache and property services, and the bmCrusher library came with built in plugins for Drive, CacheService and PropertyService. Google Cloud Storage platform cache plugin for Apps Script crusher showed how to extend this concept to other back end platforms, using Google Cloud Storage as the service. In this article I’ll show you how to write a plugin to use Microsoft One Drive as the backend

Benefits of using Microsoft OneDrive

Seems pretty crazy, right ? What’s the point when we have Cloud Storage, Property Service, Cache service and even Google Drive. Well, there’s this emerging goodie. Microsoft’s answer to Apps Script – maybe the next installment in the evolution of VBA, Office Script . I’ve written about JavaScript for Office in various posts on this site, and of course plenty about VBA (even a book about it), but Office Script is quite different. I’ll be writing about that quite a bit in coming posts. But to get back to the point – having a universal cache platform to share real time data easily between the Apps Script and Office script world sounds like it would be interesting. So that’s what this is leading to. We can also use much of the same code to read and write content directly to OneDrive for other stuff. But first, how to create the plugin on the App Script side.

How to use

Just as in the other examples, it starts with a store being passed to the crusher library to be initialized. The OneDrive version is going to have to use the OneDrive API, so we’ll need to write that from scratch and also handle authentication – but it’s pretty straightforward as you’ll see. Once the plug is written we can use it in the exactly the same way as the Google platform plugins – like this

  const goa = cGoa.GoaApp.createGoa('onedrivescripts',PropertiesService.getScriptProperties()).execute()
  const crusher = new CrusherPluginOneDriveService().init ({
    tokenService: ()=> goa.getToken(),
    prefix:'/crusher/store',
    fetcher: UrlFetchApp.fetch
  })

Initialize the crusher

You’ll notice I’m using goa for auhentication and authorization to OneDrive – see How fast can you get OAuth2 set up in Apps Script for more details. There’s a small amount of setup for that, so let’s get that out of the way. There are 3 steps

Create an app your Microsoft DeveloperApp in their portal, and get some credentials. It’s here https://apps.dev.microsoft.com/
Write them to your property store
Deploy a web app so you can authorize it via the MS Oauth2 process, and set your callback url in the Microsoft developer profile.
Delete all that and forget about it forever. It’ll take care of refreshing tokens etc.

OAUTH2

One off function to set up goa. You only need to run this once, then you can delete it.

/**
* this stores the credentials for the service in properties
* it should be run once, then deleted
*/
function oneOffStore () {
  // this one connects to onedrive
  var propertyStore = PropertiesService.getScriptProperties();
  
  cGoa.GoaApp.setPackage (propertyStore , {  
    clientId : "Your microsoft clientid",
    clientSecret : "Your microsoft clientsecret",
    scopes : ["wl.signin","wl.offline_access","onedrive.readwrite"],
    service: 'live',
    packageName: 'onedrivescripts'
  });
  
}

Initialize Cloud Storage

Make and deploy this webapp, just to get Microsoft Oauth to talk to you

function doGet(e) {
 
  // running as the user running the app
  var goa = cGoa.GoaApp.createGoa('onedrivescripts',PropertiesService.getScriptProperties()).execute(e);
   
  // it's possible that we need consent - this will cause a consent dialog
  if (goa.needsConsent()) {
    return goa.getConsent();
  }
   
  // if we get here its time for your webapp to run and we should have a token, or thrown an error somewhere
  if (!goa.hasToken()) throw 'something went wrong with goa - did you check if consent was needed?';
   
}

Deploy this

It’ll bring up this dialog

Take a copy of the redirect URI now, enter it into your Microsoft developer console as the authorized callback, then click start. It’ll go through the microsft dialog, and when it’s done (it won’t return anything), you can undeploy it and delete the doGet function. You’ll never need it again.

The plugin

Now we can get started on the plugin – it’s essentially the same as the Google Drive one, except this time we’re going to an external API.


// plugins for Squeeze service 
function CrusherPluginOneDriveService() {

  // writing a plugin for the Squeeze service is pretty straighforward. 
  // you need to provide an init function which sets up how to init/write/read/remove objects from the store
  // this example is for the Apps Script Advanced Drive service
  const self = this;

  // these will be specific to your plugin
  var settings_;
  var folder_ = null;
  let fetcher_ = null;

  // make sure we start and end with a single slash
  const fixPrefix = (prefix) => ((prefix || '')   '/').replace(/^\/ /, '/').replace(/\/ $/, '/')

  // standard function to check store is present and of the correct type
  function checkStore() {
    if (!settings_.chunkSize) throw "You must provide the maximum chunksize supported";
    if (!settings_.prefix) throw 'The prefix must be the path of a folder eg /crusher/store';
    if (!settings_.tokenService || typeof settings_.tokenService !== 'function') throw 'There must be a tokenservice function that returns an oauth token';
    if (!settings_.fetcher || typeof settings_.fetcher !== 'function') throw 'There must be a fetch function that can do a urlfetch (url,options)';
    return self;
  }

  /**
   * @param {object} settings these will vary according to the type of store
   */
  self.init = function (settings) {

    settings_ = settings || {};
    // actually this is base url of the onedrive api
    settings_.store = 'https://api.onedrive.com/v1.0/drive/root:'

    // make sure we start and end with a single slash
    settings_.prefix = fixPrefix(settings_.prefix)

    // set default chunkzise for oneDrive (4mb)
    // onedrive supports simple uploads of up to 4mb 
    settings_.chunkSize = settings_.chunkSize || 4000000;

    // respect digest can reduce the number of chunks read, but may return stale
    settings_.respectDigest = bmCrusher.Utils.isUndefined(settings_.respectDigest) ? false : settings_.respectDigest;

    // must have a cache service and a chunksize, and the store must be valid
    checkStore();

    // initialize the fetcher
    fetcher_ = new bmCrusher.Fetcher(settings_).got

    // now initialize the squeezer
    self.squeezer = new bmCrusher.Squeeze.Chunking()
      .setStore(settings_.store)
      .setChunkSize(settings_.chunkSize)
      .funcWriteToStore(write)
      .funcReadFromStore(read)
      .funcRemoveObject(remove)
      .setRespectDigest(settings_.respectDigest)
      .setCompressMin(settings_.compressMin)
      .setPrefix(settings_.prefix);

    // export the verbs
    self.put = self.squeezer.setBigProperty;
    self.get = self.squeezer.getBigProperty;
    self.remove = self.squeezer.removeBigProperty;
    return self;

  };

  // return your own settings
  function getSettings() {
    return settings_;
  }


  /**
   * remove an item
   * @param {string} key the key to remove
   * @return {object} whatever you  like
   */
  function remove(store, key) {
    checkStore();
    const result = fetcher_(store   key, {
      method: 'DELETE'
    })
   
  }

  /**
   * write an item
   * @param {object} store whatever you initialized store with
   * @param {string} key the key to write
   * @param {string} str the string to write
   * @param {number} expiry time in secs .. ignored in drive
   * @return {object} whatever you like
   */
  function write(store, key, str, expiry) {
    checkStore();
    const result = fetcher_(store   key   ':/content', {
      payload: str,
      method: 'PUT',
      contentType: "application/json"
    })
   
    if(!result.success) {
      throw new Error()
    }
    return result.data;
  }

  /**
   * read an item
   * @param {object} store whatever you initialized store with   
   * @param {string} key the key to write
   * @return {object} whatever you like
   */
  function read(store, key) {
    checkStore();
   
    const result = fetcher_(store   key)
   
    // we need to go back for the content
    if(result.success) {
      
      const dl = fetcher_(result.data['@content.downloadUrl'])
      
      if(!dl.success) {
        throw new Error(dl.extended)
      }
      return dl.content
    }
    return null
  }



}

One drive plugin

And that’s all there is to coding up the plugin. From now on the exact same methods introduced in Apps script library with plugins for multiple backend cache platforms will now operate on OneDrive instead of the other platforms. Here’s a refresher

Writing

All writing is done with a put method.

crusher.put(key, data[,expiry])

write some data

put takes 3 arguments

key – a string with some key to store this data against
data – It automatically detects converts to and from objects, so there’s no need to stringify anything.
expiry – optionally provide a number of seconds after which the data should expire.

Reading

const data = crusher.get (key)

retrieving data

get takes 1 argument

key – the string you put the data against, and will restore the data to it’s original state

Removing

crusher.remove(key)

removing an item

Expiry

The expiry mechanism works exactly the same as the other platforms. If an item is expired, whether or not it still exists in storage, it will behave as if it doesn’t exist. Accessing an item that has expired will delete it.

Here’s what some store entries look like on One Drive

Fingerprint optimization

Since it’s possible that an item will spread across multiple physical records, we want a way of avoiding rewriting (or decompressing) them if nothing has changed. Crusher keeps a fingerprint of the contents of the compressed item. When you write something and it detects that the data you want to write has the same fingerprint as what’s already stored, it doesn’t bother to rewrite the item.

However if you’ve specified an expiry time, then it will be rewritten so as to update its expiry. There’s a catch though. If your chosen store supports its own automatic expiration (as in the CacheService), then the new expiration wont be applied. Sometimes this behavior is what you want, but it does mean a subtle difference between different stores.

You can disable this behavior altogether when you initialize the crusher.


  const crusher = new CrusherPluginOneDriveService().init ({
    tokenService: ()=> goa.getToken(),
    prefix:'/crusher/store',
    fetcher: UrlFetchApp.fetch,
	respectDigest: false
  })

Always rewrite store even if the data has not changed

Formats

Crusher writes all data as zipped base64 encoded compressed, so the mime type will be text, and will need to be read by bmCrusher to make sense of it. However, there is probably a use case for the data to be kept in its original format so it can be used as a general One Drive storage client. I may add this at a later version if there’s a demand.