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.
 

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.