Excel APIs in Microsoft Graph

Excel APIs in Microsoft Graph


>>Hello, welcome
to Microsoft Build. My name is Sudhi, and I’m a Program Manager in
the Microsoft Office team. In this session, I’ll give
a quick introduction about Excel Service APIs in
a way that can help your solutions take advantage of Excel’s calculation power. I’ll walk through a helpful demo, and show resources for
you to learn and engage. Excel is one of the most popular
productivity application that users rely upon to store, and analyze business
relevant data. Excel, being part
of Microsoft Graph, plays an important role in the overall Office style
up at ecosystem. You can create web
and mobile apps that reads and writes
data to Excel files, located in the Office 365, using the web development
tools of your choice. Excel REST APIs
are available over Drive API and requires files read and write permission
to perform operations. The APIs are designed
for easy access to objects such as range,
table and charts. So, which Excel files
can be accessed? Any Excel files stored on
your OneDrive for business, a SharePoint, a group site
can be targeted. We’re working hard to support OneDrive consumer platform soon. In short, if it can release
the file using Drive API, you’ll be able to access it
through the Excel REST APIs. Here are some of
the sample API calls. The API supports persistent or right mode and an analysis mode that doesn’t save your actions. This is determined by the type
of session they create. Session APIs are important to
help with the performance. So, don’t forget to use
this in your application. Simply pass the value along
in a history to be heard of. The second sample shows reading of a
worksheet collection, and the last sample shows worksheet payment function
being called. You can learn a lot more at the Microsoft Graph
documentation page, and following us on our social channels to post your questions on StackOverflow. Now, I’ll switch to a demo
to show a sample scenario. This is inspired by a developer question that was posted on StackOverflow forum, regarding the need to read, select rows in a table
based on the criteria. Well, we provide table
filter operations to achieve this scenario. Let’s see how this is done. Here, I’m showing
an Excel spreadsheet that contains data of cities containing
the highest population. It also shows the country, and the year in which the population
measurements were taken. Now, what if I want to only analyze cities belonging
to certain countries? In Excel, you can do that by simply applying filter on the column that you
are interested in. If you want to do
this using the APIs, there are two ways to do it. One, you can read through
each of the rows, one by one, and on the client side, apply a filter on the select countries that
you wish to operate on. Well, that can be quite taxing, and then it can affect
the performance. Excel offers filter APIs which is a second and probably
the better way of doing, which is to apply filter on the columns that you
are interested in. This is simply done just
as you were doing the UI by applying filter action
on the third column, which is the country column. Now, I’ll switch over
to the code just to show how this sample
would look like. For one, you would
apply values filter, which is an action on the column that you
wish to operate on. So, you would post
simply on the column, and call on the filter resource, and apply the action
values filter by passing the values of countries
that you want to filter on. In this case, I’ve
selected two countries and I’m supplying that
as an array parameter, and the whole part looks
something like this. So, have the Excel file
that I’m interested in, with the workbook,
tables collection, on the table one which is
the table I’m operating on, and I’m selecting
the third column, which happens to be
the country column, the ID is value number three, and I’ll apply value filter. Now, let’s go to Microsoft Graph Explorer
to see how this works. So, I’m going to copy
this value of the URL, and also copy in the JSON body
with the filter values, and I’m going to
execute this API. I’ll switch over to the Excel
spreadsheet and refresh, and you’ll see that the country filter would
have taken effect. Now, this spreadsheet also has year in which
the measurement was taken. Now, the user has
marked the rows with yellow values and in
the column year measurement, indicating that the values are somewhat odd because
you’ll see that anything that was measured
recently doesn’t have this yellow
background color. Now, what if you wish to also apply a filter
based on the color. Just as it would in the UI, the APIs also allows
you to do that. So, let’s go ahead and do that, and before I do that, I’ll show how to actually
clear the values, because depending
on your scenarios, you may want to apply
multiple filter values or you may want to
clear out and reapply. In this case, I simply call upon the clear action
on the same column, and I will execute that API, and I’ll come over
and refresh the view, and you will see that the filter
that I had applied on the country will not be removed. Now, lets go ahead and see how the application of
cell color filter is applied. Now, the syntax looks
pretty much similar, except in this case, I’m switching to column five
which is the year column, and I’m calling
the apply cell color filter by calling the yellow color
in the JSON body. I’m going to copy
the actual URL path showing the whole action path, I’m going to run this query. I’ll switch on and
refresh the view, and you’ll see that
only the cells that have yellow color would be filtered out as it’s shown here. Well, all this is great. Now, what if I want to only read the rows where this filter
has been applied? You can do that by reading the underlying range values
on the filtered rows, and you can do that by calling upon the visible view function
on the underlying range. In this case, I’m applying
the visible view function on the range object for the column that
I’m interested in. Now, you can also apply this to the entire table and
read all the row values. So, let’s see how
this looks like. Unlike in the previous cases, this is a git action, and I’m going to execute this, and you will see that
the return values where now I selected
only the values of the range of the visible rows, and I see only the rows in which the cells were highlighted
with the yellow color. Hope this gets you
interested in exploring more about Excel APIs.
Thank you for watching.

2 thoughts on “Excel APIs in Microsoft Graph”

Leave a Reply

Your email address will not be published. Required fields are marked *