How To Create A Prefilled Google Form From A Google Sheet

Creating a prefilled Google Form from a Google Sheet helps in sending personalized surveys or registrations. This technique allows you to automatically fill in parts of a form based on the data you already have, saving time and reducing entry errors.

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. Let's see how to Create a Prefilled Google Form from a Google Sheet.

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.

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.

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

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.

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.

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).

Step 4: Click on the Get Link

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

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.

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

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.

Step 10: 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.
 

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. By automating the form-filling process, you minimize errors and save valuable time. 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.

Also Read: How to Create an RSVP Form on Google Forms