How To Create A Prefilled Google Form From A Google Sheet

Prefilled Google Forms is a hassle-free and time-saving technique that helps you collect data without having repetitive data. Suppose you are an employer and have information about your employees but you want to record more details about your employees like their marital status, kids, etc. Getting all the information again from your employees is a time-consuming task and consumes a lot of their and your time.

In this case prefilled Google forms are your savior. Use this technique once and you are good to go with filling details of a large amount of respondents. It will also help you focus on your prioritized work. On the other hand, it will eliminate human error in creating wrong forms.

What is a Pre-Filled Google Form

A pre-filled Google Form is like a form that’s already been partially filled out before you even see it. This can make it quicker and easier for you to answer because some of the information is already there. It’s handy for things like surveys or sign-up form

What is the SUBSTITUTE Formula

The SUBSTITUTE formula searches the original text for instances of the old text and replaces them with the new text. It can be used to correct errors, update data, or simply modify text in a cell according to your specific requirements.

The basic structure of the SUBSTITUTE formula typically looks like this:

=SUBSTITUTE(text, old_text, new_text, [instance_number])

How to Create a Prefill Google Forms from Google Sheets

Here, I’ll be using the example of a school that wants to update the leftover information of students.

Sample Google Sheet

Example

In the above screenshot, a school wants to complete the information about every child. Column A has the Enrollment Numbers of the children admitted to the school, Column B has the name of the students, Column C has the number of the class in which the child is studying, Column D has the Contact Number of one of a parent and Column E has Father’s Name of the students. In the screenshot above we can see that there are blank cells in Contact Number and Father’s Name and the school wants that to be updated by the student’s parents.

How to Create a Google Form

The first and foremost step to start with is creating a Google form. Google Forms should contain all the fields that are contained in the Google Sheets. If there is any discrepancy in the fields, it will generate undesired results.

Step 1: Create a Sample Google Form

Create a Google Form with the same fields as given in the Google Sheet.

To start, create a pre-filled link, which will generate another link of the same Google form.

Google Form Created

Step 2: Click on Three-dots and Select Get Pre-filled Link

On the top right corner of the page. Click the 3 dots and Select Get a pre-filled link. The same form will open in the new window.

pre-filled links option

Add Dummy Details

Add the dummy details in the Google form opened after clicking the prefilled form option in the above step. Later in the steps, these details will be replaced to create fill-in the original details. Not filling in dummy details will not make this process work.

Step 3: Fill the Form with Dummy Details

Fill in the dummy details in the form(as shown below).

Dummy Details filled

Step 4: Click on the Get Link

Go to the bottom of the form and click on the Get Link button shown there.

Get Link Option

Step 5: Click on the Copy Dummy Form Link

Copy the form link and then paste the form link into the spreadsheet, from where the half-filled information is to be fetched and click on the Copy Link button.

Dummy Form Link

Step 6: Link the Google Form Template to Google Sheets

Now that we have the template, the next step is to connect your Google Form with Google Sheets, ensuring that the fields fill in automatically. We’ll utilize the template to generate multiple pre-filled Google Forms in bulk with the assistance of the SUBSTITUTE formula. To get started, follow these steps:

Step 7: Create a new Sheet and Paste the link in the A1 Cell

Paste the Formula in the new Sheet

Step 8: Return to the previous spreadsheet that contains the student information to be filled in advance.

Step 9: Now Select the Cell Under the Father’s Name (E3) and Use the Substitute Formula

How to Use Substitute Formula

To replace the dummy data with the data of the students we will be using the SUBSTITUTE formula of Google Sheets. The formula will replace all the dummy values with the values present in the sheets.

The formula looks like this:

SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

Where

text_to_search: The text within which to search and replace.

search_for: The text to search for within text_to_search.

replace_with: The text that will replace search_for

[occurrence_number]: This is an optional field. This is used for specifying the occurrence number of search_for. By default, all the search_for occurrences are replaced.

Use Substitute Formula

Step 10: Preview Result

Preview Result

Another Substitute Formula

We’ll be using another substitute formula under one substitute formula to substitute another value from the dummy form. The first substitute formula will replace the contact number and this formula will replace the Father’s Name. We can add as many substitute formulas to replace the values as required.

Step 11: Use Nested Substitute Formula

Another SUBSTITUTE formula is used to replace Father’s Name. This substitute formula will use the nested substitute formula’s result as text_to_search, DummyFatherName as search_for, update the, and the text in cell E2 as replace_with and update the results in the Google Form.

Father’s Name Updated

Conclusion

Prefilling Google Forms using the above technique takes less amount of time and it will help the respondents save time too. It is a win-win process for both parties. Also, creating a pre-filled form will not take much time on its own. All the 7 steps mentioned above are easy to follow and give fantastic results.

FAQ

Can two Google Forms be linked to the same spreadsheet?

Yes, two Google forms can be linked to the same spreadsheet using different tabs. You have to store the data in different tabs and then you can link as many Google forms as you want to be linked into that spreadsheet.

Can the same Google Form be used multiple times?

Yes, the same Google form can be used as many times as you want. You just have to delete the existing data from the old form and create new data. Alongside, you have to empty the spreadsheet linked with the google form. So that it can store fresh data collected from google forms.

Do I need to fill dummy names in the form?

Yes, you do have to fill the dummy data in the google form as shown in step 3. It is because of the fact that we are using substitute formula to fill in the original values. And as the name suggests, the formula will replace the dummy values from the form with the original values from spreadsheet.