Microsoft Excel Connector 

Overview

Connect form responses directly to your Microsoft Excel Workbooks with the Microsoft Excel Connector. With the Microsoft Excel Connector, you can now quickly and easily access your response data within Excel for data processing and analytics, enabling you to do even more with your data to meet your business needs. 

Requirements

To use the Microsoft Excel Connector, a FormAssembly user must

• have Content Management user permissions enabled for their login
  • specifically, the Microsoft Excel Premium Add-on must be enabled for the user account
• have a Microsoft Office 365 Personal Account or Business Plan that includes Microsoft Excel and OneDrive Cloud Services
  • (see Microsoft Business Plans here)

Connector Properties

Refer to our Workflow-native Connectors article for general configuration information.

Configuration Tab

Destination Spreadsheet

Select an existing spreadsheet on your Microsoft 365 account. If you do not see your desired spreadsheet in the list, click Refresh Lists at the bottom of the Configuration tab. 

Destination Tab

Enter the name of an existing tab from your destination spreadsheet, or leave it empty to automatically assign Sheet1 as the Destination Tab. Alternatively, entering the name of a tab that does not exist in the destination spreadsheet creates a new tab with the name entered. 

Field Mapping

Mapping form fields to destination tab columns can be completed both automatically and manually. 

To quickly map all form fields, click Add all standard fields. This will automatically map form fields in the order that they appear on the form to each column. Note that clicking Add all standard fields leaves out the file upload field metadata.

To manually add a form field or value, click Add Another or adjust an existing field or value mapping.

• Use the dropdown to select whether to map a "Form Field" or "Value or Formula".
  • Form Field: Use the list to search for and select a form field from the forms included in the workflow. 
    • Note: Available form fields are all fields from forms that occur before the Microsoft Excel Connector in the workflow.
  • Value or Formula: Enter a value as text or open the formula editor to use the formula builder to add workflow aliases.  Click the formula button to open a WYSIWYG editor for further customizations to your value.

Choice Mapping

When mapping fields with multiple choices (checkboxes, radio buttons, etc.), the Edit Choice Mapping option becomes available. Use this to record custom values in your spreadsheet depending on the choice selected.

Repeatable Fields

When repeatable elements are detected, an option to further configure the behavior of your spreadsheet will appear. Repeatable elements can be added to your spreadsheet as new rows or comma-separated values within a single cell. By default, each submission is contained within a single row, and each repeated element is contained within a single cell as comma-separated values. This behavior can be changed to create a new row each time the selected section is repeated.

Click the X to clear the default ‘Workflow’ selection, and choose a new option from the menu.

When a repeatable section is selected from the menu, for each instance, the repeatable section is submitted, and a new row will be created for the workflow response. For example, if the repeatable section ‘Allergy Details’ is selected for a patient intake form, for each allergy that is submitted for a patient, a new row will be entered into the spreadsheet. In this patient intake form example, the patient information accompanying each allergy submitted will be copied into the appropriate cells of each line, so there will be no empty cells for lines with data. Because the information is duplicated where necessary, information can be sorted with ease within Microsoft Excel.

Known Behaviors

Please note the following known behaviors with the Excel Connector:

• The Workflow Architect (the creator of the workflow) must type to input the destination tab name to map data to an existing tab.
• If a value for the destination tab is not entered and there is no default tab named Sheet1, upon running, the connector will create a new tab called Sheet1.
• For tabs with preexisting content...
  • If preexisting content exists that was not created by a FormAssembly connector, newly submitted content will be added in the first available row below the existing content on the tab. This new content includes a header row, regardless of whether it matches the header row of the preexisting data.
  • If preexisting content exists that was created by a FormAssembly connector, newly submitted content will be added to the next available row, excluding an additional header row.
• If the form is changed or the connector is later configured to include additional fields, any recently added or new field labels will not be reflected in the workbook headers.
• The Manage Authorizations table may not reflect all authorized accounts within workflow connectors. However, revoking authorization revokes accounts on both form and workflow connectors.
• If the workbook tab is being actively edited while responses are being received, there may be a delay in data updates.

Viewing Responses in Your Workbook

To see the connector in action, submit a test response.

Workflow responses generated through the Excel Connector are processed in real-time, though initial workbook creation may take longer. Responses are automatically added to your workbook upon submission.

You can view responses through FormAssembly and your Microsoft 365 account.

In your Microsoft 365 account, look for the name of the workbook you configured in your Excel Connector.