Excel REST API (Public preview)

We look at a small demo that shows the interaction with Excel document using these api’s and finally I’ll point you to some useful resources on the web. I’r excited to announce that we are adding brand new REST API that lets you develop.

Applications that read and modify data and Excel workbook these ApS can be used to build new class of web and mobile applications that take advantage of analytical and visualization power of Excel the Excel REST. Api swil be supported for Excel files stored in onedrive business or on a SharePoint Online site. The new API s are going to be available as part of the Microsoft graph API S, which is a single endpoint to access many, the office 365 services.

To begin with, we’re going to be releasing this as part of the beta version, the excel files can be addressed or files API by adding an additional URL segment at the end called workbook. This does the service that you’re in working an Excel API. An example URL is shown here. This addresses worksheets collection of the Excel document, notice that we are using files API to identify the target file and, following that with workbook segments and worksheets collection resource in terms of the functionality.

These API s, we are introducing allows state access through objects such as worksheets tables range chart and named items. Some of the actions these API support are creation and management of worksheets tables and charts, read and update range data formula number formats, formatting of range chart and tables downloading of chart is also supported. As an image, you have the option of saving your updates to the workbook, or you may simply wish to operate on the data and do analysis without saving the file.

All operations are based on open standards such as our 2.0 rest or data, and uses JSON format. For both input and output, now, let’s focus on the initial steps as a first step register your app in Azure portal, while setting up your app permissions include necessary permissions to read and write the files, follow the OAuth flow and get access token and finally use Microsoft. Graph files API to address the target file and add workbook URL segment at the end of the URL to invoke Excel API.

So let’s look at the demo, I’m going to show the demo in two parts. First I’ll use a simple web application to showcase how an app can interact with Excel data stored remotely. Secondly, I’ll run through some aps manually for the purpose of this demo. I’ll bypass odd flows and access internal bills, endpoint directly in the real world, you’ll be accessing api’s through Microsoft, graph endpoint using OAuth flow.

Now, what you’re looking at is an excel file stored online. This contains lists of tasks and priority and status and person completion and some date information. I’r going to pin this on one side and what you’re, seeing on the left side is a web application that is reading and writing to the same excel file that we just looked at. Let’s go ahead and add a new task. I’r going to choose some values here and insert a row as you can see.

As soon as I created that row in the application, the excel file got updated and essentially the application added a new row to the existing table using the same template that the application understood now. Wouldn’t it be great if we can visualize the tasks using the native excel chart feature now that all of these tasks have status? Maybe you one other question that you might have is: what is the breakup of these tasks based on the type of status? The application provides the link that goes and fetches an existing chart in the spreadsheet and download start as an image and displays it on the application.

This is a great way to use the analytical power of Excel and display the content in your mobile or web application. Now that’s the demo using the application. Now, let’s look at some of these requests manually now, I’m going to just do a couple of things onto the same spreadsheet that we are looking at I’m going to add a new worksheet and update range on top of that newly created worksheet. Just to give you a flavor of what the syntax looks like I’m going to use fiddler in order to do this and fiddler is a tool using which you can interact at the protocol level.

I’r going to go to the composer and to begin with, I’m going to add a new worksheet. So I’m going to use the worksheet address the worksheets collection and provide a JSON body stating that I want this sheet to be created which the with the name sheet 3. Now I get a 201 and on the right hand, side you can see that a new worksheet called sheet 3 got inserted. This was just a brief introduction, look for new features and enhancements, as we add, more excel api’s.

Your feedback is important to us, so please send us your comments through the listed support blogs. These pages provide important resources to help, learn more and ask questions. Thank you for reading this session and happy coding.