Excel 365 Connector v1.0

1. Prerequisites and Additional Steps and Helper Utilities

1.1 Excel App setup on Azure Portal

Login into your Microsoft Azure account with your correct credentials.

New App Registration

Go to the App registration screen and click on the New Registration button:

Fill out the new app form with the required details and click on the Register button.

After creating a new app, the page is redirected to the App overview page.

Create New Credentials

Now click on the Client Credentials button to create a new client secret. Click on the New Client Secret button.

Provide the description and validity of that secret value. Click on the Add button.

Important: After successfully creating the secret, it shows the secret ID and value. Please note this secret value for later use.

This secret value will be used for OAuth integration purposes. When creating the connection on OneTeg, the Value will be used as the Client Secret and the Application (Client) ID will be used as Client ID on OneTeg

Adding API permissions

Go to the API permission screen for adding the required permissions.

Click on Add a permission button and select the Microsoft Graph option:

Select the Microsoft Graph option:

Now select the delegated permissions:

Choose relevant permissions(For reading & writing Excel files select all files permissions) and click on Add permission.

After adding permission, click on the Grant admin consent button.
(This action can only be applied by the Azure Admin)

Configure Platform, Authentication Type & Callback URL

Select the Authentication section from the left menu and click on Add a platform button:

Choose the Web platform and provide the redirect URL:

Next, enter the Callback / Redirect URIs.

Click on the Configure button.

Choose other settings shown below:

Expose an API

Go to the Export an API section from the left side menu and click on Add a scope button.

Click on save and continue

Provide the required details and choose “Admin and Users” as consent:

Scope will be added successfully:

1.2 Graph Explorer Configuration

Step 1:

Go to Excel | Microsoft 365 (office.com) and create a new workbook. Be sure that both the Excel 365 and Graph Explorer websites are opened with the same user account.

Step 2:

Go to Azure Portal Register a client application in Azure Active Directory for the Azure Health Data Services | Microsoft Learn and follow all the steps in registering the application and save the client id, tenant id and other required stuff.

Note: Please set the correct redirect URI in the application.

2. Connector Actions & Tests

Action Name / Method	Get Worksheet
Action Description	Get sheet from workbook
Test Case	Get the sheet data from workbook
Request Body Name	Parameter	Type	Description (Example)
	Sheet Name	String	Sheet1
	Workbook Id	String	Get the workbook id from List Workbook Step

Action Name / Method	Add New Worksheet
Action Description	Add new worksheet to workbook
Test Case	Add a new worksheet to workbook
Request Body Name	Parameter	Type	Description (Example)
	New Sheet Name	String	My Sheet
	Workbook Id	String	Get the workbook id from List Workbook Step

Action Name / Method	Get Tables from Worksheet
Action Description	Get tables from worksheet
Test Case	Get tables data of worksheet
Request Body Name	Parameter	Type	Description (Example)
	Sheet Name	String	My Sheet
	Workbook Id	String	Get the workbook id from List Workbook Step

Action Name / Method	Get Charts from Worksheet
Action Description	Get charts from worksheet
Test Case	Get charts data of worksheet
Request Body Name	Parameter	Type	Description (Example)
	Sheet Name	String	My Sheet
	Workbook Id	String	Get the workbook id from List Workbook Step

Action Name / Method	Get Used Range from Worksheet
Action Description	Get used range from worksheet
Test Case	Get used range data of worksheet
Request Body Name	Parameter	Type	Description (Example)
	Sheet Name	String	My Sheet
	Workbook Id	String	Get the workbook id from List Workbook Step

Action Name / Method	Add Table in Worksheet
Action Description	Add table in worksheet
Test Case	Add table in a worksheet
Request Body Name	Parameter	Type	Description (Example)
	Cell Reference	String	A1:D1
	Sheet Name	String	My Sheet
	Has Headers	Boolean	true
	Workbook Id	String	Get the workbook id from List Workbook Step

Action Name / Method	Add Column in Table
Action Description	Add column in table
Test Case	Add column in a table
Request Body Name	Parameter	Type	Description (Example)
	Column Value	String	Test
	Table Name	String	Table1
	Workbook Id	String	Get the workbook id from List Workbook Step

Action Name / Method	Add Row in Table
Action Description	Add row in table
Test Case	Add row in a table
Request Body Name	Parameter	Type	Description (Example)
	Column Value	String	Test
	Table Name	String	Table1
	Workbook Id	String	Get the workbook id from List Workbook Step

Action Name / Method	Delete Table
Action Description	Delete table from worksheet
Test Case	Delete a table from worksheet
Request Body Name	Parameter	Type	Description (Example)
	Table Name	String	Table1
	Workbook Id	String	Get the workbook id from List Workbook Step

Action Name / Method	Delete Worksheet
Action Description	Delete worksheet from workbook
Test Case	Delete a worksheet from workbook
Request Body Name	Parameter	Type	Description (Example)
	Sheet Name	String	Sheet1
	Workbook Id	String	Get the workbook id from List Workbook Step

Action Name / Method	List Rows
Action Description	Get all rows data from worksheet
Test Case	Get all rows data of a worksheet
Request Body Name	Parameter	Type	Description (Example)
	Table Name	String	Table1
	Skip Rows	Integer	1
	Top Rows	Integer	1
	Workbook Id	String	Get the workbook id from List Workbook Step

Action Name / Method	List Columns
Action Description	Get all columns data from worksheet
Test Case	Get all columns data of a worksheet
Request Body Name	Parameter	Type	Description (Example)
	Table Name	String	Table1
	Skip Columns	Integer	1
	Top Columns	Integer	1
	Workbook Id	String	Get the workbook id from List Workbook Step

Action Name / Method	List Workbooks
Action Description	Get all the workbooks data.
Test Case	Get Workbook Id and used in another step.
Request Body Name	Parameter	Type	Description (Example)

3. Connection Settings and Configuration

Settings Parameters	Value	Description	Required (Y/N)	Default Value
Connector	Excel Office 365	Connector Name	Y	Excel Office 365
Authentication	OAUTH	Authentication Type	Y	OAuth
Client ID	Application Client ID	Client ID from Excel 365 OAUTH credentials.	Y	-
Client Secret	Application Client Secret	Client Secret from Excel 365 OAUTH credentials.	Y	-
Token URL	https://login.microsoftonline.com/common/oauth2/v2.0/token	Token URL required for OAuth	Y	https://login.microsoftonline.com/common/oauth2/v2.0/token
Authorization URL	https://login.microsoftonline.com/common/oauth2/authorize	Authorization URL required for OAuth	Y	https://login.microsoftonline.com/common/oauth2/authorize
Scopes (Comman seperated)	offline_access https://graph.microsoft.com/.default	Scopes required for OAuth and performing CRUD on Excel APIs. Note: offline_access is mandatory for refresh token	Y	https://graph.microsoft.com/.default
Endpoint	https://graph.microsoft.com/v1.0	Endpoint required for performing CRUD on Excel APIs.	Y	https://graph.microsoft.com/v1.0
Response Types	code