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:
Then, the column names on your sheet should be labeled exactly the same.
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
- 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
- Click the Add Form Element button in the Form Builder.
- Go to the Widgets tab.
- Search and select the Spreadsheet to form widget.
- Click the Upload File button in the widget’s settings panel.
- Upload the spreadsheet file.
- Set the Access code column and Label row 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” in this field.
Labels Row
The column labels‘ row number. Also known as the column headings that are usually on the first row. 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” in this field.
Autofill By Condition
Enabling this option allows the widget to autofill the fields without clicking on its fill button if the widget’s input field gets a value through the conditions. Here’s a demo form to see it in action.
The other options, Fill Button Text, Loading Message, etc., are relatively straightforward.
Questions? Don’t hesitate to post them in our Support Forum.
Send Comment:
20 Comments:
@isabella & @julie We were having the issue too. However, we converted the spreadsheet to .xls instead of .xlsx.
We are also experiencing an issue with the Spreadsheet to Form widget currently as it is not working and has been working well until discovered today that it is not returning the values expected - instead get ''undefined'' under the Access code column field
Is there a known issue with the Spreadsheet to Form widget currently as it is not working and has been working well until discovered today that it is not returning the values expected - instead get ''undefined'' under the Access code column field
This is a useful widget but I am agree with the comment from "Cortesias Edam"... it would be really powerful if it was an option to bring data from an online spreadsheet. We have been waiting years for a lookup solution in jotform and nothing goes towards this essential feature.
I have been posted before it. It would be powerful and very useful if it could be linked to an online spreadsheet like google drive/sheet without needed to load the xls file every time.
Same inquires from: @Zbulo! Discover Albania and @NjW
I can't understand this can I call on phone to fix my problem? I'm old & don't understand how to do this
Is there any way to contact the developer? The purpose is that I use my Jotform offline via the Source Code and I would like to incorporate the Spreadsheet to Form Widget, but the Widget is not available through Source Code / Offline. I am interested in working with the Developer of this widget to gain this Widget functionality offline.
How does that unique code work precisely?
Can we get a way to link this widget to an online spreadsheet like google forms? I would like it to update automatically versus having to download the spreadsheet and then upload it every time new data is entered.
How can i upload cell value from jotform table to a form?
¿Is there any chance to bring back attachments (PDF) to the results?
Hello, excellent integration of the spreadsheet, but how could I select several products to send them in the same form?
I have tried duplicating the widget for this, but it replaces the entire form with the latest search.
Thank you
I want to use Jotform tables for this is posible.?
Hi team, we found that the video and guideline shows that spreadsheet is workable for "Time" widget, while the demo form https://www.jotform.com/213275499944975 shows that "Time" widget is empty. May I knows whether "Spreadsheet to From" is workable for "Time" widget? Many thx
If you'll make the input box a drop down list using the Unique Code column, It would be the perfect solution for me.
Autofill on condition: I'm hiding the widget and use conditional logic to fill in a value. Once that value is added, matching cells are auto-populated with spreadsheet content without need to manually press the button.
To load data within the form (before submission) from an uploaded sheet and enrich submissions allows for a lot of spreadsheet magic. It's the closest we get to a look up function. This would be so much more powerful if it could use a linked Google Sheet to pipe in dynamic content!
How good would this be if we could use a google spreadsheet therefore any changes would sync to the form
Ok, I found the solution for the populate question: https://www.jotform.com/answers/3200215-populate-the-spreadsheet-to-form-widget-field
Still don't get the "autofill by condition"
How can we keep update the Excel file in case the quantity of unique keys increase?
Do we have to upload a new Excel every time?
How works "Autofill by condition" here?