Create and send pre-filled Google Forms with Google Sheets

October 28, 2021
Create and send pre-filled Google Forms with Google Sheets
Table of Contents
Table of Contents

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.

Step 5: Send out the pre-filled Google Forms using YAMM

Now that the pre-filled Google Forms are ready for all the students, it's time to send them over to the students. Doing this manually will be a very hectic task, we can automate this process by using Google Apps Script or the YAMM add-on.

Before we get into creating the draft for the email, we have to install the YAMM add-on to our Google Sheet. I have covered these steps in my previous blogs, click on the link to know more.

Step 6: Create your Email Draft

To create the draft, you have to follow the same steps that you follow to write an email. To create your draft, follow these steps:

  1. Go to gmail.com
  2. Click on Compose and start writing your email draft.
  3. We have to replace the variables in the draft with the data in the Google Sheet. To do that you need to specify the column names that you want to replace in the draft like this.

Copy the contents of the email:

Dear {{Name of the Student}},


Your roll number is  {{Roll number of the student}} . Now that the covid cases have dropped, we are planning to start school offline. 

Here is the Google Form : {{Google Form Link}}
In this Google Form, fill in the necessary details. 



Regards,
Moderator
Passion Education School

4. Here I have done the following:

  • Here I wanted to put the name of the student at the beginning of the email, so I put in the column name where the names are stored.
  • The same is for all the other variables that I have called in the email draft.

5. After you are done creating the draft, save and close the draft.

Step 7: Send the Emails

Now that we have installed the YAMM Add-on, created the email draft, it's time to send out the emails. To send out the emails, follow these steps:

  1. Go to Add-ons and click on Yet Another Mail Merge and start the mail merge.

2. On clicking Start Mail Merge, it will bring up a box where you need to put in some details.

Here you need to specify the sender name, followed by the email template that you want to use.

Now that we have added all the features that we need, let's send out the emails. You can do the following:

  1. Send all the emails
  2. Schedule the emails
  3. Send a test email to check whether everything works fine.

For now, we are going to go ahead and send the emails by clicking on Send 10 Emails.

Step 8: Check the Emails

Here you can see on successful execution the email has been sent successfully and all the details have come in properly.

Here you can see all the details that we had specified have successfully come into the email.

In this blog, we covered how to create pre-filled Google Forms using the SUBSTITUTE  Google Sheet formula. After creating the pre-filled Google Forms, we sent the forms to the students on their email addresses using YAMM. You can send your feedback to aryanirani123@gmail.com



Great! Next, complete checkout for full access to The Yet Another Mail Merge Blog
Welcome back! You've successfully signed in
You've successfully subscribed to The Yet Another Mail Merge Blog
Success! Your account is fully activated, you now have access to all content
Success! Your billing info has been updated
Your billing was not updated