Creating an Automated Email when a Google Form is Filled Out

jthurston
Participant V

Hello,

I have an existing Google Form that students use to submit their contact information, and to let us know what kinds of classes they are interested in. I want an email to be automatically sent to students after they fill out the form.

When the form is submitted, automatically populates a Google Sheets file, which is the data source for my App Sheet table. I set up a workflow that should send an email when a new row is added to the table. However, the emails arenโ€™t being sent.

I checked the audit log, and it does not show any โ€œAdd Rowโ€ operations. I found this article, and it seems like workflows are only triggered when records are added through App Sheet.

Is there a way to make it so the workflow is triggered when a Google Form is filled out? Or to create a form that the students can use without having access to stored data or other parts of the app?

Thank you,

1 8 7,252
8 REPLIES 8

Yes it would be possible to send out the email when the form was submitted, but you would need to use some coding inside the google spreadsheet when a form submission is received.

Select โ€˜โ€™ after clicking on tools
Opens a new window like this:


Enter a custom function to handle the email response using custom Google Apps Script (GAS) code.
Then go to:
3X_f_1_f1d5d9f4a410060d9e80659d5298d93d78f6f525.png
Edit>Current projectโ€™s triggers
This will open yet another new page:

And then configure the trigger for โ€˜On form submitโ€™

I suggest doing a standard google search โ€˜how to send an email from a google form responseโ€™ to figure out what your custom function will need to look like. This will involve code and using either MailApp (predefined code that should be readily available with autocomplete inside the script environment) or enabling the Gmail API within the script project.

Austin
Participant V

A quick clarification point: no you couldnโ€™t do this within appsheet, yes you can do it outside of appsheet

It also seems that there is now an Add-On available to set this up more easily. I apologize for any potential confusion, when I set all of this up myself previously, none of these built in options were available.

https://workspace.google.com/marketplace/app/email_notifications_for_google_forms/984866591130

LeventK
Participant V

@Justin_Thurston
As @Markus_Malessa had already explained, you can do that with constructing a Google Apps Script code and then assign either an onChange or onFormSubmit trigger. Provided you can explain what data or what kinda email you want to send to the students, I can help with the GAS.

jthurston
Participant V

Thanks to everyone for the detailed and helpful information! It is much appreciated.

@LeventK
Right now, I want to generate a pretty generic email. Something like:

"Hello [Name],

Thanks for registering for our classes! Your advisor will contact you soon with more information"

Thank you,

@Justin_Thurston
You can use below script code in your Form Responses gSheet.

  • Open the gSheet
  • Tool > Script Editor and delete sample function code in code.gs namespace

Google Apps Script


const emailColIndex = 1; // A = 1, B = 2, ...., Z = 26 etc.
const userNameColIndex = 1; // A = 1, B = 2, ...., Z = 26 etc.
const subject = "Your email subject line goes here";
const body = "Thanks for registering for our classes! Your advisor will contact you soon with more information"+<p>+"Thank you";

function emailUser(e) {
	var sSht = e.source;
	var sht = sSht.getActiveSheet;
	var shtName = sht.getName();
	
	if (shtName === "Form Responses") { //Change the sheet name as it fits
		var rng = sht.getActiveRange()
		var row = parseInt(rng.getRow());
		
		var to = sht.getRange(row, emailColIndex).getValue().toString();
		var user = sht.getRange(row, userNameColIndex).getValue().toString();
		body = "Hello "+user+",<p>"+body;
		
		MailApp.sendEmail(to, subject, body, {htmlBody=body});
	}
}

Check @Markus_Malessaโ€™s post on how to trigger this code with onChange. Enjoy.

Thank you!

Youโ€™re welcome.

Top Labels in this Space