Restricting Form Responses for Each Option in Google Forms

Welcome!

In this blog, I am going to show you how to restrict responses for each option in Google Forms. We are going to do this using some basic Google Sheets formulas and a Google Form Add-on called FormRanger.

So let’s get started.

https://youtu.be/tavBzKPSphM

If you need to refresh your knowledge on using Google Forms, click on the link given below.

Create a Quiz with Google Forms

Step 1: Sample Google Form

The form that I will be using is a Simple Session registration form . (If you prefer working with a copy of the Google Form, click here)

This form contains the following:

• Name of the Attendee
• Email address of the Attendee
• Session that the attendee wants to attend. (He will be allowed to choose from 3 sessions)

Step 2: Sample Google Sheet

I have created two sheets. The first sheet will contain all the form responses entered by the user while filling the Google Form. This is the first Sheet.

This is the Second sheet.

This sheet contains the following details:

• The message that has to be shown when the Session limit has been reached.
• Session Name
• Session count: This column will contain the number of people applied for each session. This will get automatically update from the Responses sheet using formulas.
• Session Limit: This column will contain the maximum number of people who can apply for the session.

Step 3: Load up the Formulas

Now we are going to write a formula that will count every time a new response is added for Session1.

=COUNTIF('Form Responses 1'!D:D,B2:B)

This is the formula that you have to write in C2. Now let’s understand what this formula does.

• This formula will count the number of responses in Form Responses column D and compare it to values in B2.
• After writing the formula go ahead and click enter.
• For now, you will see that it is showing you zero.

Now carry the formula to all three sessions. Now you can see that all the Columns have zero.

Now that we have set the session limit and the session count using formulas, so now its time to add some formulas that will change the display name as soon as the session count equals the session limit.

=IF(C2<D2,B2,"This session is full,Please try again later")

This is the formula that you have to write in A2. Now let's understand what it does.

• This formula checks if the Session count is less than the Session Limit.
• If the session count is less than the session limit, it does not change anything.
• On the other hand, if the session count is equal to the session limit it displays the text, that says the session is not available.

Now carry the formula to all three sessions. Now you can see that since there are no responses in the Google Sheet, the text has not changed.

Step 4: FormRanger Add-on

Now we have got all our formulas set up, it’s time to add the add-on that will make the changes in the Google Form.

To add the Add-on follow these steps:

• Click on the three dots next to send

• Now click on Add-ons

• Here you can search for Add-ons according to your requirement. But for now, search for Form Ranger.
• After searching Form Ranger, you see many of them but for now, install this one.

After you are done installing its time to use the Add-on. To do that follow these steps:

• Click on the puzzle shaped emoji

• Then you have to select the Form Ranger Add-on and the click on start.

• When you click start you will see that the Add-on has appeared on your screen.

• So now we have to specify the sheet that we have to use.
• So click on Populate from range and then click on the plus sign next to it that say New range .

• Then you have to select the Form Responses Google Sheet.

• Here it is going to ask you to Select a sheet, so we will be using the second sheet.
• Then we have to specify the column header, and we are going to select the Session Unavailable Message header as we have added the formulas in it.
• After making the changes click on Next .

• Since we have more than one session , we have to go through each session individually, so we are naming the range as Session1 .
• Now go ahead and click Save and populate question .

Now click on On form submit. This automatically changes the values as soon a new responses comes into the spreadsheet.

Now let’s go ahead and make a response in the Google Form.

Here you can see that I have filled the form. As soon as you click on Submit go back to your Google Form.

Here you can see that the question has been replaced by the Session Unavailable Message. Now when someone else will fill this form they can see that Session1 is not Available. If you want custom messages for each Session you can go back to the Google Sheet and edit the formula for each session.

This is all for this blog. I hope you have understood how to restrict responses for each option in Google Forms. You can send your feedback to aryanirani123@gmail.com.