How to Use the Spreadsheet to Form Widget

November 23, 2022

Are you hoping to populate your form fields from an external Excel Sheet? Perhaps you wanted to pull existing records and display them on your form? If so, your long wait is over. Meet the new Spreadsheet to Form widget.

The widget allows you to upload a spreadsheet to your form and use the values you have on the spreadsheet to pre-populate the form. By using a unique code present on each row of your spreadsheet, you can access that specific row when it matches the unique code entered on the form. Think of it as querying a database using the unique code as the key index.

Requirements and Limitations

Before we proceed, here are some limitations and key points you should be aware of:

  • The widget requires XLS, XLSX, or CSV files; it won’t accept any other format.
  • The unique code on your sheet doesn’t necessarily need to be on the 1st column. You can place it anywhere for as long as you configure the widget with the respective column index (more on this later).
  • The unique code is case-sensitive.
  • The column names on your sheet should match the field labels on your form.

For example, if your form fields were labeled like this:

form-field-column-labels-min.png

Then, the column names on your sheet should be labeled the same.

spreadsheet-to-form-sheet-column-names-b

Here’s the demo form and sample spreadsheet used in the setup above.

  • The arrangement of the form fields on your form won’t matter, even if you have other fields in between. What’s important is to ensure that the form field labels and spreadsheet column names match.
  • The widget only supports the following form elements:
    • Full Name
    • Email
    • Time
    • Short Text
    • Long Text
    • Dropdown
    • Single Choice
    • Multiple Choice
    • Scale Rating
    • Star Rating
  • When the unique code is correct, the values inside the fields to be populated will be cleared and replaced by the values fetched from the spreadsheet.

If everything above looks good, let’s get on with it!

Setting up the Widget

To set up the widget:

  1. Click the Add Form Element button in the Form Builder.
  2. Go to the Widgets tab.
  3. Search and select the Spreadsheet to Form widget.
jotform-add-spreadsheet-to-form-widget
  1. Click the Upload File canvass in the widget’s settings panel.
  2. Upload the spreadsheet file.
jotform-spreadsheet-to-form-widget-upload-file
  1. Select the Access Code Column from a dropdown. The widget will also ask you to select the Sheet Name if you have multiple sheets in the spreadsheet file.
jotform-spreadsheet-to-form-widget-access-code-column
  1. Set up the other options. Some of the options, such as the texts, are relatively straightforward, so let’s skip to the following:
    • Submit Input Field value — This option allows you to include the Unique Code as part of the submission, so it’s visible in both the emails and submissions page.
    • Autofill By Condition — Enabling this option allows the widget to autofill the fields without clicking the autofill button if the widget’s input field gets a value through the conditions. Here’s a demo form to see it in action.
    • Reset fields when Invalid — Choose whether to automatically clear the entered unique code value if it’s not present in the uploaded spreadsheet file.
    • Run in the background — Instead of hiding the widget when it’s configured to be filled by condition, enable this option to run it in the background.
  1. Finally, click the Update Widget button at the bottom, and that’s it!

If you have questions, suggestions, or feedback, kindly post a comment below. You can also reach us by creating a support ticket.

Was this guide helpful?
Contact Support:

Our customer support team is available 24/7 and our average response time is between one to two hours.
Our team can be contacted via:

Support Forum: https://www.jotform.com/answers/

Contact Jotform Support: https://www.jotform.com/contact/

Send Comment:

Jotform Avatar
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Comments: