Posted by on Aug 16, 2016 in #Office365Dev | 0 comments

What is the Microsoft Graph?

If you haven’t heard, there is an easy way to call a great amount of Microsoft APIs – using one single endpoint. This endpoint, so called the Microsoft Graph (https://graph.microsoft.io/) lets you access everything from data, to intelligence and insights powered by the Microsoft cloud.

This is a part of my Microsoft Graph posts – to get started you will need to configure your application in either Azure AD or the new Application Registration Portal (converged app model v2.0). The Azure AD approach is well-tested and production-ready, whereas the new converged application model is still in preview – but it covers both Azure AD and Microsoft accounts. Pick the approach that suits your needs.

Authentication with Azure AD: http://simonjaeger.com/microsoft-graph-authentication-with-azure-ad/
Authentication with app model v2.0 (preview): http://simonjaeger.com/microsoft-graph-authentication-with-the-converged-model-preview/

What is Worksheet Protection?

Worksheet protection is the ability to protect worksheets located in an Excel workbook from different types of editing. This can be useful in a variety of scenarios, for instance when making sure that users can leverage pre-populated cells, formulas or other data – while not being able to accidentally impact it. This is great for user experience.

In the Microsoft Graph, there is a bunch of Excel operations, located within the Excel REST API (a part of the Microsoft Graph). The Excel REST API allows you to read and modify workbooks stored within the Microsoft Cloud (OneDrive, SharePoint, etc.). It’s extremely straight forward, and your natural starting point is always a drive item (http://graph.microsoft.io/en-us/docs/api-reference/v1.0/resources/driveitem).

In this blog post, we will take a look at the ability to protect a worksheet, but you can learn more about the Excel REST API and its many operations here: http://graph.microsoft.io/en-us/docs/api-reference/v1.0/resources/excel

How does it work?

Once you have the authentication piece in order, you can go ahead and get started. Be sure to include the Authorization header (with your access token) and set the Content-Type header to application/json. Also, make sure that your access token contains the required scopes to read and write to the drive items (files) (http://graph.microsoft.io/en-us/docs/authorization/permission_scopes).

First you will need to locate a drive item within the Microsoft Graph, which is an Excel workbook. Doing a simple GET on such an object (drive item) should get you something like this:

Now this is only properties of the drive item itself, but once you know this is an Excel workbook – you can go ahead and append /workbook to the previous URL and find yourself in the Excel REST API universe. Appending /workbook/worksheets will allow us to list all of the worksheets in the workbook. The response will look something like this:

To finally get into the protection of a worksheet, we would navigate logically with the previous URL of that worksheet and append /protection to it. This operation will return the current state of the worksheet protection, which should look something like this:

In the response you will find a bunch of different properties. Many of them are delicate ones (called options) such as allowing for inserting or deleting rows and columns, but the most apparent one is called protection. This boolean signifies whether the worksheet is protected or not. If it’s set to false, the options will not effect the worksheet.

In order to protect the worksheet you will append /protect to the previous URL and POST the above object with the desired configuration. The only addition (and removal of the protected property) is the password property, which is optional like the options property. The request would look something like this:

If the operation is successful, you’ll be returned with a 204 (No Content) status code. If the worksheet is already protected, the operation will fail with a 400 (Bad Request) status code.

To unprotect the worksheet, simply POST (with an empty request body) towards the previous URL – but with /unprotect appended instead of /protect. The operation will return a 204 (No Content) status code.

This feature is great if you’re creating or modifying Excel workbooks with the Microsoft Graph. If you want to see the Excel REST API in action, have a look at this Xamarin Native sample: https://github.com/microsoftgraph/xamarin-csharp-propertymanager-sample

You can also learn much more about the Microsoft Graph at: http://graph.microsoft.io/en-us/

-Simon Jaeger