Excel Online (OneDrive)

Excel Online (OneDrive) connection provider lets you work with Excel files stored in OneDrive. This connection provider only supports personal (MSA) accounts.
This connector is available in the following products and regions:
Service | Class | Regions |
---|---|---|
Logic Apps | Standard | All Logic Apps regions |
Power Automate | Standard | All Power Automate regions except the following: - US Government (GCC High) - China Cloud operated by 21Vianet |
Power Apps | - | Not available |
Connector Metadata | |
---|---|
Publisher | Microsoft |
Known Issues and Limitations
- The maximum size of an Excel file that is supported by the Excel Online (OneDrive) connector is 5 MB.
- The data may not be up to date (there may be some delay) when filtering or sorting is used in the
List rows present in a table
action. - The
List rows present in a table
action supports basic filtering and sorting:- Supports the following filter functions: eq, ne, contains, startswith, endswith.
- Only 1 filter function can be applied on a column.
- Only 1 column can be used for sorting.
- An Excel file may be locked in OneDrive for an update or delete up to 12 minutes since the last use of the connector.
- A single Excel file should be used by a single connection to prevent writing data from multiple connections and possible data inconsistency.
- Simultaneous file modifications made by other connectors or manual edits are not supported.
- The connector supports files only in Microsoft Excel Open XML Spreadsheet format (*.xlsx).
- The connector retrieves rows for 500 columns maximum in the
List rows present in a table
action. Rows for first 500 columns are returned by default. You can provide comma-separated list of specific columns to retrieve inSelect Query
parameter. - The connector doesn't support using OData parameters for tables that contain hidden columns.
- The connector timeout caused by re-calculations can occur, either there are complicated formulas or there are too many rows in the worksheet. In both cases, data can be inserted multiple times because of retry policy.
- The connector will return the Bad Gateway error if the spreadsheet is in a read-only mode. In order to disable read-only mode, please learn more here.
Filter Query
/Order By
/Select Query
operation parameters support only alphanumeric column names.- Pivot tables are not supported due to Graph API limitations.
- If a user makes too many requests within a short period of time, it is possible to get a 429 response. The throttling limit is unique to each request and it varies based on the request count, memory used, and other important factors.
Column names handling
Please note that column names in the action's response results may be transformed in order to be compatible with OData format:
Character | Encoded value |
---|---|
. | _x002e_ |
@ | _x0040_ |
: | _x003a_ |
# | _x0023_ |
E.g. Column [one]#1
-> Column [one]_x0023_1
General Limits
Name | Value |
---|---|
Maximum number of identity column variants that can be used to Get/Insert/Update/Delete a row from a single excel table. Current value is set to 2, which means that up to two column name variants should be in use across workflows for one particular table. | 2 |
Throttling Limits
Name | Calls | Renewal Period |
---|---|---|
API calls per connection | 100 | 60 seconds |
Actions
Add a key column to a table |
Add a key column to an Excel table. |
Add a row into a table |
Add a new row into the Excel table. |
Add a row into a table [DEPRECATED] |
This action has been deprecated. Please use Add a row into a table instead.
|
Create table |
Create a new table in the Excel workbook. |
Create worksheet |
Create a new worksheet in the Excel workbook. |
Delete a row |
Delete a row using a key column. |
Get a row |
Get a row using a key column. |
Get tables |
Get a list of tables in the Excel workbook. |
Get worksheets |
Get a list of worksheets in the Excel workbook. |
List rows present in a table |
List rows present in a table. |
Update a row |
Update a row using a key column. |
Add a key column to a table
Add a key column to an Excel table.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
File
|
file | True | string |
Select an Excel file through File Browse. |
Table
|
table | True | string |
Select a table from the drop-down. |
Key Column
|
idColumn | string |
Provide the key column name. |
Add a row into a table
Add a new row into the Excel table.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
File
|
file | True | string |
Select an Excel file through File Browse. |
Table
|
table | True | string |
Select a table from the drop-down. |
Row
|
item | True | dynamic |
Row to add into the specified Excel table. |
DateTime Format
|
dateTimeFormat | string |
DateTime Format. |
Returns
Add a row into a table [DEPRECATED]
This action has been deprecated. Please use Add a row into a table instead.
Add a new row into the Excel table.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
File
|
file | True | string |
Select an Excel file through File Browse. |
Table
|
table | True | string |
Select a table from the drop-down. |
Row
|
item | True | dynamic |
Row to insert into the specified Excel table. |
Returns
Create table
Create a new table in the Excel workbook.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
File
|
file | True | string |
Select an Excel file through File Browse. |
Table name
|
TableName | string |
Enter the Excel table name. |
|
Table range
|
Range | True | string |
Enter the table address using A1 notation. |
Columns names
|
ColumnsNames | string |
Enter the columns names separated by ';' or ','. |
Returns
Table metadata
- Body
- TableMetadata
Create worksheet
Create a new worksheet in the Excel workbook.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
File
|
file | True | string |
Select an Excel file through File Browse. |
Name
|
name | string |
Worksheet name. |
Returns
- Body
- WorksheetMetadata
Delete a row
Delete a row using a key column.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
File
|
file | True | string |
Select an Excel file through File Browse. |
Table
|
table | True | string |
Select a table from the drop-down. |
Key Column
|
idColumn | True | string |
Select a column from the drop-down. |
Key Value
|
id | True | string |
Enter the key value. |
Get a row
Get a row using a key column.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
File
|
file | True | string |
Select an Excel file through File Browse. |
Table
|
table | True | string |
Select a table from the drop-down. |
Key Column
|
idColumn | True | string |
Select a column from the drop-down. |
Key Value
|
id | True | string |
Enter the key value. |
DateTime Format
|
dateTimeFormat | string |
DateTime Format. |
Returns
Get tables
Get a list of tables in the Excel workbook.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
File
|
file | True | string |
Select an Excel file through File Browse. |
Returns
Name | Path | Type | Description |
---|---|---|---|
value
|
value | array of object | |
Id
|
value.id | string |
Table Id. |
Name
|
value.name | string |
Table name. |
Show banded columns
|
value.showBandedColumns | boolean |
Show banded columns. |
Highlight first column
|
value.highlightFirstColumn | boolean |
Highlight first column. |
Highlight last column
|
value.highlightLastColumn | boolean |
Highlight last column. |
Show banded rows
|
value.showBandedRows | boolean |
Show banded rows. |
Show filter button
|
value.showFilterButton | boolean |
Show filter button. |
Show headers
|
value.showHeaders | boolean |
Show headers. |
Show totals
|
value.showTotals | boolean |
Show totals. |
Style
|
value.style | string |
Table style. |
Get worksheets
Get a list of worksheets in the Excel workbook.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
File
|
file | True | string |
Select an Excel file through File Browse. |
Returns
Name | Path | Type | Description |
---|---|---|---|
value
|
value | array of WorksheetMetadata |
List rows present in a table
List rows present in a table.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
File
|
file | True | string |
Select an Excel file through File Browse. |
Table
|
table | True | string |
Select a table from the drop-down. |
Filter Query
|
$filter | string |
An ODATA filter query to restrict the entries returned. |
|
Order By
|
$orderby | string |
An ODATA orderBy query for specifying the order of entries. |
|
Top Count
|
$top | integer |
Total number of entries to retrieve (default = all). |
|
Skip Count
|
$skip | integer |
The number of entries to skip (default = 0). |
|
Select Query
|
$select | string |
Comma-separated list of columns to retrieve (first 500 by default). |
|
DateTime Format
|
dateTimeFormat | string |
DateTime Format. |
Returns
Update a row
Update a row using a key column.
Parameters
Name | Key | Required | Type | Description |
---|---|---|---|---|
File
|
file | True | string |
Select an Excel file through File Browse. |
Table
|
table | True | string |
Select a table from the drop-down. |
Key Column
|
idColumn | True | string |
Select a column from the drop-down. |
Key Value
|
id | True | string |
Enter the key value. |
Provide the item properties
|
item | True | dynamic |
Provide the item properties. |
DateTime Format
|
dateTimeFormat | string |
DateTime Format. |
Returns
Definitions
TableMetadata
Table metadata
Name | Path | Type | Description |
---|---|---|---|
name
|
name | string |
Table name |
title
|
title | string |
Table title |
x-ms-permission
|
x-ms-permission | string |
Table permission |
x-ms-capabilities
|
x-ms-capabilities | TableCapabilitiesMetadata |
Metadata for a table (capabilities) |
schema
|
schema | Object | |
referencedEntities
|
referencedEntities | Object | |
webUrl
|
webUrl | string |
Url link |
TableCapabilitiesMetadata
Metadata for a table (capabilities)
Name | Path | Type | Description |
---|---|---|---|
sortRestrictions
|
sortRestrictions | TableSortRestrictionsMetadata |
Metadata for a table (sort restrictions) |
filterRestrictions
|
filterRestrictions | TableFilterRestrictionsMetadata |
Metadata for a table (filter restrictions) |
selectRestrictions
|
selectRestrictions | TableSelectRestrictionsMetadata |
Metadata for a table (select restrictions) |
isOnlyServerPagable
|
isOnlyServerPagable | boolean |
Server paging restrictions |
filterFunctionSupport
|
filterFunctionSupport | array of string |
List of supported filter capabilities |
serverPagingOptions
|
serverPagingOptions | array of string |
List of supported server-driven paging capabilities |
Object
TableSortRestrictionsMetadata
Metadata for a table (sort restrictions)
Name | Path | Type | Description |
---|---|---|---|
sortable
|
sortable | boolean |
Indicates whether this table has sortable columns |
unsortableProperties
|
unsortableProperties | array of string |
List of unsortable properties |
ascendingOnlyProperties
|
ascendingOnlyProperties | array of string |
List of properties which support ascending order only |
TableFilterRestrictionsMetadata
Metadata for a table (filter restrictions)
Name | Path | Type | Description |
---|---|---|---|
filterable
|
filterable | boolean |
Indicates whether this table has filterable columns |
nonFilterableProperties
|
nonFilterableProperties | array of string |
List of non filterable properties |
requiredProperties
|
requiredProperties | array of string |
List of required properties |
TableSelectRestrictionsMetadata
Metadata for a table (select restrictions)
Name | Path | Type | Description |
---|---|---|---|
selectable
|
selectable | boolean |
Indicates whether this table has selectable columns |
WorksheetMetadata
Name | Path | Type | Description |
---|---|---|---|
Id
|
id | string |
Worksheet Id. |
Name
|
name | string |
Worksheet name. |
Position
|
position | integer |
Worksheet position. |
Visibility
|
visibility | string |
Worksheet visibility. |