Use Microsoft Excel API with Selenium. Just like what we have seen in the Google Sheets API Integration post, we can also expand our automation capabilities by using Microsoft Excel API with Selenium online by incorporating Microsoft Graph API’s.
Table of Contents
The Beginning!
First thing first, We need elevated license (premium) for Azure AD in order to implement this on your JAVA project , since it requires Azure admin delegate permissions, we need to be having a delegated access level; I know it is really annoying when compared to Google authentication flows, here they made it inaccessible for the personal accounts holders 🙁
Worry not , we can use the Microsoft Graph Explorer UI to try out these delegated requests from UI.
Sign up Azure AD and create app integration
- Navigate to the Azure portal.
- Login using Microsoft 365 account/personal account.( In my case, I am using my personal account).
- Navigate to Azure Active Directory.
- Click Add > App registration
- Enter a sample application name and select Accounts in any organizational directory
Do not select Personal Microsoft accounts only, since it is exclusive for personal use, we can’t invoke any API requests from background service with this scope.
- Click Register button
- Now the test app would be successfully created in your directory(Tenant), Make sure to copy Client ID and Tenant ID for future use.
Generating Client Credentials
- Click Add a certificate or secret to create client credentials for our app usage.
- Provide appropriate description and select the Expiry time for the client secret.
- Click Add button to Add the Client Secret to your app.
- After adding the client secret, it will display the new record as shown in the above screenshot, Make sure to copy the secret value from this page before navigating away, it will be displayed only once and if we miss to copy this one, we have create another client secret.
Setting API Permission for the daemon service
- Now, In order to set the API permissions for our resource usage, we have to navigate to API Permissions > Add a permission page.
- Under Microsoft APIs tab, click Microsoft Graph as our API Provider.
- Now, we have to choose Application permissions as our designated choice as we have to invoke the API’s without the need of signed-in user.
- Now type File in the Select permissions search bar, you would be populated with the following permissions, select the below options
- Files.Read.All
- Files.ReadWrite.All
Attention Please!
In the below screenshot, you can see that under Admin consent required column, the selected permissions requires Admin consent, which means an admin authority is needed before making any request to those resources, Ultimately this can be done only while using Azure organization admin access.
Code Walkthrough – Use Microsoft Excel API with Selenium
Here is my github project for your reference https://github.com/vigneshram20/MicrosoftExcelOnlineAPIDemo
Clone the project into your local and do the necessary changes as explained below.
Paste your Tenant ID, Client ID, Client Secret in application.properties file
- Create new Microsoft Spreadsheet file in your OneDrive account
Copy the spreadsheet id from the url
onedrive.live.com/edit.aspx?resid=*******************&nd=1
**** from the above URL part is your spreadsheet id.
- Paste your SpreadSheetID & Tenant ID in the below java file.
That’s it , Now all left is to run the Java project and see the text values from the online Excel Sheet in the Console.
Remember, This API call will give us the results only when the logged in user is either a premium license holder / corporate license user with elevated access level, otherwise it will throw you 401 unauthorized access
Then, How do we try it out ?
Since the API’s are accessible only when we delegate the permissions from an admin, for now as a work around we can use the Microsoft Graph Explorer UI to execute the API’s manually for personal accounts. This great tool is provided by Microsoft itself to try the API’s online.
Execute API using Microsoft Graph API Explorer
- Navigate to https://developer.microsoft.com/en-us/graph/graph-explorer
- Login using your personal account.
- Find the GET API call used range in worksheet from the left Sample queries panel.
- Click the GET button.
- Modify the Spreadsheet ID in the place highlighted below.
- Click Modify permissions (preview) button.
Type the below permission and Click Consent button to delegate the permission access to the Microsoft Graph API Tool.
Files.ReadWrite.All
You will be populated with Grant Permission popup, Click Yes to allow .
- Now Click Run query button from the explorer, The output response is successfully displayed in the Response preview console.
By this way we can easily integrate Microsoft Excel API’s with our Selenium or any other background service applications. Do comment and let me know if you have any doubts or difficulties in implementing this on
Thanks for reading! 🙂
