While Google Forms does not include a built-in feature for sending confirmation emails to respondents, it's still achievable. In this article, we'll guide you through the process of setting up confirmation emails for Google Forms submissions. Simple follow the steps below.
Step 1: Connect Google Form to Google Sheet
This is a feature that is built into Google Forms. We will need the information in a Google Sheet in order to send confirmation emails.
- In the top left, in the "Responses" tab, select "Summary."
- In the top right corner, click "More," then "Select destination for responses."
- Select "Create a new spreadsheet."
- Click "Create."
Copy the URL for the new Google Sheet and save it for step 2.
Step 2: Connect Google Form Response Spreadsheet to Lido
We will use Lido to send our confirmation emails since this is not possible in Google Sheets. You can create a free account here: www.lido.app/go/signup.
Then follow these steps:
- Create a fresh Lido file
- Next, click on 'Connect Data,' and opt for 'Google Sheets.'
- Paste the URL of the Google Sheet obtained in Step 1, which contains your Google Form responses. You'll be prompted to log in to a Google account with access to that particular Google Sheet.
- Now, select the specific columns you want to import into Lido. Each column should correspond to a question from your Google Form. Make sure to choose all the columns you intend to include in your email notifications.
Click Add Data. This will create a Lido table of your Google Form responses.
Step 3: Write Custom Confirmation Email Templates
Now we will write custom confirmation emails that will be sent upon Google Form submission.
Set up a new worksheet for your confirmation email templates. Use [@Column] to reference table columns as variables. This allows you to insert information from the Google Form response directly into your confirmation emails.
Step 4: Set up Confirmation Emails
Next, let's connect our new email templates to our table. Return to your table and add a new computed column named “Subject”.
In the second row of this new computed column, enter the formula:
```=STRINGTEMPLATE(Sheet1!$B$1)```
Replace Sheet1!$B$1 with the actual cell where the confirmation subject template from step 3 is located.
Create another computed column and repeat this process for email body:
```=STRINGTEMPLATE(Sheet1!$B$2)```
Again, remember to replace Sheet1!$B$2 with the location of your email body template.
Your table should now resemble the below image. Notice how the variables [@Column] have been substituted with the real values from the corresponding form responses for each row.
Step 5: Add SENDGMAIL Formula
We'll use Lido's special SENDGMAIL formula to send emails.
Now, make a new column in your spreadsheet. In the second row of that column, put in this formula:
```=SENDGMAIL(<sender-credential>, B2, E2, F2, H2)```
Here is what each part does:
Sender credential: this is the email address confirmations will be sent from. You'll need to connect to a Gmail account ("credential") the first time you set up this formula
Recipient: B2, this is the email address confirmations will be sent to.
Subject: E2, this is the email subject
Body: F2, this is the email body
Status: H2, this is the spreadsheet cell where the result of whether the email has been sent will be displayed
Make sure to replace the different cell locations in the formula with the actual cells in your spreadsheet.
If you would like to copy yourself on each confirmation email, you can use the optional cc argument after Status to do so.
Right click on one of the SENDGMAIL formulas in your column and Run action to send a test email.
You will be asked to log in to the sender's email address the first time you use this feature. Make sure that the email you login to is a Google email account and that it matches the email address you've mentioned in the formula.
Once the email is sent successfully, you'll see a "success" message briefly appear in the cell. The cell you designated as the status cell will also now contain "success."
Step 6: Customize when Confirmation Emails are Sent
Now we need to ensure that only one confirmation email is sent per new form response.
First, add a Linked Column to your table to keep track of confirmation email sent status. You will be asked to choose an ID column. We recommend selecting Timestamp. If you did not connect the timestamp column, you can either choose a different column that is likely to be unique (like email) or click Edit data and go back to the data browser to add timestamp.
The location of your status column in the spreadsheet must be the same as the location specified in the status_cell for your SENDGMAIL formula (column H in this example). If this is not the case, then update your SENDGMAIL formula now.
Next we will add an IF statement to our SENDGMAIL formula so that it only displays if an email confirmation has not already been sent for that row.
```=IF(H2<>”success”,SENDGMAIL(<sender-credential>, B2, E2, F2, H2))```
The SENDGMAIL formula will now only display if a confirmation email has not already been successfully sent to that row.
Step 7: Automate Sending Confirmation Emails for Each New Google Form Submission
The last step is to automate running all of the SENDGMAIL formulas in the column. Click on the column header and select Run column on a schedule.
This will open the automation panel. Select a frequency for update checkng. On each automation interval, Lido will pull in your latest form submissions and send email confirmations for all rows that have not already received an email. In this example we will select 15 minutes.
Click Save. Then you are all set!
Now, when a new Google Form response is submitted, Lido will automatically fetch the information and send an email confirmation on your behalf.
We hope this article has helped you to make Google Forms send confirmation emails. You might also like our article on how to easily send Google Forms responses to multiple emails.
