JotForm User Guide / Advanced Features /

How to Use the Spreadsheet to Form Widget

How to Use the Spreadsheet to Form Widget

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 Spreadsheet to Form 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.

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

1. The widget requires an .xls, .xlsx, or even a .csv file and it won't accept any other format.

2. 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).

3. The Unique Code is case-sensitive.

4. 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.

Fullname > Email > Time > Short Text Entry > Long Text Entry > Dropdown > Single Choice > Multiple Choice > Star Rating > Scale Rating

5. 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.

6. 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

(all other fields not mentioned on the list above will not work with the widget)

7. 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!

Step 1 - Prepare your Spreadsheet and Form.

Step 2 - Add the widget to your form.

Step 3 - Upload the spreadsheet you'll use.

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

Step 4 - Setup the other options on the widget.

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. 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: https://www.jotform.com/71642066575965

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.

Send Comment

27 Comments...

  • NewPerspectivesTravel

    Is there an answer to tutuprat and ViaInsideSales' questions? I want to do the same thing and have a pre-populated (drop-down) instead of text question.

  • tutuprat

    Same as last question, any way to make it as a dropdown instead of a text field?
    Also, will this widget populate hidden fields? I want to have several answers on the final submission but I want the end user to only see a few.

    Cheers,

  • ViaInsideSales

    How can I include the number option and the input table, to get populated with spreadsheet to Form Widget?, or is there another option to get pre populated?

  • ViaInsideSales

    I have the option multiple choice, but is not working, how has to be displaid in the excell cell?
    example of data in a cell:

    PAYMENT METHOD REQUIRED
    ACH,VIACHECK,E-CHECK

    SELECT DEPOSIT DAYS "
    MONDAY,WEDNESDAY,FRIDAY,

  • txforesttrail

    This is really cool!
    Can you suggest an easy way to prevent errors from users entering the unique code incorrectly (typos). For example, would it be possible to populate the input field for the unique code with a user's previous selection from a dropdown?

  • YoungsIncentive

    Is it only possible to have one Unique Code input field per form? I can get this to work great, but need the ability to add multiple fields and have unique information pull for each. When I add a second Spreadsheet to Form Widget, and enter the next Unique Code, it updates all fields, even those for the previous entry.

    I'm trying to create a form where the user inputs an item number and then the description, pricing and details for each is auto populated. Need to be able to have multiple entries, 10 - 20 items per form.

    Thanks

  • AnkeetModi

    helllo,
    i tried testing the data but i get the error "undefined"

    what does that mean?

  • yenem

    Is the spreadsheet to form widget really working? Have tried several spreadsheets plus the one given to use as an exemple but non seems to work.

    Would really appreciate your feedback on this.

  • tireprosatd

    I am trying to use the spreadsheet to form widget but it is not working. I have checked my access code columns and label rows and everything looks right. Does this widget no longer work?

  • awgahr

    I created my form and uploaded the excel file but when I publish the form and enter a unique ID from the spreadsheet, the form just says "loading" and does not ever change.
    My column headers match the short text entry labels.
    Not sure what I am doing incorrectly...

  • Lrgunter

    I need to evaluate the a field that is filled from the spreadsheet file. Based on its value, I will show or hide more information. It seems like the field is not being evaluated.
    What am I doing wrong?

  • Lrgunter

    Is there anyway to customize the message "Cannot find access code"?

  • BetsyCohen

    I'm trying to use spreadsheet to form with the JotForm Cards and it's not working

  • motionconnected

    Is it possible to change the text that is shown below the button after it is clicked and has completed its operation?

  • saukin

    The validation message is in english and is just one... ¿is it possible to edit?... please

  • iwonamypa

    This looks very interesting. For what purposes do people use this widget?

  • khaidin

    How can i make this widget 100% automatically?

    I have intergrated my form with google spreadsheet. But i cannot use that google spreadsheet in my google drive account to lookup data. So I need to download it and save it in .xlsx.

    Is there is any solution for that?

  • LensLockers

    Does this work with any spreadsheet and does it have to be uploaded into the form?
    Does the spreadsheet need to have public access?

  • thomasrusso

    I keep getting Server Error when I attempt to upload my file. The file is on my desktop and is only 30kb. What am I doing wrong?

  • artistcraftsman

    Is there a way to use a dropdown to select the Unique Code to force the user to select a value on the spreadsheet? (And maybe avoid the need for the button to be pressed afterwards)

  • saukin

    OK... works only if you embed copying the code in iFrame option... at least for me

  • saukin

    Hi.. i have the same issue.... Every attempt to get it to populate the one form I have in my account results in "Unknown access code". When i try in preview form works like a charm but using in my web page the problem appears "Unknown access code". Like pdssa.....what am I missing too?

    desperately waiting

  • pdssa

    Hi,

    I have a spreadsheet loaded that has a unique code in one of the columns.

    Every attempt to get it to populate the one form I have in my account results in "Unknown access code".

    What am I missing????

    Thanks,

    Jim

  • sweetpeatles

    This is sooooooo cool and will be very helpful for us!! Thank you!

  • valensa_1

    What is the difference between using a matrix and this spreadsheet? I have been asked to create an interactive weekly calendar. The supervisor wants the ability to take notes during the day. So the traditional calendar but with the ability to fill in activities and takes notes during meetings and just anything. I have been successful however #1 I am unable to print the pdf version #2 I don't like the V4 version that keeps displaying but I cant get back to the v3 version to preview only for editing purposes. #3 the entire matrix doesn't show up expanded on the screen everything is somewhat compact. This needs to be something that is able to remain open as the day goes on. HELPHELPHELP

  • BxClinic

    Is version2 of this widget going to allow connecting to spreadsheets online (like Google Sheers) via URL?

  • BxClinic

    Wonderful. Amazing. Brilliant. Thanks :-)