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 or XLSX 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 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
To set 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 canvass in the widget’s settings panel.
- Upload the spreadsheet file.
- 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.
- 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.
- 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.
Send Comment:
66 Comments:
45 days ago
LIMITADO A MENOS DE 14500 FILAS.... NO ME SIRVE
51 days ago
I've been using this widget for months and love it. However today my entry fields just continue to spin with "loading...". Any idea if there's a known bug with this widget?
83 days ago
You have a great demo with regards to the autofill demo, can we get the innerworkings of that demo, the spreadsheet, the condition, just more detailed information in general.
133 days ago
I need to make updates the the spreadsheet the form is pulling from. I accidentally deleted the file off my computer, is there a way to download the file that I have already uploaded to ensure the updated one is formatted correctly?
138 days ago
Could you do a demo on how to use this function but taking information from another form, downloading its info in excel and then transferring some of those field values into another document. With the added difference that each question from form 1 has been numbered with a space after the number before the question.
152 days ago
This is actually pretty nice.
Curious, is there anything in the works where a USER can upload a spreadsheet, input the Access code, have that data populate into the form - and then submit the form with the data?
Trying to figure out how I can help my team from all the entry errors (mistyping numbers, entering numbers for one machine into a different machine, etc.). Would be nice if, at the end of their shift, they simply upload the spreadsheet - select the code from the worksheet, click the button - all done.
Thanks,
Craig
286 days ago
Is there a way that this form can be integrated with google sheets? My data is constantly changing and having an autosync would be so helpful. I have to keep removing my excel spreadsheet and then reattaching everytime something changes.
Thanks so much!
308 days ago
Is there any maximum limit of rows that the Spreadsheet to form widget? I have a spreadsheet that has 37,000 rows and it works great BUT when inputting the data it needs to find on line 30651 it gives and error and says access code not found.... Appreciate the help.
328 days ago
You said in point 7, "Set up the other options. Some of the options, such as the texts, are relatively straightforward"... can you please explain how this is done as there is no way to connect other fields to the spreadsheet?
342 days ago
Is there a way to have different fields display based on which code is entered from the spreadsheet? Ideally I would upload a spreadsheet with 2 sheets, each with one column of unique codes. If a code is entered from Sheet 1, certain fields display and different fields display if a code is entered from Sheet 2.
342 days ago
Two questions --
1) Can a single spreadsheet by used by multiple forms that need the same lookup fields?
2) is there an API to update/replace the spreadsheet, instead of manually uploading a new spreadsheet?
Thanks!
357 days ago
How many columns does this support or load? If I have roughly 1,000 columns worth of data, would it load it for all the columns?
More than a year ago
Any idea how to change the notice "please add a code" when clicking the button without having a value into input box.
Thank you!
More than a year ago
how to manage to get an instant notice, when typing a character which is not supported?
I've managed to do this for normal input fields using Conditions Show/Hide Field, but it is not working for this widget. Is the another another possibility to manage this?
More than a year ago
Can I use the Spreadsheet to Form widget to update data in the spreadsheet? Also, can I use the same Spreadsheet to Form widget to create rows in the spreadsheet?
More than a year ago
It would be super helpful if this could be integrated with Google sheets. Since we can't connect Jotform to our school district's student database, we do not have a way to verify end user identity based on log in credentials. But using an excel of student data is only a snapshot in time. Not helpful for the constant flow of new enrollments. There's got to be a better way.
More than a year ago
Is it possible to set the spreadsheet to autoupdate from a source for a given frequency versus only having a static spreadsheet that will not update after being uploaded to the widget? If not, that additional functionality would be benefical.
More than a year ago
Is it possible to make the input all capital letters?
More than a year ago
Any chance this widget will support Address fields in the near future?
More than a year ago
Great widget but should be allowed to make search non case sensative maybe?
More than a year ago
Comment by Rooster [March 21, 2023, 9:37 PM]:
"What if we would like to data to be pulled live from a Google Sheet, instead of uploading static data that cannot change?"
As cool as that idea is, I doubt it would be feasible for Jotform to implement. I am a software developer btw (not affiliated with Jotform).
The problem with Google Sheets is that 3rd party applications can only track changes to a sheet by constantly polling its state - which means that a program will have to be running at regular intervals to check if a sheet was updated.
Now imagine doing that for every form that uses that feature. Costs would skyrocket!
A cheaper alternative would probably have the widget expose an HTTP endpoint (maybe through Jotform's existing REST API) that end-users can use to upload the data programmatically in a multitude of formats (*.XLS, *.XLSX, *.CSV, *.JSON).
This option would allow end-users to have programmatic control over the upload process allowing them to create their own automations and integrations. The downside with this approach is that it puts some of the technical burden on the end-user. Still, its probably the most cost-effective route for Jotform (might even be quicker to roll-out too).
More than a year ago
Does this option account for new entries in my spreadsheet and therefore would update my fields based on new data automatically?
More than a year ago
Is it possible to download a previously uploaded spreadsheet to make edits? How can I access this if I no longer have the file saved?
More than a year ago
How do I use the spreadsheet to form widget to create a form?
More than a year ago
Updating here for users since Jotform will not - and saving you time.
NOTE - THIS DOES NOT CURRENTLY WORK FOR MULTIPLE CHOICE FIELDS.
They don't know why and haven't fixed it. They also will not update the instructions to save you from repeating the problem while they "fix" it.