Converting and copying Google Apps files to Microsoft OneDrive

In Using Advanced Drive service to convert files I covered how to use the Advanced Drive Service to convert Sheets to Excel and write the result back to Drive. It occurred to me that, since Microsoft OneDrive has a pretty good API too, I might as well do what you probably might want to do in the end - namely convert an Apps file to its Office equivalent, and put the result directly on OneDrive.

The OneDrive API uses OAUTH2, just like Google APIS do. In fact I covered how to do this in Microsoft Live service for Goa Examples using the Goa library to help out with the OAUTH2 dance.  I'll go through that again, specifically for this example, at the end of the article.



Setting things up on OneDrive

I recommend you create folders on OneDrive to receive the files you'll be copying over there. I'm using /Documents/conversions/ for all of mine. I don't create this automatically (although I could) as it's better to have a double check that things are going to the place you want them to. 

Next you need to make a list of the file ids on Google Drive that need converted, and what to convert them to. You'll notice that I'm not using the complex mimetypes to specify this, but the more friendly app names. Here I'm converting sheets, docs and presentation files. I think it's self explanatory what's going on here.
var filesToConvert = [{
  "id":"1-9A9c8GJAnh7MtKSHmuUmC7dVeogAtKnpCfwOWsSOuM",
  "to":"powerpoint",
  "folder":"/Documents/conversions/"
}, {
  "id":"165pHbC6MLbhVdPd2577eS1CKpygtjuFhRHxwoPRueT8",
  "to":"word" ,
  "folder":"/Documents/conversions/"
}, {
  "id":"1TxZ9Ut5VIOpJF_Zf2KwFtbup4RWCV_8rsCBz4Gkv6SU",
  "to":"excel",
  "folder":"/Documents/conversions/"
}];

List of known conversions

I've set up these short names for conversions. They are of course specific to certain types of input files (sheets to excel for example), and will fail if the conversion type is not supported (say,  slides to csv), but in any case, I may add more to this over time if there's a demand.
var MIME_TYPES = {
  slides:'application/vnd.google-apps.presentation',
  powerpoint:'application/vnd.openxmlformats-officedocument.presentationml.presentation',
  pdf:'application/pdf',
  word:'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
  text:'text/plain',
  docs:'application/vnd.google-apps.document',
  richText:'application/rtf',
  openDocument:'application/vnd.oasis.opendocument.text',
  html:'text/html',
  zip:'application/zip',
  openSheet:'application/x-vnd.oasis.opendocument.spreadsheet',
  excel:'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
  csv:'text/csv',
  sheets:'application/vnd.google-apps.spreadsheet'
};

Main function

This function works through the list of files to be converted, does the work and logs a link to the file on OneDrive in case you want to do something with it.
/**
* does the work of the script
* the webapp will have been run once already
* in order to generate the necessary token
*/
function convertAndCopy () {

  // now convert each file and write to oneDrive
  filesToConvert.forEach  (function (d) {
    
    // convert the file type
    var convertedFile = convertFile (d);
    
    // write it to onedrive
    var oneDriveFile = copyToOneDrive (convertedFile);
    
    // show the url for accessing it on onedrive    
    Logger.log (oneDriveFile.webUrl);
    
  });
  
}

Converting the files

This converts the file to the chosen format.
/**
* convert a list of files to requested type
* @param {object} conversion the id & requested types
* @return {object} the conveted blobs abns various meta information
*/
function convertFile (conversion) {
  
  
  // get the files metadata
  var meta = Drive.Files.get (conversion.id);
  if (!meta) {
    throw 'Couldnt get file id ' + conversion.id;
  }
  
  // check that we know how to convert that
  if (!MIME_TYPES[conversion.to]) {
    throw 'dont know how to convert to ' + conversion.to;
  }
  
  // and that this kind of file supports it
  var exportLink = meta.exportLinks[MIME_TYPES[conversion.to]];
  if (!exportLink){
    throw meta.title + '(' +
      meta.mimeType + ') ' +
        ' cannot be converted to ' + conversion.to;
  }
  
  // now get the blob
  return {
    conversion:conversion,
    meta:meta,
    toType:MIME_TYPES[conversion.to],
    outputName:conversion.folder + meta.title + exportLink.replace(/.*\&exportFormat=(\w+).*/,'.$1'),
    blob:UrlFetchApp.fetch ( exportLink, { 
      headers: {
        Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
      }
    }).getBlob()
  };
  
}


Copying the files to OneDrive

Finally this copies each file to OneDrive to the specified folder.
/**
* copy a converted file to oneDrive
* @param {object} file the converted file object
* @return {object} the oneDrive response
*/
function copyToOneDrive (file) {
  
  var oneDriveBase = 'https://api.onedrive.com/v1.0/'
  return JSON.parse( 
    UrlFetchApp.fetch (
      'https://api.onedrive.com/v1.0/drive/root:' + 
       file.outputName + 
      ':/content' , { 
          contentType:"text/plain", 
          headers: {
             Authorization: 'Bearer ' + getGoa().getToken()
          },
          payload:file.blob.getBytes(),
          method:"PUT"
       }).getContentText());

}

Note.....

If you get an error about Drive being undefined, it means you still haven't done this: 'you'll need to authorize the Advanced Drive service in Apps Script and in the developer console', so go and do that now. It's in the resource menu.

After running convertAndCopy, here are my files on OneDrive, all nicely converted to Office format!




Setting up Oauth2 

The first step then is to get some credentials from the Microsoft developer console. It's a little primitive compared to its Google equivalent, and one problem is that it only allows one set of credentials per application, and also checks the originating url is not being used by another application - so since all apps script apps originate from script.google.com, that can be a little limiting. But in any case - it's workable. 

For details about how Goa works, you can go to Goa Setup.The library, cGoa, is available under this project key, or on github. 

MZx5DzNPsYjVyZaR67xXJQai_d-phDA33

Next we need to commit the credentials to a property store. I'm assuming that any app you create would be published as the user accessing the app, and therefore would need a doGet() to initiate an authorization dialog. However, once this is done once, it will automatically refresh tokens so your main app can be triggered or otherwise run without a doGet. 

In any case - here's how to setup your one time credentials - just substitute in the ones you get from the Microsoft developer console

/**
* 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 : "0xxxxx73",
    clientSecret : "N-xxxxM",
    scopes : ["wl.signin","wl.offline_access","onedrive.readwrite"],
    service: 'live',
    packageName: 'onedrivescripts'
  });
  
}

Next you'll need to authorize the Google Advanced Drive service through the Apps Script menu (remember to authorize in the developer console as part of that too).

Finally, you'll need a doGet pattern to get authorized with Microsoft - so you can use the code below exactly as written, publish the app -  and run it. The first authorization dialog will look like this, so copy the redirect URI to the MS dev console before continuing. The origin should be set to be script.google.com. 



That should take you into a Microsoft authorization dialog which will give the script authorization to access your Drive, and then we can forget all about this stuff.

Here's the pattern to do all that you can use. It doesn't need any changes.   
function doGet (e) {
  return doGetOneDrive (e)
}

/**
* in case you need to reauthorize
*/
function killPackage () {
  getGoa().kill();
}

/**
* returns the goa infrastructure for this script
* @return {Goa} the executed goa object
*/
function getGoa (e) {
  var userPropertyStore = PropertiesService.getUserProperties();
  
  // this tells it where the credentials are stored
  return cGoa.GoaApp.createGoa (
    'onedrivescripts',
    userPropertyStore
  ).execute(e);
  
}

/**
* this is how  to do a webapp which needs authentication
* @param {*} e - parameters passed to doGet
* @return {HtmlOurput} for rendering
*/
function doGetOneDrive (e) {
  
  // this is pattern for a WebApp.
  // passing the doGet parameters (or anything else)
  // will ensure they are preservered during the multiple oauth2 processes
  var scriptPropertyStore = PropertiesService.getScriptProperties();
  var userPropertyStore = PropertiesService.getUserProperties();
  
  // this starts with a package copy for a specific user if its needed
  cGoa.GoaApp.userClone(
    'onedrivescripts', 
    scriptPropertyStore , 
    userPropertyStore
  );
  
  // this tells it where the credentials are stored
  var goa = getGoa();
  
  // handling the callback is done automatically
  // you must have this code in to provoke that
  if (goa.needsConsent()) {
    return goa.getConsent();
  }
  
  // now return the evaluated web page
  return HtmlService.createHtmlOutput (
    createWebPage()
  )
  .setSandboxMode(HtmlService.SandboxMode.IFRAME);
  
}

function createWebPage () {
  
  return '<h1>Authorization complete</h2>' +
    '<p>Now you can go ahead and use this script ' + 
      'with no further web interaction required</p>';
  
}

Now we're talking to OneDrive, and we can go and do the real work.

For more like this, see OAuth2 for Apps Script in a few lines of code and 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. 

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, All formats are available 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