Google Forms – Detailed guide to send custom response emails

Related Articles

KubeCon Salt Lake City – My 2nd International Talk in 2024

5 days ago

Let me start this post by asking a question, how many of you get link to survey forms via WhatsApp/Email from your friends ? I’m quite sure that most of you would have an affirmative answer to this. One of the advantages of technology that is helping researchers and students is online forms.

Ever since these online forms like Google Forms, Microsoft Forms, Survey Monkey et all – have come in, conducting surveys have become a piece of cake. These online form services come in handy as they provide a lot of options like type of questions, branching, response emails and so on. Google Forms is one of the most widely used online form and the credit goes to them for integrating is seamlessly with the gmail or GSuite account.

Need for Custom Response Emails for Google Forms

What I’m going to be talking about in this blog post is a challenge many people might be facing. A couple of options that Google Forms provide is confirmation message and response mailers. What it does is that when a respondent has submitted the form, it shows the confirmation message to the user and allows them to receive the submitted responses. Now, you can customize the confirmation message, but what if you want to send custom response emails to all the respondents ?

Say, you have a hotel reservation form and upon successful submission of form, you want to send them a unique reservation id. These type of custom response emails for Google Forms aren’t available directly. And you need some type of programming knowledge to achieve that.

That’s what exactly this post is all about. How to send custom response emails to Google Form responses.

Pre-requisite for custom response email

There are a few things that you will need to setup in order to send custom response emails for Google Forms. Trust me this is a completely FREE way to send response mailers. You’ll just have to set this up once and forget. You need to use any paid software to send custom response emails to your users/customers.

So before we dive into the exact script for this, here are the pre-requisites. Create a form in Google Forms with the following options enabled:

• Collect the email address of respondents. To do this, you need to navigate to Settings of a Google Form and check the box next to “Collect Email Address” This is mandatory as we need the email address to send custom response emails to. In case you don’t activate this, make sure you collect email addresses from all respondents.
• Store answers in spreadsheet. This option is enabled by default. But make sure that the Google form responses are stored in a Google sheet. We will be picking the data from this sheet.

These are the two things that must be enabled in your Google Form to send custom response emails. Let’s dive into the coding bit. This can be overwhelming, but trust me if you follow these steps exactly as mentioned, you’ll not have any issues.

How to send Custom Response Emails to Google Forms

Open the Google Form for which you want to send custom response emails. Make sure it has some responses. If not, fill one with a valid email id.

Click on Responses tab and then click on the green spreadsheet icon that says “View Responses in Spreadsheet” This will open the spreadsheet with the responses.

Navigate to Tools and click on Script Editor. This will open a new tab with a new Untitled Project. You should see a Code.gs screen with a a couple of lines of code already written.

Adding the Code

Now it’s time to add the code. The code that I’m sharing is essentially have two methods:

1. The first method is the actual one that will do the work of pulling the email id and sharing the email.
2. The second method is going to fetch the last row from the responses. This ensures that the mail is sent only to the person who submitted the form last.

PS: Do note that there might be million other ways to write this code and in a more efficient way. I found bits of this code on the internet, have combined it to my use.

// Method to Send Email
function myFunction() {
 var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = getNonActiveRows(); // First row of data to process
  var numRows = 1; // Number of rows to process
  // Fetch the range of cells
  var dataRange = sheet.getRange(startRow, 1, numRows, 2);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i in data) {
    var row = data[i];
    var emailAddress = row[1]; // First column
    var message = 'Hey, this is a custom response from Socialmaharaj.com'
    var subject = 'Sending emails from a Spreadsheet';
    MailApp.sendEmail(emailAddress, subject, message);
}
}

// Method to get the last row from the spreadsheet
function getNonActiveRows()
{
  var lastRow;
var data = SpreadsheetApp.getActiveSheet().getRange('A1:A500').getValues();
for(var i = data.length - 1; i>=0; --i){//bottom-up loop
  if(data[i][0] !== '') break;
}
  lastRow = i+1;
  return lastRow;
}

After adding the code, save the project and provide a name. You might need to change the column number based on which column the form is storing the email address. In this case it is the 2nd column At this point of time, we have the code ready to send email.

However, we need to tell Google Forms when to trigger this code ? For that, we will have to work with Triggers.

Adding Triggers

Trigger is an activity that will start our code. Google provides a list of triggers that we can use. But what we need is to send the custom response emails only after the form is submitted, so let’s see how that would work.

Go to Edit and Select Current Project Triggers. This will open a Google Apps Scripts dashboard in a new tab with the project name you provided earlier. If you are visiting this page for the first time, you will see a My Triggers page with a no filters.

Go ahead and click on Add Trigger button. In the new Add Trigger page, you need to leave all the options as it is, except “Select Event Type” Choose “On Form Submit” from the drop down and hit save. You might be required to provide permissions to save this script. Please provide the permissions.

Testing the script

This will create a trigger for our script to run. What we did now is that we created an automated flow in which whenever a response is submitted to this form, it will trigger our code which will in turn pick the email id from the spreadsheet and send the custom response emails.

To test the trigger, simply open your Google Form and submit the details. If all is correct, you will receive an email to the mail id submitted with the custom response. Do note that the email will be sent from the account that has created the Google Form.

Voila ! You’ve successfully sent custom response emails !

Send Custom Response Emails for Google Forms

I know this might be a little overwhelming for a lot of non technical folks, but this was the only way I was able to send custom response emails from Google Forms. In case you aren’t able to get this working, I would be happy to help you out. Just drop me a DM on Twitter. I feel this is a great trick from freelancers to SMBs to send custom response emails. Without investing in 3rd party applications to do this for you, you are able to send custom response emails with minimal coding.

Do note that you can customize the response email to any extent as it just requires the code to be changed. You can add more inputs from the spreadsheet to your response emails, or you can have conditions based on certain inputs to send different email basis the chosen options etc. Isn’t this a great Google trick ? Let me know your thoughts in the comments below or tweet to me at @Atulmaharaj or DM me on Instagram.