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.