Populate form fields based on Excel Sheet

Hi Jotform Support Team,

I would like assistance in creating a form with the following functionality:

• A user enters a specific value (e.g., an ID or code) into a field in the form.
• Upon pressing Submit, the form should check this value against an Excel file stored in OneDrive.
• If the value exists in the Excel file, it should display a corresponding result or message.
• If the value does not exist, it should display a message such as "Invalid" or "Record not found".

Is this functionality possible in Jotform? If so, could you please guide me on how to set this up—especially how to integrate the Excel file from OneDrive for live lookups?

Thank you in advance for your help!

Hi sdacwarranty,

Thanks for reaching out to Jotform Support. We have a Spreadsheet to Form Widget that you can use to populate the form fields based on a value from your Excel sheet. Let me show you how to use it:

1. Create a spreadsheet with your data structured like you see below. Make sure that the column names in the spreadsheet match the field names on the form, and include a unique column, like an ID, so the data can be distinguished.

2. In Form Builder, click on the Add Form Element menu on the left side of the page.

3. Click on the Widgets tab, search for the Spreadsheet to Form widget, and add it to your form.

4. In the menu on the right side of the page, upload your data file. (You can simply drag & drop your Excel file to the Upload File area.) 

5. Then, click on the Downward Arrow icon in the Access Code Column option and select the unique column.

6. You can also customize the texts used in the widget. After you complete everything, click on the Update Widget button at the bottom right of the menu.

Now, when people fill out your form and enter the unique code from the spreadsheet, the corresponding fields will automatically populate when they click Auto Complete Fields. Check out my screencast below to see how it works:

Do note that this method works only with the uploaded Excel Sheet. If you updated the sheet's data, you need to reupload the Excel Sheet again to the widget.

Give it a try and let us know if you need any help.