Category: FormApp

A few days before publishing this post, I put a call out for some Beta testers to get free access to my new course Google Sheet: Learn the Essentials through 3 Detailed Projects. 

Yeah, yeah, yeah, I am super excited about finishing this course. It’s been a labour of love for two and a half months. No wonder I am banging on about it.

Anyway back to the tutorial. I needed a way to provide coupon codes for free access to the course in exchange for some feedback and much-needed tutorials.

To do this, I created a Google Form.  This contained some details and expectations, and then some details about the submitter and a consent checkbox at the end. If the submitter consented and hit submit I wanted them to get an email back with the coupon code.

In this tutorial, we will go through how to create a custom auto email response containing:

• The submitter’s name in the greeting.
• Your email message.
• Your primary signature block from your Gmail account.

To me, this seems like a pretty standard task that anyone might be considering doing. So, I thought I would share the process and the code.

Let’s get cracking!

The Google Form

Here we will go over the highlights and setup as they pertain to the context of the Google Apps Script code.

The form starts off with some information about beta testing the course.

Next, we have the input fields.

1. Email: This is an atypical field. You create this field by going to the Settings taband select  Collect email addresses. Here, I also sent the submitter a copy of their responses and limited the responses from each user to one. This made the user use their Gmail account, which added some extra integrity to the form and… well… the user will need a Gmail account to complete the course anyway. This will become important in our code later.

2. Name: The name of the Beta tester. I’ll grab the first name from this and also record it as part of their testimonial that they consented to.
3. Occupation (optional): This apparently helps with the credibility of the testimonial and it is also interesting to see the occupation of the people taking the course. Testers can opt-out of this one.
4. Company (optional): This one was a fizzer. I understand why, you really don’t want to be telling people where you work online. This item was added as part of the testimonials that I researched in preparation for this. I have no idea how course creators got these, which makes me one part, a little suspicious and, one part, blown away by how these folk got students to share who they worked for. In hindsight, I would not add this item. If I personally wouldn’t give out these details, I shouldn’t expect others to.
5. Consent: This is a mandatory field that the submitter needs to check to confirm their expectation in exchange for the free course. If they don’t check the box, they can’t submit the form and get the coupon code.

The code

We want to get our email auto-responder code to run when the Google Form is submitted to us.  To do this we need to connect our script to the form by opening the Google Apps Script editor up in the Google Form.

You can do this by clicking the vertical ellipsis (⁝) in the top-right of the Google Form editor and selecting Script editor.

Code.gs

You can copy and paste this now into your project and rename the project to whatever you are working on. I usually name my project the same as my Form or Sheet if it is directly related.

You can update the subject and htmlBody values to your own data here. You can add HTML to your htmlBody within backticks (your html) Lines 27 & 28

Of course, the coupon code is a big faker-Rooney.  🙄

The code grabs the current users email and name and then emails the respondent with a coupon code after they hit submit on the Google Form.

If you run the code right now you will face some errors. First, we need to set up the trigger to run this code when the user submits it. Also, we need to get authorization to use scopes, or permissions to give Google Apps Script certain access to your data to run the code and add in an API to access our Gmail signature block.

Add the Gmail API

First up we need to add an advanced API to get access to be able to display the signature block from our Gmail account. You know, to make it look professional.

To add this API in the Google Apps Script editor:

1. Ensure you are in the Editor.
2. Select Services +
3. Scroll down and select Gmail.
4. Select Add

Note! As of writing this, the Gmail API only uses your primary signature block and can’t access any other signatures you might have. 

Make sure the Scopes have been Authenticated

You may find, even if you have set up the triggers, that the script doesn’t run because it is missing the permissions you need to run the script. To find out what happened you can check out the execution log in the Google Apps Script IDE  sidebar -4th one down.

You can then check the errors of your execution log to see what happened.

To fix this ahead of time we can modify the onFormSubmit(e) function a little to run a dummy version of the script.

Before we start, you need to have a least one response in your Google Form. Go ahead now and add in a dummy form submission. You can always delete it later.

Go back into your editor create a new Google Apps Script file and call it Test.gs.

Copy and paste in this code:

Go ahead and run the code. You should get a warning popup for authentication. This will happen just one time and it will be fine for you and anyone who enters the form in future.

Once you have accepted all the scopes your code should run and you will get an email. If you don’t, check the execution log for any errors.

You can also check to see your script scopes by going to the Overview of your Google Apps Script IDE.

Now we can go back to our Code.gs file and add our trigger to send the email on submission.

Assign the Trigger on Submit

Next, we need to assign a trigger to run our onFormSubmit(e) function in our Code.gs file.

To do this, navigate to the Code.gs file and then:

1. 1. In the sidebar of the IDE, select the Triggers button indicated by a clock.
   2. Select Add Trigger at the bottom right of the page.
   3. A popup window will appear. Under Choose which function to run select onFormSubmit.
   4. Select event type: On form Submit.
   5. Click Save.

Click to Expand!

Run a practice Form Submission

Go ahead and run a dummy form submission again and you should now get an auto-response email with the email you created.

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

Code breakdown

Pfaawhh!!! 

If you made it to this bit you really want to learn about the nuts and bolts of the script. That’s awesome.

In this, code breakdown, I will go over some of the highlights of the code to help you understand what is going on.

Get the current submitters data

You will notice the single ‘e‘ as a parameter for the onFormSubmit function. This indicates an event object. When a trigger, like our on form submit trigger, is assigned to a function, you can use the event object to get the data that will be drawn from triggering that event. For us, we will get the response from the person who submitted the trigger.

To do this we use the response method on our event object and assign it to our responses variable. Line 3

This will give us access to the same list of methods as you would when you run the Test.gs testAndAuthScopes function’s response variable on the first response in the form. 

const responses = formResponses[0];

You can find out more options about the formResponse class here.

Here we can access things like the date and time the response was submitted, the response, the response item, the respondent’s email if that was enabled in the form.

For us, we will grab the current respondent’s email first. Getting to the email is a little different than accessing other responses. If you remember when you set up your form, you can force users to add their email to the form in the settings menu.

The email here will always be at the top of the form. To access it, you need to use the getRespondentEmail() function. Line 4

Next, you can access any form item response in your form by calling the getItemResponses() method that will return an array of responses in your Google Form. You can then count from zero down your form to find the number of the item that you want to draw the response from and grab it with an array call, [n].

For us, we need to get the first item after the email, which is the name. This is the zeroeth item in the list. Line 5

Alternatively, if you have already grabbed the item’s ID, then you could use, getResponseForItem(item).

You would have probably have noticed the trim() method on the end of each email. This JavaScript method allows us to simply take any whitespace that the respondent accidentally added to their response or email – I’m guilty of accidental extra spaces too. 

Get the first name of the respondent

We want to personalise our email response and use the respondent’s name in the email. These days, it’s generally preferable to use a casual first name approach.

We don’t know if the respondent entered their first name or full name. We know with a certain degree of confidence that they will also not add in their title in this input as well. With this in mind, we can extract all the letters up to the first name and safely assume that this will be their first name (No one likes entering a first and last name input when they can do it in one line).

Here’s our code:

Here we create our firstName variable. The first thing we need to check is if the user added just a first name or a first and last name.  This is done with a JavaScript ternary or conditional operator that is basically a single line if statement.

In our condition inside the braces, we use the indexOf method on our name variable. This method takes one variable, the searchable value. In our case, this is an empty space, (" "). If a space is found it will return the index or numerical location of the space. If no space exists, it will return -1.

We check to see if there is no space, which will equal -1. If this is the case, then we just report the name variable, because the user only entered their first name on the form.

However, if there is a space, then we want to create a new string with all the characters up to the first space. We do this by using JavaScript’s substring method. This method takes a start and end index inside the string. For us, our start is the first value which is zero. To get the end index we can use indexOf() again on name to get the location of the space.

Use our signature block on the email

We want to look professional here and add our Gmail signature block to the end of our email like we normally do when we send an email. You would think this would be a pretty simple process, but actually, it is a little challenging.

To access the signature, we have to use the Gmail API Advanced Service we installed earlier.

Here’s the code:

Google Advanced APIs are written a little differently than what is shown in the doc’s when we use them in Google Apps Script. This can make using them a little confusing to get your head around. Let’s take a look at the Gmail API documentation for this method:

GET https://gmail.googleapis.com/gmail/v1/users/{userId}/settings/sendAs.list

In Google Apps Script, this boils down to:

Gmail.Users.Settings.SendAs.list({userId}).sendAs

We replace the {userId} hint with the special "me" input to reference your own account.

Running this on its own will give you a list of all of your emails and their data that are assigned to your account. kind of like this:

For me, I have a number of domain accounts along with my primary Gmail account assigned. As you can see in the sample data above, you can see a signature object in the first array. This object contains all the HTML we need to generate our signature block at the end of the email.

We can access this by using the JavaScript filter method. This method creates a new array based on parameters you intend to use to filter. For us, we want to filter down to just keep our default Gmail account. Line 7 

This will give our list of objects and we can then select the signature object (Line 7) which we store in our signature variable (Line 4).

Note, that as of writing this email, there is no way to access alternate emails.

We can then use this at the end of our email message.

Emailing the respondent

Finally, we can email the respondent with our custom message. We do this with the MailApp class and call the sendEmail() method. This method can take a number of possible variations of parameters, but I like to use the object parameter because it gives me more flexibility.

With this approach, you can modify a number of different objects. For our project we will use the following:

• to: This is the email of the respondent that we will send our message to.
• subject: Just like in a normal email, you can add your subject text here.
• htmlBody: You can add your HTML for your text here. You can always use one of the numerous email template builders to do this without hard coding HTML for most of your task. However, you will need to wrap your code in backticks and then at the very bottom add in the signature paragraph.

Here is a link to more emails on MailApp for you to get familiar with it:

• Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data
• Google Apps Script: Create an HTML email reminder service from Google Sheet data.
• Google Apps Scripts: Create Time Triggers to automatically send email task reminders from a Google Sheets checklist

Conclusion

In this tutorial, we looked at creating a custom email responder that is triggered when a Google Form is sent. We looked at how to add triggers to run our code when the form is submitted. Then we made sure all of our permissions and scopes were added so that our code would run. We also had to add the Gmail API advanced service to get our signature block.

So where to next?

You might want to run different custom email responses based on the respondent’s response. Here you could look at the user’s response and then perhaps use an if statement or switch to send the code to a specific set of htmlBody that relates to that particular response.

What do you think you would use this for? I would love to hear in the comments below.

If you’ve liked this tutorial and want to get regular updates on what I am working on next, you can subscribe down below this post.

Happy coding!

~Yagi