How to Use the Spreadsheet to Form Widget

May 24, 2021

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 CSVfiles; 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 Labels

    Form Field Labels

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

  • 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 Field Labels and Column Names match.
  • The widget only supports the following fields:
    • Full Name
    • Email
    • Time
    • Short Text Entry (Textbox)
    • Long Text Entry (Textarea)
    • Dropdown
    • Single Choice (Radio Button)
    • Multiple Choice (Checkbox)
    • 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

  1. Prepare your Spreadsheet and Form.
  2. Add the widget to your form.
    Add Spreadsheet to form widget
  3. Upload the spreadsheet you’ll use.
    Upload File

    Upload File

    Note: the bigger the file, the longer it will take for the data to load on the form.

  4. Setup the other options on the widget.
    Spreadsheet to form options

    Spreadsheet to form options

Access Code Column

The Unique Code column index. This will help identify each data row you want to appear on the form. Say, your unique codes are placed on Column K in Excel, then you type in “K” on this section.

Labels Row

The column labels‘ row number. This will identify each column label and fill in the exact question label from your form. The same idea applies if your column labels are on Row 1, then type in “1” on this section.

The other options, Fill Button Text and Loading Message, are relatively straight forward.

To test it out, here’s a demo form.

And here’s a screenshot of the actual data within the spreadsheet used for this form:

If you want to use this same spreadsheet to do your own tests, feel free to download it from this link.

Questions? Don’t hesitate to post them in our Support Forum.

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: