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. 

This connector is available as an Add-on for Team plans and higher when using FormAssembly Workflow. For those interested in purchasing this Add-on, please contact your Account Manager. If you do not have an Account Manager, please contact our Sales Team. 

Requirements

To use the Microsoft Excel Connector, a FormAssembly user must

• be part of a Team plan or higher
• purchase the Excel Connector Add-on for their instance
• 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)
• use the connector on a FormAssembly workflow

Connector Setup for Latest Version

1. Add the Microsoft Excel connector to your workflow

• Select Add Step and choose Connector
• Select Microsoft Excel

2. Set up the Connector Properties

Description Tab

Use the Description tab to add a Step Name and Step Description.

Authorization Tab

The Authorization dropdown lists all saved authorizations from all workflows owned by your user account. Select an existing authorization from the list or scroll to the bottom to create a New Authorization.

Create a New Authorization

• Select New Authorization from the Authorization dropdown list
• Enter a Name for the authorization
• Click Authorize

Grant access to Microsoft Excel.

• Sign in with your Microsoft 365 account credentials
• Complete any additional authentication steps configured for your account
• Accept the permissions requested by the FormAssembly Microsoft Excel Connector for your Microsoft 365 account
  • If you do not accept the permissions, you will be unable to use the connector
• Click Save to store and apply this authorization to the connector

Edit an Existing Authorization

• Select a saved authorization from the Authorization dropdown list
• Click Edit to modify authorization settings
• When complete, click Save to apply the changes to the saved authorization, or click Save as New to save the modified authorization as a new entry in the Authorization dropdown list.

Manage or Delete Authorizations

• Click Manage to view the Authorizations page

From the Authorizations page, an authorization can be revoked. When revoked, the authorization is deleted from all connectors. Ensure an authorization is not used in a live workflow before it is revoked, to prevent connector errors.

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, 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.

Actions and the Add Action Button

The Microsoft Excel Connector may be configured to send data to more than one destination tab or spreadsheet by using multiple Actions. For each additional destination you want to configure, use the Add Action button to create another Action. Complete the configuration of the destination spreadsheet, destination tab, and field mapping for each Action.

Delete an action by pressing the Delete Action button at the bottom of each action configuration.

Error Handling Tab

By default, if the connector encounters an error, the workflow terminates. To override this behavior, you can configure the error to redirect the workflow to another step by selecting a step from the Upon Connector Error… dropdown menu.

You may create a custom error message to display to your respondent. If a custom message is not added, the default connector error displays. To include the default message with your custom message, select the checkbox to do so.

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 not a 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.
• FormAssembly does not recommend using previous versions of Excel Connectors and the latest version of the Excel Connector in the same Workflow. It is recommended to delete the previous version of the connector and add a new step using the new Excel Connector setup.
• If the workbook tab is being actively edited while responses are being received, there may be a delay in data updates.

Connector Setup for Previous Version

1. Within a new or existing workflow, add a connector step for your Excel Connector.
   • Click Add Step
   • Select Connector from the Search for Step dropdown menu
2. Set up the Connector Properties to pull response data from a FormAssembly Form, and send the data through an Excel Connector.
   • In Connector Properties, select the Description Tab and enter a desired Connector Name (required) for the step and a description
   • In Connector Properties, select the Configure Tab
     • In the Form Response Action dropdown, choose the form you want to connect to Excel
     • In the Select Connector dropdown, choose "Microsoft Excel"
     • Click the Configure Connector button
3. Configure the Excel Connector to connect to your Microsoft 365 Business account, choose an Excel workbook to send the response data to, and enable the connector.
   • Grant access to your Microsoft 365 account
     • Click the "Grant new access to Microsoft 365" button
     • In the Microsoft modal pop-up, sign in with your Microsoft account email or phone number
     • Enter your Microsoft account password, when prompted
     • Complete any additional authentication steps configured for your Microsoft account
     • Accept the permissions requested by the FormAssembly Excel Connector for your Microsoft account
       • Note: If you do not accept the permissions, you will be unable to use the connector
     • You should see a banner message saying "Access granted to Microsoft 365. Please follow Step 2 instructions, enable the connector, and click Save button when ready."
4. Choose whether you want FormAssembly to create a new workbook for you, or select an existing workbook from your Microsoft account to be updated
   • Under STEP 2 - SELECT DESTINATION, select the radio button for either...
     • Let FormAssembly create a new workbook
       • A new workbook will be generated the first time this workflow step is completed
       • The workbook will be named "Responses for form [FORM NAME]_[YYYY]-[MM]-[DD] [HH]_[mm]_[ss]" where...
         • FORM NAME = the name of the form you selected in Connector Properties
         • YYYY = the 4-digit calendar year the first time this workflow step is completed
         • MM = the 2-digit calendar month the first time completed
         • DD = the 2-digit calendar day the first time completed
         • HH = the 2-digit hour the first time completed
         • mm = the 2-digit minute the first time completed
         • ss = the 2-digit second the first time completed
         • Example Workbook:  Responses for form Example Form_2023-04-04 14_07_16.xlsx
     • Select a workbook to be updated
       • Choose an existing workbook in your Microsoft 365 account from the dropdown
       • The connector will create a new tab in this workbook the first time this workflow step is completed
5. Enable the Excel Connector by selecting the radio button next to "Enabled"
6. Click SAVE

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. For more information on viewing workflow responses through FormAssembly, click here. 

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

For the Previous Version of the Connector Only:

If you chose to connect to an existing workbook, search for the name of that workbook. If you chose to have FormAssembly create a new workbook, look for a workbook named the following:  Responses for form [FORM NAME]_[YYYY]-[MM]-[DD] [HH]_[mm]_[ss].xlsx

The Excel Connector generates the following Column Headers:

• Submitted Date 
• Completion Time 
• Completion Status 
• [FORM QUESTIONS]
  • For each question on a Form, the name of the Form Question will have its own column header 
• Response URL
• Referrer
• IP Address
• Unprotected File List
  • If File Upload fields exist in the Form, any uploaded files will populate here

Each response appears as its own row in the workbook.