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 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:

    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.

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:
Contact JotForm Support: https://www.jotform.com/contact/

Send Comment

105 Comments...

See all comments
  • pmjlc

    There appears to be a serious limitation of the ‘Spreadsheet to Form Widget’. Being able to use a spreadsheet to pre-populate fields on loading a form is extremely useful when the index value is say a date or time you want to use to lookup some values in a row matching the index. If for example you create a pricing table where the date the form is loaded determines the cost, then you can’t achieve that without the user having to click on the “populate fields from spreadsheet” button.

    Other uses such as using a QR code to determine the lookup value and resulting data to populate the form is another example where this would be useful.

    The omission to provide the option to use a default index value appears to be is a real oversight by the developers. You can already hide the widget (in properties/advanced) though I’m not sure what good that is unless they implement a default value.

    Would be great if Jotform would reconsider the priority on implementing this.

  • qhuifong

    Can have more than 1 uniquecode to populate the fields?
    Meaning using 2 selection e.g. a dropdown selection + short text to populate the fields....

  • kbmandir

    This is a great feature but we would like to check if we can have search based on two columns rather just one like email id + last name. Is this possible?

  • kevinreynolds

    Is there anyway this could be either a link to a live Google Sheet OR a drop down menu in the column of choice "A", "C" etc. This would be amazingly helpful and powerful. Thank you - good job

  • herman925

    Can I use fuzzy search logic or even autocomplete into this widget? e.g. a list of countries

  • viuproduct

    Can I use the form's own unique Submission ID as the Access Code Column for this "Spreadsheet to Form" widget?

  • summitmn

    There is a great opportunity with this widget. I see how it could save the end-user a lot of time. Any idea when it can link to address fields?

  • shirli

    Is this Spreadsheet to Form Widget HIPAA compliant? i.e. work with JotForm HIPAA Account?
    Thanks.

  • afachritesting

    Hi, this is cool widgets, but can you guys please update the widget so it can support the Appointment fields?

    this would be very helpful, thank you!

  • Esteban

    Is there a way to change the .input-box type?
    I want to use email as Unique Code, and i need to change de .input-box type to email, instead of text.

  • Nojusss147

    If if you upload a spreadsheet is there a way to download it

  • plandesignconsultants

    Wish item:

    I know it's not a supported field, but this would only really be of great use for us, if we could load data into the 'Configurable List' widget.

  • njw6202

    Is there a way we can link an online spreadsheet or can we request that functionality?

  • KOBNPL

    Hello,

    We also have the following problem when we push the button to populate the files :

    Internal error: The filename /www/app-widgets/live/user_files/KOBNPL/KobJ201007.5f7da8ed5ae083.21681177.XLSX is not recognised as an OLE file

    After testing it seems that with an "xls" file it seems to work, so it is the "xlsx" file where there are issues with. maybe this can help the developers to solve the problem.
    So, workaround is to change your xlsx to a xls for now.

    Best Regards, Benny De Coen

  • debyan

    Hi
    Spreadsheet to form widget doesn't work today.
    Work just fine until today
    thanks
    https://forms.cisssca.com/203033889218054

  • S_Health

    Hi we're having the same problem as everyone else. Can you please help? It seems this issue is on your end and it's greatly affecting our business.

  • TREF_forms

    Hello,

    The Spreadsheet to Form widget can be set as a required field, but it doesn't operate as a required if successfully validated. Is there a way to require the user to hit the button before moving submitting the form? Presently a user can simply type anything without pressing the button to satisfy the required condition.

  • hrlmw

    Hi,
    I have same problem with others. Everything work fine until this monday, all fields don't populate data and not show any errors.
    My form https://www.jotform.com/build/202031916491045

  • MJoad

    I would like to create a form using the Spreadsheet to Form widget with a variable number of multiple-choice checkboxes for each of 100 surveys I send out, and the names of each checkbox in each survey will be different. So for the first survey, there are two checkboxes visible in the survey and their names are: CheckboxName1 and CheckboxName2. For the second survey, there are three checkboxes visible with names: CheckboxDiffName1, CheckboxDiffName2, and CheckboxDiffName3. For the third, there are two checkboxes: CheckboxYetDiffName1...etc. For the fourth survey, there are nine checkboxes. Is this form with variable number, uniquely named multiple-choice checkboxes possible using the Spreadsheet-To-Form widget? Possibly in combination with a Configurable list widget? Thanks for your assistance.

  • claybenjamin

    is there a way to accomplish this same concept using the jotform tables rather than an external source? I have one form that populates a table and I want to use that table to populate a different form.

  • HOMktg

    Haven't changed anything on the form and suddenly yesterday, the fields don't populate and the widget is showing an 'internal error' message. I noticed that my .xlsx file is showing up as a .doc file and upon several attempts to upload the .xlsx file again, it's still showing .doc in the widget and is not working.

  • Matthew

    Same issue here. Everything was working fine. Now, at least on mobile Safari, fields are not completing. This is a pretty big problem - forms just don't work now.

  • twizzlas02

    Good morning,

    Having similar issues as gilesoh. Spreadsheet populated fine last week and now no fields are completing (but no error message). Please advise.

  • gilesoh

    Hi, my spreadsheet to form widget worked correctly yesterday.

    No changes were made this day but now it does not populate even a single box.

    This error applies to all my forms that use this widget.

    Kindly advise?

  • newlifefc20

    Is there any way to get some direct help with this. I am not understanding the instructions. I am not getting the same field in Step 3 and the instructions below are confusing. Can someone call me?
    801-635-4554? Thank you

  • ThreeFeathersBearScare

    The widget used to populate all the fields on my form if they had the same Column name. This stopped happening a few weeks ago and we have been struggling with getting the fields to populate.
    We have Employee_Name in a 4 places on the form that require filling. I used the widget to fill these automatically but now it will only fill in 1 field out of the 4. Did something change in the programming?

  • Matthews_Warwick

    Ho there - I really don't understand the "Unique Code" - where do I find this please

  • ddemitrius

    How do I change the color of the font in the widget when it gives an error code

  • jtte86

    Buenas esta muy genial la herramienta del Widget de Hoja de Calculo pero lamentablemente no he podido configurarla , comprendo el proceso hasta el punto 4 que es donde se carga el archivo después de este punto no he podido conseguir que me muestre la información, agradecería más especificaciones del cuadro de Acces code column-Labels Row- y con que extensión debe aparecer el archivo al cargarlo ya que me aparece .Doc o Pdf.

    De pronto es que estoy haciendo algún proceso mal.

  • Kenny Gilles

    Hello, how Can i do this with airtable ? Thanks

  • Consorcio_Salud

    Quisiera poder personalizar el botón del widget, el que viene por defecto no me gusta. ¿Cómo dede CSS lo llamo; he intentado con .btn .button .input_77 y ninguno me funciona, me podrían enviar una imagen con un ejemplo. Gracias.

  • HuttonSafetyGroup

    The widget is good. Is there a way to have the form automatically populate itself without the "auto complete fields" button ?

    We are scanning a QR code which generates a specific url and it would be great if the form would just autofill automatically. (https://form.jotform.com/201774608478061)

    Are there any plans of making this possible?

    Best regards

  • esteel

    I am using the Spreadsheet to form widget and uploaded my file of data. The form is active and has submissions. Can I upload a new spreadsheet adding additional unique code lines to my list without affecting any of the already completed form submissions?

  • tanda11

    Is there any way to have the form automatically populate itself without having to click the "auto complete fields" button on page load?

    I'm trying to do this: A link is sent with the unique code in the link like this: https://www.jotform.com/xxxx?uniquecode=123. Then when they click on that it would take them to the form and populate it without them having to click the button.

    Thanks

  • PneusPros

    Hi. Is it possible to update the Spreadsheet to form data via an api or web service of any automated method?
    Thanks
    Christopher

  • InfiniteEye

    Great tool!! I'm a new user and having some trouble.
    Maybe I'm not familiar enough with the program yet. But, can I have multiple unique codes for a single row? If the unique code is an email address and that is associated with data, can the program look at more than 1 field for the unique code and bring up the associated data?

  • pollinatorpartnership

    Thanks! This tool is great.

    Do you know when it will be able to take number and address fields?

  • Ssarav01

    How do you use this if your form has translations?

  • robertmk20

    Hi,

    This is an awesome feature - can this feature be used offline as well?

    Thanks
    Robert

  • waynelaidlaw

    How can I fix the numbers to show only 2 decimal points in the spreadsheet to form.
    The spreadsheet number is .67 but it shows .67155898999 (for example)
    I have toggles the limt entry characters on but that did not work
    I have limited the characters to 4 and that still did not work.
    Also can you use this in a table format?
    Thanks in advance

  • foegler

    Hello, this feature could bring a lot of value to me.

    Is there a way, using the Multiple Choice (Checkbox), to pass along a value through the "Other" option? So a value that is not predefined?

    Thanks!

  • JDog84

    I have tried to use this widget and I select the unique key. It says "Fields Have Been Auto Filled" but I check the fields that are labeled with the column headers and nothing shows up. Did I miss a step in the setup?

    Form address is https://form.jotform.com/200637636093153

  • bfackrell

    This is pretty cool but, is there a way to open an excel file from within the live form to populate the data?

    I don't want to have to go back into the form design and upload a new Excel file each time. May data will be different each time I use the form.

  • Western_Shield

    HI, Just wondering how I can use a spreadsheet to populate a field based on an answer to a previous field. For example species of animal have a common name and a scientific name. I have created a dropdown list of common names but I want the jotform to look at that name and then select the appropriate scientific name from the excel spreadsheet and insert that into a another field. There are over 100 names so I didn't want to create a conditional logic for each option.

    my form is: https://www.jotform.com/build/200647457102853#preview

  • BNRATHI

    Can we use a Google or Jotform Spreadsheet instead of file upload.

  • PESJotForm

    How do you make a pulldown field populate the speadsheet to form field? I saw one thread on the update/calculate field but cannnot find this function.

    Thank you.

  • Jackson_Matt

    Hi. How can we do this using google sheets instead of Excel?

  • Teiyonab

    Hello, I'm currently using the Spreadsheet to form widget. I keep receiving this error message, "There are errors on the form. Please fix them before continuing". Please assist.

  • essilorvisionfoundation

    Is it possible to point this at a spreadsheet that is hosted on the web or a Google Sheet so the file can be updated with new values?

  • Hasan

    What a great and amazing widget! Does this work also on conditional drop-downs?

    Example

    Are you registered with ASA?

    Yes / No

    No - nothing happens
    Yes - please input your ASA ID

    So can the ASA ID be pre-populated as well given that it is a field that only shows up on the conditional YES answer.

    Thanks