How to use a spreadsheet to validate form access?

We need to do a form collecting comments from a subset of our staff. Is it possible to upload a spreadsheet of employee id numbers and then have the employee input their id on the form where it is validated against the spreadsheet of allowed values before allowing comments to be entered and submitted?

something along this workflow:

(1) enter your employee id number

(2) validate entry against spreadsheet of allowable numbers

(a) id found, you are authorized to complete the form - display form; or

(b) id not found, you are not authorized to complete this form - exit

Hi talwaweb,

Thanks for reaching out to Jotform Support. It is indeed possible to use a spreadsheet as a means to fully access a form for users to fill out. This can be achievable by using the Spreadsheet to Form widget, so an external data from a spreadsheet can be used as validation to fully access your form. Incorporate that with Conditional Logic to show/hide fields based on the form user's answer, and unwanted submissions can be avoided if the wrong employee ID has been entered. I'll be guiding you on how to create one, and you can use this as the basis for the form you'll be creating based on your preference. Before we proceed, here's a screenshot of the sample Excel file I will be using for this guide, which needs to be entered in the said widget:

Let's start:

1. Assuming you have a new form ready, click Add Form Element on the left and go to the Widgets tab.
2. Search for Spreadsheet to Form widget and add it into your form.
   
3. Automatically, the settings of the widget will show on the right side of the page. Click Upload File and add the spreadsheet to be used in the form.
4. Once uploaded, go to Access Code Column and select the data to be used for the widget.
5. Scroll down and choose Yes for Submit Input Field Value. This is optional if you want the entered data to be displayed in the submissions.
6. Scroll further down and select YES for Reset Fields When Invalid. This is to make sure that the data will be automatically deleted if the wrong one was entered.
7. Go to the other options available so you can modify them. Once done, hit Update Widget.
   

After that, you can now add any other fields necessary to complete the form, like Name, Email, or anything. Once you're done, you can now set the Conditional Logic for your form, as mentioned earlier. Here's how:

1. After adding the necessary fields in your form, go to Settings on the orange navigation bar of Form Builder.
2. Select Conditions on the left panel and from the list, choose Show/Hide Field.
3. From there, start adding the conditions.
   
4. Here's how the condition should look like:
   
   As shown on the screenshot above, the fields will only show up if the widget has been filled with the correct information. Unless met, they will stay hidden within the form. Hit Save once done.

Let's now test the form. Here's a preview of the form if the data entered is incorrect:

And here's when the correct data is entered:

Check out this demo form so you can use it as basis to your own form. Feel free to clone it if necessary.

Give it a try and let us know if you have any other questions.

This is AWESOME!!! Thank you.

Are we able to turn this into a public post so others may benefit from your hard work too?

Hi talwaweb,

Thanks for getting back to us. We're glad that the solution worked for you. I've set this thread as public as requested.

Reach out again if you need any more help.