>> 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.