Create Pre-filled Google Forms using a simple Google Sheet Formula

Welcome!

I will teach you to create pre-filled Google Forms thanks to Google Sheets. That is made possible with the SUBSTITUTE formula.

Why send pre-filled Google Forms?

If there is one thing that people hate, it’s filling forms with the same information over and over again. You probably already have information such as their name, roll number, and other data. Use this data to make people’s lives easier with pre-filled forms!

What is the SUBSTITUTE formula?

The SUBSTITUTE formula is used to replace existing text with new text in a cell.

Step 1: Sample Google Form

The pre-filled Google Form that we will be sending out is a form that will ask the students whether they want to use the offline mode of study. Using the formula we can fill in the student details such as name, roll number, and more so that the students can focus on the important questions in the Google Form. The form contains the following details:

1. Name of the student
2. Roll number of the student
3. Division of the student
4. Do you want to continue school offline?
5. Any suggestions?

Step 2: Sample Google Sheet

The Google Sheet contains the details of the students that we will be used to pre-fill the Google Form:

1. Name of the student
2. Roll Number of the student
3. Division of the Student
4. Offline school question
5. Suggestions for the school if any

Step 3: Create a Pre-filled Google Form

To create mass pre-filled Google Forms, we need to create a template Google Form link with the required fields for information. To do that follow these steps:

1. Click on the three dots at the top right of the Google Form and click on Get pre-filled link.

2. On clicking Get pre-filled link, you will be taken to a new form where you should input dummy data.

3. After entering dummy data you will see a button that says Get Link. This link is the pre-filled Google Form link in which we will be substituting the new values using the formula. The google form link will get copied to your clipboard.

Now that we have got the template it’s time to use it to create bulk pre-filled google forms using the SUBSTITUTE formula. To do that follow these steps:

1. Create a new Sheet and paste the link in the A1 cell.

2. Switch back to the previous Sheet that has the student details that need to be pre-filled.

3. Next select the cell under the Google Form Link column.

4. Start the formula by typing SUBSTITUTE (

5. Here we will pass the following parameters in the SUBSTITUTE Formula:

• Sheet5!$A$1: Here I have passed the name of the sheet followed by the range where the Google Form link is stored using absolute cell referencing.
• DummyName: Here I have passed the DummyName parameter that we created. This formula is going to search in the range specified above for the text specified and replace it with the next parameter.
• B3: Here I have specified the column that we want to replace the search for data with. For this question, we have replaced the student’s name with the dummy name in the Google Form link, so I am going to specify the B3, which contains the name of the first student.

6. After you close the formula and open the Google Form, you will see that the name question in the Google Form contains the data present in B3.

We are going to do the same for all the questions present in the Google Form. To do that we are going to add as many substitute formulas as there are columns of information to pre-fill

The final SUBSTITUTE formula will look as follows :

=SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (
Sheet5!$A$1,"DummyName",B3),
"A001",A3),
"B",C3),
"Yes", D3),
"Keep+hand+sanitizer+dispensers+in+the+school",E3)

Here we have started by mentioning the name of the Google Sheet followed by the cell where the Google Form Link is stored. Then for all the times, we need to SUBSTITUTE we have mentioned the value in the Google Form followed by the cell with which has to be replaced.

Step 4: Check the Output

After saving the formula, the pre-filled Google Form will look something like this.

Here you can see all the data from the Sheet are pre-filled, and only new questions remain empty.

Originally published on YAMM.

I hope you have understood how to create pre-filled Google Forms using a simple Google Sheet formula. In the next blog I am going to show you how to send out these pre-filled google forms link to the students on their email using the popular mail merge Add-on YAMM.