Microsoft Excel Online Helper template

Kathyana Queeman

Updated August 26, 2024 14:53

The easiest way to get started working with Microsoft Excel Online is to utilize pre-built flow steps from the marketplace. You can source flow steps from the marketplace directly in the flow builder. Here are the most common use cases.

Flow: Read data from one table and update another table

This flow contains one export and one import. You do not have to use the entire flow or create a clone of a flow. Instead, after creating a connection, you can clone the flow step directly from the marketplace by creating a flow step.

Important

When creating your connection, you need to toggle from a simple connection to an HTTP connection and add the following scopes manually:

Files.Read
Files.ReadWrite
offline_access
Sites.Read.All
Sites.ReadWrite.All

Export: Read all data from a table

Read data from a table within a workbook and output really easy-to-use records. This supports Excel worksheets stored in OneDrive or SharePoint. To configure this step, go to the custom settings at the bottom of this resource and choose your configuration.

To configure your export:

Where is your Excel file stored? (required): Select where your Excel file is stored.
Workbook: Select your Excel workbook.
Worksheet name (required): Enter the name of the worksheet that you would like to extract or update. For example, Sheet1.
Does your sheet have column headers?: If this setting is changed, the preview will have the old setting until saving the export. Because of this, it's advisable to save the export after changing this setting.

Warning

Do not modify any aspects of the flow step except the Custom settings section. Once you configure the custom settings, the correct URI will be automatically generated.

Import: Update existing table rows and append new table rows in bulk

Upload data to a table within a workbook and insert/update existing rows in bulk. This supports Excel worksheets that are stored in OneDrive or in SharePoint. To configure this step, go into the custom settings at the bottom of this resource and choose your configuration.

Warning

Do not modify any aspects of the flow step except the Custom settings and Mappings. Once you configure the custom settings, the correct URI will be automatically generated.

To configure your import:

Where is your Excel file stored? (required): Select where your Excel file is stored.
Workbook: Select your Excel workbook.
Table name (required): Enter the name of the table that you would like to extract or update. For example, Table1.
Headers: Calculated after save. These are the column headers discovered in the spreadsheet.
Column to find existing rows (required): Enter the column header from your spreadsheet that should be used to find existing rows. For example: id, email, task_id, etc...
Source record field to find existing rows (required): Enter the field from the source record that should be used to lookup existing rows in the spreadsheet. For example, my_record_id.

Flow: Read data from one worksheet and update another worksheet

This flow contains one export and two imports. You do not have to use the entire flow or create a clone of a flow. Instead, after creating a connection, you can clone the flow step directly from the marketplace by creating a flow step.

Export: Read all data from worksheet

Read data from a worksheet and output really easy-to-use records. This supports Excel worksheets stored in OneDrive or SharePoint. To configure this step, go to the custom settings at the bottom of this resource and choose your configuration.

To configure your export:

Where is your Excel file stored? (required): Select where your Excel file is stored.
Workbook: Select your Excel workbook.
Worksheet name (required): Enter the name of the worksheet that you would like to extract or update. For example, Sheet1.
Does your sheet have column headers?: If this setting is changed, the preview will have the old setting until saving the export. Because of this, it's advisable to save the export after changing this setting.

Warning

Do not modify any aspects of the flow step except the Custom settings section. Once you configure the custom settings, the correct URI will be automatically generated.

Import: Update existing rows and append new rows

Upload data into a worksheet, updating existing rows vs appending new rows based on a key column. This supports Excel worksheets that are stored in OneDrive or in SharePoint. To configure this step, go into the custom settings at the bottom of this resource and choose your configuration.

To configure your import:

Where is your Excel file stored? (required): Select where your Excel file is stored.
Workbook: Select your Excel workbook.
Worksheet name (required): Enter the name of the worksheet that you would like to extract or update. For example, Sheet1.
Does your sheet have column headers?: Is there a column header row in the sheet you are updating? If true, the headers will be used as the field ids. If false, the zero-based column indexes will be used as the field ids.
Headers: Calculated after save. These are the column headers discovered in the spreadsheet.
Column to find existing rows (required): Enter the column header from your spreadsheet that should be used to find existing rows. For example: id, email, task_id, etc...
Source record field to find existing rows (required): Enter the field from the source record that should be used to lookup existing rows in the spreadsheet. For example, my_record_id.

Warning

Do not modify any aspects of the flow step except the Custom settings and Mappings. Once you configure the custom settings, the correct URI will be automatically generated.

Import: Append new rows in bulk

Upload data into a worksheet by appending rows in bulk. This supports Excel worksheets that are stored in OneDrive or in SharePoint. To configure this step, go into the custom settings at the bottom of this resource and choose your configuration.

Warning

Do not modify any aspects of the flow step except the Custom settings and Mappings. Once you configure the custom settings, the correct URI will be automatically generated.

To configure your import:

Where is your Excel file stored? (required): Select where your Excel file is stored.
Workbook: Select your Excel workbook.
Worksheet name (required): Enter the name of the worksheet that you would like to extract or update. For example, Sheet1.
Does your sheet have column headers?: Is there a column header row in the sheet you are updating? If true, the headers will be used as the field ids. If false, the zero-based column indexes will be used as the field ids.
Headers: Calculated after save. These are the column headers discovered in the spreadsheet.
Column to find existing rows (required): Enter the column header from your spreadsheet that should be used to find existing rows. For example: id, email, task_id, etc...
Source record field to find existing rows (required): Enter the field from the source record that should be used to lookup existing rows in the spreadsheet. For example, my_record_id.