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:
49 Comments:
Is it possible to make the input all capital letters?
Any chance this widget will support Address fields in the near future?
Great widget but should be allowed to make search non case sensative maybe?
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).
Does this option account for new entries in my spreadsheet and therefore would update my fields based on new data automatically?
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?
How do I use the spreadsheet to form widget to create a form?
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.
Does this work with HIPAA accounts?
Doesn't work. Even following these instructions exactly, I get the error “ReferenceError: XLSX is not defined”
Can you use a customer's email address (existing from a column within the XLS file) to be their “Unique Code”?? My concern with generating any kind of other unique code is that other who use the form may be prone to “guess” unique codes and thereby obtain another organizations data on all their customers.
Id like to add my vote for this widget to be integrated with google sheets so that I don't have to update the excel file. I have constantly changing data and love this feature but would prefer if it wasn't static.
What if you would pull the data from jotform Tables instead of uploading static data that cannot change?
What if we would like to data to be pulled live from a Google Sheet, instead of uploading static data that cannot change?
Is there a way to integrate with Google sheets to pull data live?
No se puede conectar directamente con una hoja de calculo de Google Drive??
Any updated on whether this can be integrated with Google Sheets or any other online spreadsheet?
The article says, CSV are allowed. Indeed they are not selectable.
Also, the form is now taking much longer to execute compared to several weeks ago. Agree with others that uploading the xlsx is more difficult now than previously.
Para nuestra operación esto es un problema criticó, los usuarios no pueden armar la información!
is not working since 1 week ago, stuck loading file and later no uploaded spreadsheet file
Same here for over 1 week: attached spreadsheets "drop" mysteriously several times a day and we have to re-upload them, which within itself can take several attempts. Please fix ASAP!
My spreadsheet widget not working and it only shows "No uploaded spreadsheet file." even though there is a file. Can please fix it and show me how to fix this, as we need it as soon as possible
I do not have a Labels Row option in my Widget Settings. I only have Access Code Columns, then "Fill Button Text", and other customizations for the Various Text options. I also cannot get it to work for multiple option form element.
It's not working and it only shows "No uploaded spreadsheet file." even though there is a file.
Can someone fix it asap?
It just says, "No uploaded spreadsheet file." no matter how many times I upload a file, no matter which file I choose.
i want the user when he/she input the ID in a form, if it is already entered before, a message like "this ID is already registered.
can someone help me in achieving this task?
I am trying to use this widget. However, I can not get it to work for me. I have uploaded a .xlxs, an xls and a csv file. None seem to work.
I have my Unique code in column a with headings in row 1. My unique code is labeled Registration Number. ALL the headings match the form.
I have marked "Autofill by condition" as yes.
It doesn't fill the fields from the form I uploaded no matter which format I have the form in.
Please HELP me.
I was worried reading the latest comments but it seems to work for me. Using a simple xlsx with 800+ rows and 7 columns
The widget is not working, I have it in all my customer-facing form and now my operations are stopped because of something that is broken on this widget
@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 <a target='_blank' href="https://www.jotform.com/213275499944975" >https://www.jotform.com/213275499944975</a> 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: <a target='_blank' href="https://www.jotform.com/answers/3200215-populate-the-spreadsheet-to-form-widget-field" >https://www.jotform.com/answers/3200215-populate-the-spreadsheet-to-form-widget-field</a>
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?