Using OneDrive and Excel APIs in the Microsoft Graph for App Storage
The Microsoft Graph is constantly evolving with new and powerful endpoints. If you want a glimpse into current engineering investments and future of the Graph, take a look at the latest developments on beta branch. One of the beta endpoints I'm particularly excited for is the new Excel APIs. The Excel APIs allow you to perform advanced manipulations of Excel remotely via REST calls into the Microsoft Graph. I recently recorded an Office Dev Show on Channel 9 discussing these APIs. I found it incredibly easy to manipulate worksheet data using the Excel APIs. So much so, that I thought I would try to use Excel and OneDrive as the data layer for a mobile application. In this post, I'll illustrate how to perform CRUD operations on worksheet data using the Excel APIs in the Microsoft Graph. I'll also discuss a few patterns for working with files in OneDrive for Business and provisioning application assets at run-time.
NOTE: The sample used in this post is built with Ionic2/Angular2/TypeScript, but the patterns and API end-points apply to any language platform.
Video showcase of solution:
[embed]https://www.youtube.com/watch?v=InKtB447zFM[/embed]
Ensuring App Resources
If OneDrive for Business and Excel will serve as the data layer for my application, I need to ensure the appropriate files and folders are configured each time a user launches the application. OneDrive already provides a special folder for each custom application to store app-specific resources. These special app folders get provisioned on-demand with the app's name to the "Apps" folder of the drive (ex: Apps/MyExpenses). You aren't limited to working in this folder, but it keeps things clean for app-specific files and consistent with competitors like Dropbox. You can reference your application's special folder by referencing the endpoint /drive/special/approot. My expense application uses this special folder to store receipt images captured by the application and an Expenses.xlsx file that it used for store all application data (more on this later). Below is the TypeScript I use to check for the existence of Expenses.xlsx.
ensureConfig ensures the Expenses.xlsx is provisioned:
//ensures the "Expenses.xslx" file exists in the approot
ensureConfig() {
let helper = this;
return new Promise((resolve, reject) => {
helper.authHelper.getTokenForResource(helper.authHelper._graphResource).then(function(token: Microsoft.ADAL.AuthenticationResult) {
helper.http.get('https://graph.microsoft.com/v1.0/me/drive/special/approot:/Expenses.xlsx', {
headers: new Headers({ 'Authorization': 'Bearer ' + token.accessToken })
})
.subscribe(res => {
// Check the response status
if (res.status === 200) {
helper.workbookItemId = res.json().id;
window.localStorage.setItem('CACHE_KEY_WORKBOOK', helper.workbookItemId);
resolve(true);
}
else {
//create the files
helper.createWorkbook().then(function(datasourceId: string) {
helper.workbookItemId = datasourceId;
window.localStorage.setItem('CACHE_KEY_WORKBOOK', helper.workbookItemId);
resolve(true);
}, function(err) {
reject(err);
});
}
});
}, function(err) {
reject(err); //error getting token for MS Graph
});
});
}
Provisioning the Workbook
If the Expenses.xlsx does not exist, the mobile application will provision it on-demand (creating it's own datasource...pretty cool). For this, I decided to store the Excel workbook template IN the Cordova mobile application. If is it determined that Expenses.xlsx file doesn't exist, the application will read this template and provision it in OneDrive for Business. The function below illustrates this provisioning. One important note below...the Angular2 documentation for http.put indicates that the body can be any object. However, my testing determined it only supports a string body right now which is not appropriate for binary content of an upload. For this reason, I'm using an XmlHttpRequest instead. Angular2 is still in beta, so hopefully this will be fixed before final release.
createWorkbook provisions the Expenses.xslx file when it does not exist in OneDrive:
//creates the "Expenses.xslx" workbook in the approot folder specified
createWorkbook(folderId: string) {
//adds a the workbook to OneDrive
let helper = this;
return new Promise((resolve, reject) => {
//get token for resource
helper.authHelper.getTokenForResource(helper.authHelper._graphResource).then(function(token: Microsoft.ADAL.AuthenticationResult) {
//reference the Excel document template at the root application www directory
window.resolveLocalFileSystemURL(cordova.file.applicationDirectory + 'www/Expenses.xlsx', function (fileEntry) {
fileEntry.file(function (file) {
//open the file with a FileReader
var reader = new FileReader();
reader.onloadend = function(evt: ProgressEvent) {
//read base64 file and convert to binary
let base64 = evt.target.result;
base64 = base64.substring(base64.indexOf(',') + 1);
//perform the PUT
helper.uploadFile(base64, 'Expenses.xlsx').then(function(id: string) {
resolve(id);
}, function(err) {
reject(err);
});
};
//catch read errors
reader.onerror = function(err) {
reject('Error loading file');
};
//read the file as an ArrayBuffer
reader.readAsDataURL(file);
},
function(err) {
reject('Error opening file');
});
}, function(err) {
reject('Error resolving file on file system');
});
}, function(err) {
reject(err); //error getting token for MS Graph
});
});
}
CRUD Operations with Excel
CRUD operations with the Excel APIs are relatively easy if you understand the data format Excel expects (multi-dimensional array of values). Retrieving data is accomplished by performing a GET on the rows of a specific table (ex: /drive/items/workbook_id/workbook/worksheets('worksheet_id')/tables('table_id')/rows)
getRows function retrieves rows from the Excel workbook:
//gets rows from the Expenses.xslx workbook
getRows() {
let helper = this;
return new Promise((resolve, reject) => {
helper.authHelper.getTokenForResource(helper.authHelper._graphResource).then(function(token: Microsoft.ADAL.AuthenticationResult) {
helper.http.get('https://graph.microsoft.com/beta/me/drive/items/' + helper.workbookItemId + '/workbook/worksheets(\'Sheet1\')/tables(\'Table1\')/rows', {
headers: new Headers({ 'Authorization': 'Bearer ' + token.accessToken })
})
.subscribe(res => {
// Check the response status before trying to resolve
if (res.status === 200)
resolve(res.json().value);
else
reject('Get rows failed');
});
}, function(err) {
reject(err); //error getting token for MS Graph
});
});
}
Adding data to the worksheet uses the exact same endpoint as above, but with a POST. Also, the row data to add must be included in the body of the POST and formatted as a multi-dimensional array (ex: { "values": [["col1Value", "col2Value", "col3Value"]]}).
addRow function adds a row to the Excel workbook:
//adds a row to the Excel datasource
addRow(rowData: any) {
let helper = this;
return new Promise((resolve, reject) => {
helper.authHelper.getTokenForResource(helper.authHelper._graphResource).then(function(token: Microsoft.ADAL.AuthenticationResult) {
helper.http.post('https://graph.microsoft.com/beta/me/drive/items/' + helper.workbookItemId + '/workbook/worksheets(\'Sheet1\')/tables(\'Table1\')/rows', JSON.stringify(rowData), {
headers: new Headers({ 'Authorization': 'Bearer ' + token.accessToken })
})
.subscribe(res => {
// Check the response status before trying to resolve
if (res.status === 201)
resolve();
else
reject('Get rows failed');
});
}, function(err) {
reject(err); //error getting token for MS Graph
});
});
}
Updating a row in a worksheet is a little different than you might expect. Instead of referencing the table in Excel, you PATCH a specify range with new values (in the same multi-dimensional array format as add). Because updates (and deletes as you will see later) are performed against ranges, it is important to keep track of the row index of the data you work with.
updateRow function update a row in the Excel workbook (via Range):
//updates a row in the Excel datasource
updateRow(index:number, rowData:any) {
let helper = this;
return new Promise((resolve, reject) => {
helper.authHelper.getTokenForResource(helper.authHelper._graphResource).then(function(token: Microsoft.ADAL.AuthenticationResult) {
let address = 'Sheet1!A' + (index + 2) + ':D' + (index + 2);
helper.http.patch('https://graph.microsoft.com/beta/me/drive/items/' + helper.workbookItemId + '/workbook/worksheets(\'Sheet1\')/range(address=\'' + address + '\')', JSON.stringify(rowData), {
headers: new Headers({ 'Authorization': 'Bearer ' + token.accessToken })
})
.subscribe(res => {
// Check the response status before trying to resolve
if (res.status === 200)
resolve();
else
reject('Get rows failed');
});
}, function(err) {
reject(err); //error getting token for MS Graph
});
});
}
Deleting a row is accomplished by performing a POST to a specific range with /delete tacked on to the end. You can also include instruction in the body on how the Excel should treat the delete. For CRUD operations, we want deleting to shift rows up, so {"shift", "Up"} is in the body of the POST.
deleteRow function deletes a row in the Excel workbook (via Range):
//deletes a row in the Excel datasource
deleteRow(index:number) {
let helper = this;
return new Promise((resolve, reject) => {
helper.authHelper.getTokenForResource(helper.authHelper._graphResource).then(function(token: Microsoft.ADAL.AuthenticationResult) {
let address = 'Sheet1!A' + (index + 2) + ':D' + (index + 2);
helper.http.post('https://graph.microsoft.com/beta/me/drive/items/' + helper.workbookItemId + '/workbook/worksheets(\'Sheet1\')/range(address=\'' + address + '\')/delete', JSON.stringify({ 'shift': 'Up' }), {
headers: new Headers({ 'Authorization': 'Bearer ' + token.accessToken })
})
.subscribe(res => {
// Check the response status before trying to resolve
if (res.status === 204)
resolve();
else
reject('Delete row failed');
});
}, function(err) {
reject(err); //error getting token for MS Graph
});
});
}
CRUD Operations with OneDrive
Nothing too special about working with files and OneDrive. However, I found the OneDrive documentation to be a little unrealistic and unhelpful as it shows operations on text files and not binary data. Binary data (ex: images and Office documents) introduces additional complexity (especially for a client-side application) so I thought I would document some of the utilities I wrote to work with files. Thanks to Waldek Mastykarz, Stefan Bauer, and Sahil Malik for advisement on upload...it wasn't working at first (turned out to be Angular2 bug), and they were very helpful. The getBinaryFileContents function is directly from Waldek's blog HERE.
uploadFile uploads a binary file to a specific location in OneDrive using PUT
//uploads a file to the MyExpenses folder
uploadFile(base64: string, name: string) {
let helper = this;
return new Promise((resolve, reject) => {
helper.authHelper.getTokenForResource(helper.authHelper._graphResource).then(function(token: Microsoft.ADAL.AuthenticationResult) {
//convert base64 string to binary
let binary = helper.getBinaryFileContents(base64);
//prepare the request
let req = new XMLHttpRequest();
req.open('PUT', 'https://graph.microsoft.com/v1.0/me/drive/special/approot:/' + name + '/content', false);
req.setRequestHeader('Content-type', 'application/octet-stream');
req.setRequestHeader('Content-length', binary.length.toString());
req.setRequestHeader('Authorization', 'Bearer ' + token.accessToken);
req.setRequestHeader('Accept', 'application/json;odata.metadata=full');
req.send(binary);
//check response
if (req.status === 201)
resolve(JSON.parse(req.responseText).id); //resolve id of new file
else
reject('Failed to upload file');
}, function(err) {
reject(err); //error getting token for MS Graph
});
});
}
deleteFile deletes a specific file using DELETE
//deletes a file from OneDrive for business
deleteFile(id:string) {
let helper = this;
return new Promise((resolve, reject) => {
helper.authHelper.getTokenForResource(helper.authHelper._graphResource).then(function(token: Microsoft.ADAL.AuthenticationResult) {
helper.http.delete('https://graph.microsoft.com/beta/me/drive/items/' + id, {
headers: new Headers({ 'Authorization': 'Bearer ' + token.accessToken })
})
.subscribe(res => {
// Check the response status before trying to resolve
if (res.status === 204)
resolve();
else
reject('Delete row failed');
});
}, function(err) {
reject(err); //error getting token for MS Graph
});
});
}
For images, I decided to take advantage of another beta API in the Microsoft Graph...thumbnails. The thumbnails API allow you to download small formats of an image in OneDrive. It will generate Small, Medium, and Large thumbnails for all images. I decided a medium thumbnail would look fine in my mobile application and be MUCH more performant.
loadPhoto loads the medium thumbnail for a specified image in OneDrive
//loads a photo from OneDrive for Business
loadPhoto(id:string) {
//loads a photo for display
let helper = this;
return new Promise((resolve, reject) => {
helper.authHelper.getTokenForResource(helper.authHelper._graphResource).then(function(token: Microsoft.ADAL.AuthenticationResult) {
//first get the thumbnails
helper.http.get('https://graph.microsoft.com/beta/me/drive/items/' + id + '/thumbnails', {
headers: new Headers({ 'Authorization': 'Bearer ' + token.accessToken })
})
.subscribe(res => {
// Check the response status before trying to resolve
if (res.status === 200) {
var data = res.json().value;
var resource = data[0].medium.url.substring(8);
resource = "https://" + resource.substring(0, resource.indexOf('/'));
helper.authHelper.getTokenForResource(resource).then(function(thumbtoken: Microsoft.ADAL.AuthenticationResult) {
//prepare the content request
let req = new XMLHttpRequest();
req.open('GET', data[0].medium.url, true);
req.responseType = 'blob';
req.setRequestHeader('Authorization', 'Bearer ' + thumbtoken.accessToken);
req.setRequestHeader('Accept', 'application/json;odata=verbose');
req.onload = function(e) {
//check response
if (this.status === 200) {
//get the blob and convert to base64 using FileReader
var blob = req.response;
var reader = new FileReader();
reader.onload = function(evt){
var base64 = evt.target.result;
base64 = base64.substring(base64.indexOf(',') + 1);
resolve(base64);
};
reader.readAsDataURL(blob);
}
else
reject('Failed to read image');
};
req.onerror = function(e) {
reject('Failed to download image');
};
req.send();
}, function(err) {
reject('Error getting token for thumbnail');
});
}
else
reject('Thumbnail load failed');
});
}, function(err) {
reject(err); //error getting token for MS Graph
});
});
}
Final Thoughts
The Excel APIs in the Microsoft Graph have capabilities well beyond CRUD operations for an app, but I thought this was an interesting pattern (especially for a guy that is always going over my monthly Azure quota). You can grab the MyExpenses solution written with Ionic2/Angular2/TypeScript at GitHub repo listed below:
https://github.com/richdizz/Ionic2-Angular2-ExcelAPI-Expense-App