# Excel
Microsoft Excel (opens new window) is a spreadsheet software program and a powerful data visualization and analysis tool.
Workato's Excel connector enables you to read data from Excel workbooks stored in OneDrive for Business, SharePoint sites, or shared Group drives.
SQL TRANSFORMATIONS FOR EXCEL FILE SOURCES
The Excel connector only supports working with files stored in SharePoint and OneDrive. For processing or transforming large-volume Excel files from other sources, such as Google Drive or SFTP, use SQL Transformations.
# API version
The Excel connector uses the Excel REST API v1 (opens new window).
# How to connect to Excel on Workato
Workato supports the following types of connections to Excel:
- Authorization code grant authentication (OAuth 2.0)
- Client credentials-based authentication (OAuth 2.0): Only available for tenant-specific connections
# Authorization code grant authentication (OAuth 2.0)
This authentication method requires the following value for tenant-specific account types:
- Tenant ID/Domain
# Step 1: Register the Workato App in Azure portal
Complete the following steps to register the Workato app and assign it permissions for authorization code grant connections.
Register the Workato app in the Azure Portal
Sign in to the Azure portal (opens new window).
Select App registrations > + New registration, under Azure services.
Enter a unique name for the application and select a Supported account type.
Select Web from the Select a platform drop-down menu.
Enter https://www.workato.com/oauth/callback
as the Redirect URI and select Register.
Register an app
Assign permissions to your app
In the navigation sidebar, select Manage > API permissions.
Click + Add a permission and select Microsoft Graph APIs.
Add permissions
Add the required permissions as outlined in the Permissions required to connect section. Depending on your connection type, you must assign Application or Delegated permissions.
Click Add permissions.
Admin consent is required for specific permissions. Refer to the Granting admin consent section to learn more.
Obtain the Directory (tenant ID) from the Azure portal
Go to the Overview > Essentials section.
App details
Copy the Directory (tenant) ID
for use in Workato.
# Step 2: Complete setup in Workato
Click Create > Connection.
Search for and select Excel
as your connection in the New connection page.
Provide a name for your connection in the Connection name field.
Use the Location drop-down menu to select the project where you plan to store the connection.
Use the Connection account type drop-down menu to select the type of account you plan to use. The available choices are Common and Tenant-specific.
Select Authorization code grant
as the Authentication type.
Optional. By default, the connector requests a set of scopes necessary for all triggers and actions to function properly. In the Advanced settings section, you can manually select the permissions instead. The minimum permissions required to establish a connection are Files.Read
and offline_access
. Workato always requests these permissions regardless of the permissions you select. Refer to the permissions section for more information.
Click Sign in with Microsoft.
# Client credentials-based authentication (OAuth 2.0)
COMPATIBLE AUTHENTICATION
Client credentials-based authentication is only compatible with tenant-specific connections.
This method requires the following fields:
- Tenant ID/Domain
- User ID
- Client ID
- Client Secret
# Step 1: Register the Workato App in the Azure Portal
Complete the following steps to register the Workato app and assign it permissions for client credentials-based connections.
Register the Workato App in the Azure Portal
Sign in to the Azure portal (opens new window).
Select App registrations > + New registration, under Azure services.
Enter a unique name for the application and select a Supported account type.
Select Web from the Select a platform drop-down menu.
Enter https://www.workato.com/oauth/callback
as the Redirect URI and select Register.
Register an app
Assign permissions to your app
In the navigation sidebar, select Manage > API permissions.
Click + Add a permission and select Microsoft Graph APIs.
Add permissions
Add the required permissions as outlined in the Permissions required to connect section. Depending on your connection type, you must assign Application or Delegated permissions.
Click Add permissions.
Admin consent is required for specific permissions. Refer to the Granting admin consent section to learn more.
Generate an API key
Go to Manage > Certificates & Secrets > Client secrets and click + New client secret.
Provide a Description for the client secret and specify an expiry date.
Click Add. Copy and save this secret for use in Workato.
Obtain the Application (client) ID, Object ID, and Directory (tenant) ID from the Azure Portal
Go to the Overview > Essentials section.
App details
Copy the Application (client) ID
, Object ID
, and Directory (tenant) ID
for use in Workato.
Obtain the User ID from the Azure Portal
To obtain the User ID
, go to Home > Users.
Select users
Search for and select the user associated with your account.
Copy the Object ID
. This value is used for the User ID in Workato.
Return to Workato to finish setting up your connection.
# Step 2: Complete setup in Workato
Click Create > Connection.
Search for and select Excel
as your connection in the New connection page.
Provide a name for your connection in the Connection name field.
Use the Location drop-down menu to select the project where you plan to store the connection.
Select Tenant specific as the Connection account type. This option is specifically designed for users who belong to a particular organization (tenant).
Tenant specific account connection type
Provide your Tenant ID/Domain. This is the Directory (tenant) ID
for your app. Refer to the Register an app in Azure section to learn how to obtain this value.
Select Client credentials
as the Authentication type.
Supply the User ID, Client ID, and Client secret for your app. Refer to the Register an app in Azure section to learn how to obtain these values.
Click Sign in with Microsoft.
# Permissions required to connect
# Default scopes for authorization code grant connections
By default, the Excel connector requests the following scopes for authorization code grant connections. These scopes are necessary to use all of this connector's triggers and actions. Additionally, you must assign these permissions to the Workato app as Delegated permissions in the Azure portal.
Permission | Description | Relevant action or trigger |
---|---|---|
Files.Read | Allows the app to read the signed-in user's files. | Get cells, Get rows, List tables, List worksheets and Search workbooks |
Files.ReadWrite | Allows the app to read and write to the signed-in user's files. | All actions in Files.Read and Add a table, Add a worksheet, Add rows in batch, Delete row and Update row. |
Group.Read.All | Allows the app to read files for all groups the signed-in user can access. | Get cells, Get rows, List tables, List worksheets and Search workbooks |
Sites.Read.All | Allows the app to read documents in all SharePoint site collections on behalf of the signed-in user. | Get cells, Get rows, List tables, List worksheets and Search workbooks |
Sites.ReadWrite.All | Allows the app to read and write to documents in all SharePoint site collections on behalf of the signed-in user. On the consent page, this scope appears as the Maintain access to data you have given it access to permission. | All actions in Sites.Read.All and Add a table, Add a worksheet, Add rows in batch, Delete row, and Update row. This permission is recommended for all actions to preserve the connection's validity. |
User.Read | Allows the app to sign-in and read the profile of signed-in users. | This permission is recommended for all actions. |
offline_access | Allows the app to access Microsoft Graph data after the user has signed out or the session has expired. | This permission is required to establish a connection. |
# Minimum scopes for authorization code grant connections
The following minimum scopes are required to establish a connection to Excel using authorization code grant authentication:
Files.Read
offline_access
# Default scopes for client credential connections
By default, the Excel connector requests the following scopes for client credentials connections. These scopes are necessary to use all of this connector's triggers and actions. Additionally, you must assign these permissions to the Workato app as Application permissions in the Azure portal.
Permission | Description | Relevant action or trigger |
---|---|---|
Files.Read.All | Allows the app to read the signed-in user's files. | Get cells, Get rows, List tables, List worksheets, and Search workbooks |
Files.ReadWrite.All | Allows the app to read and write to the signed-in user's files. | All actions in Files.Read and Add a table, Add a worksheet, Add rows in batch, Delete row, and Update row. |
Sites.Read.All | Allows the app to read documents in all SharePoint site collections on behalf of the signed-in user. | Get cells, Get rows, List tables, List worksheets, and Search workbooks |
Sites.ReadWrite.All | Allows the app to read and write to documents in all SharePoint site collections on behalf of the signed-in user. On the consent page, this scope appears as the Maintain access to data you have given it access to permission. | All actions in Sites.Read.All and Add a table, Add a worksheet, Add rows in batch, Delete row, and Update row. This permission is recommended for all actions to preserve the connection's validity. |
User.Read.All | Allows the app to sign-in and read the profile of signed-in users. | This permission is recommended for all actions. |
# Minimum scopes for client credential connections
The following minimum scopes are required to establish a connection to Excel using client credentials-based authentication:
Files.Read.All
User.Read.All
# Connect Microsoft Entra ID to the Excel connector
To connect to the Excel connector using a Microsoft Entra ID account, ensure that all the consent requests are granted by admins.
Complete the following steps to grant admin consent using an admin account:
Sign in to your Azure portal and navigate to Enterprise Applications > Activity > Admin consent requests.
Approve the necessary consent requests.
Last updated: 11/27/2024, 3:59:35 PM