Automate emails with Google Forms and Google Apps Script

Подписаться 1,7 тыс.

Просмотров 15 тыс.

50% 1

This Google Apps Script tutorial video will teach you how to automate emails using onForm Submit with a Google Sheets workbook and a Google Form.
The begins with a short demo, which will show you what the end product will look like. We'll begin by setting up our files in the Google Apps Script environment. The getSheetById() will be created - you can learn more about that in the video linked below - followed by the composition of our main function.
After accessing the data from our workbook, we will connect the code file to the html file and then write some html. We'll conclude by setting our onForm Submit trigger followed by a concluding demo.
Sorry about the funny error at the end - after I finished recording I realized I entered my email "@google.com" instead of "@gmail.com", haha.
In case you missed my first video on email, you can watch it here: • Automate emails with G...
or, you can learn more about the getSheetById function: • Automate data collecti...

Опубликовано:

23 сен 2023

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 54

@jeffreyduncan4184 5 месяцев назад

Excellent video. I learned a ton in a short period of time and pulled off a major victory with this automation this week. Can't thank you enough!

@mattbrigidi 5 месяцев назад

thank you my friend! glad to be of help!

@jedwoolley 4 месяца назад

This video was amazing! I'm also so surprised this isn't built into google forms already :). I subscribed because this is the kind of JS project I need to be practicing

@mattbrigidi 4 месяца назад

Thanks Jed! I really appreciate your kind words. Let me know if you ever want to discuss projects

@odinakachukwuezeobika1307 6 месяцев назад

Thanks for this video. It was really helpful.

@mattbrigidi 6 месяцев назад

i’m so glad to hear that! thank you for letting me know

@davegaskell7680 6 месяцев назад

Great video. Exactly what I was looking for. Perfect pace of delivery too. What would make it even more helpful is if the code of the scripts could be copied into the video notes so that the scripts can just be cut-and-pasted as a starting point. Thanks for giving the tutorial.

@mattbrigidi 6 месяцев назад

thank you for the kind words and constructive feedback - i’m still exploring the best way to share code but i think it’s a good idea

@eBubble111 7 месяцев назад

This is really good! Thank you so much!

@mattbrigidi 7 месяцев назад

I'm glad to hear it! thank you for letting me know

@fabrice9848 2 месяца назад

you clearly deserve to get many more new subscribers

@mattbrigidi 2 месяца назад

thank you for the kind words!

@MCS.VEDIOS Месяц назад

Thanks for the Video

@mattbrigidi 26 дней назад

my pleasure!

@alicia3234 5 месяцев назад

thanks

@mattbrigidi 5 месяцев назад

You're welcome!

@fabrice9848 2 месяца назад

I cannot add the trigger with "on form submit" event. I'm getting this message: "Script authorization failed, please check your pop-up blocker". I've already run my script multiple times and allowed its execution. No errors when running my script manually. I've also tried after disabling my adblocker. Any suggestion?

@mattbrigidi 2 месяца назад

hmm sorry to hear you're experiencing this road block. I see you've added another comment. I will pick this up there

@raghhavms8126 2 месяца назад

Thanks for the video :) Do you have the script available for download?

@mattbrigidi 2 месяца назад

Not yet - I'm still trying to figure out the best way to share

@HolyCow2395 Месяц назад

Hi, Matt. How can I include the email of the responder of the form as recipient when sending the email? I assume I need to find the last row in the linked sheet but not sure how to do that in the code.

@mattbrigidi Месяц назад

sorry, i'm a bit confused - you want to send the email to the person who entered the form? or is it something else?

@HolyCow2395 Месяц назад

Sorry for the confusion. Yes, I also want the person that submitted the form to receive that email as well. Because your guide only allows specific email.

@mattbrigidi Месяц назад

i believe the video includes logic that collects the email of the person who submitted the form responses. i recorded this quite sometime ago, so apologies if that not the case. feel free to email me your workbook (edit access please) and i’ll look at your code and make specific suggestions. happy to help you get what you need

@alicia3234 5 месяцев назад

Hi Matt, Thanks for the great vid. I implemented the script and HTML you guided us through. Unfortunately, I get the following error message: TypeError: Cannot read properties of null (reading 'getRange') main @ Code.gs:9 I double checked the typing and it matches what you have. Any clues as to what the error means and how to resolve?

@mattbrigidi 4 месяца назад

my apologies - this comment was held for review and i was not aware of it. so sorry to respond so late. i'd be glad to help you get this up and running: my email is in my about section. if you send me a message i'd be happy to help you work through your code!

@schnellster1 5 месяцев назад

In the email ,if I want to provide an edit response link too ..how can I do that?

@mattbrigidi 5 месяцев назад

Someone else reached out to me about this! We worked on a solution. I was considering making a video about it. In the meantime, I can send you the solution over email if you'd like - it's in my about section

@notorioustofu3482 6 месяцев назад

Is there a need/way to sanitize user input before it gets sent to the spread sheet?

@mattbrigidi 6 месяцев назад

forms to spreadsheet from this example would make it difficult to clean the data during that part of the pipeline. if you are: 1. collecting responses via form 2. storing data in a sheet 3. emailing results then you probably want to clean between steps 2 and 3; rather than 1 and 2. it really depends on the specifics of you situation. i’d be happy to email about this if you wanna dive into it mattbrigidi.dev@gmail.com

@notorioustofu3482 5 месяцев назад

@@mattbrigidi thanks for the insights! What I ended up doing was creating a second sheet that pulls the data form the responses sheet with a VLOOKUP and applying a LAMBDA to massage the data. Then tweaked things a bit further before passing data to the htmlTemplate. Your videos were a big help, so thanks again

@fabrice9848 2 месяца назад

I've managed to add the trigger by selecting "on change" instead of "on form submit". But when I submit the form, I don't receive any email. I only receive an email when running the script manually. I've checked my Google sheet and it contains a new row, so this change should have triggered the script...

@mattbrigidi 2 месяца назад

unfortunately, you'll need to get the onForm submit trigger to work, as onChange will not provide the same functionality as onForm submit. This problem is challenging bc I have never experienced it before. feel free to email me and we can try to isolate your issue

@LimoreRicciardi Месяц назад

How would you handle it if the email address were a group? I think it may have to parse the group in order to send but I am a newbie and have no idea what I am even saying.

@mattbrigidi 26 дней назад

hey! sorry for the late response - i was on vacation. So you have a google group and you want to send a message to it? or you want to send a message to multiple addresses?

@alexroks7253 4 месяца назад

Thanks for the great video! I'm getting: Exception: Gmail operation not allowed. While I believe I have all permissions allowed. Any idea?

@mattbrigidi 4 месяца назад

Ah, I'm sorry to hear that you're experiencing an error. I'd be happy to look at your code for you. feel free to send it to me via email or to comment it here

@gabrielcambronero4338 7 месяцев назад

Hi!! First of all thank you so much for the video, i'm not a programmer btw. I did all, but it is giving me this issue: ReferenceError: "htmlTemplate" is not defined. main @ Code.gs:32. Any idea how can I fix it?

@mattbrigidi 6 месяцев назад

my apologies - this comment was held for review so i’m just seeing it now. the error could be the result of a few issues. if you send me your code in an email then i’d happily debug it with you

@alfonsobrandao1495 4 месяца назад

@@mattbrigidi Hi Matt, I'm having the same issue, when I comment the line and remove the "htmlBody" parameter in sendEmail(), the code runs without any error. Have you been able to fix this to the comment above? Maybe my fix is the same

@mattbrigidi 4 месяца назад

Alfonso and I worked this out over email - in the event that you are experiencing this problem, the solution is to ensure that the variables being used in your "html" file have been defined in your code file. an example: // create template objective for dynamically constructing html var htmlTemplate = HtmlService.createTemplateFromFile('email'); //define html variables for questions htmlTemplate.questionOne = formQuestions[0][1]; htmlTemplate.questionTwo = formQuestions[0][2]; if the variable is not defined - htmlTemplate.Variable_Name - then it will not be available for use in your html file.

@KiranSharma-pf4vm 4 месяца назад

What is the limitation of daily email sent in such way

@mattbrigidi 4 месяца назад

hello - I'm not sure this video is the best example of sending a daily email. Sounds like you might be better served watching this video on mailing lists: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-nGpOmOif20I.html the limitation is that you can only send to mailing lists that have 50 emails maximum. let me know if this doesn't answer your question and i'll do my best to clarify!

@niknakstoe 11 дней назад

Hi! This is a very helpful video. Can you help me figure out how to send a static image in Google drive to slack via appscript?

@mattbrigidi 7 дней назад

I'm glad you found the video helpful! I have a video series on Slack, which you can find on my channel page. However, I'm not aware of how to make a private image from Google Drive in Slack. I'm only aware of how to use an image using a URL, like so: app.slack.com/block-kit-builder/T06JXCU4MFH#%7B%22blocks%22:%5B%7B%22type%22:%22image%22,%22image_url%22:%22assets3.thrillist.com/v1/image/1682388/size/tl-horizontal_main.jpg%22,%22alt_text%22:%22delicious%20tacos%22%7D%5D%7D

@niknakstoe 7 дней назад

@@mattbrigidi yeah I went to a rabbit hole trying to do this. I think the only way to do this is to create a slackbot with file write and read privileges and use the files.upload API which has recently replace by a more complex way of doing it. I gave up after 2 days of trying to figure it out. Lol. Thanks for replying! I appreciate it

@mattbrigidi 6 дней назад

@@niknakstoe i’ll see what i can see bc this would be a cool thing to know how to do. my wife and i are expecting our first child in september so im a little tight on time! but if i find anything out i’ll be sure to update this thread

@niknakstoe 6 дней назад

@@mattbrigidi thanks man! And congratulations on your first child! 🥳

@user-jm4iy6iz4v 6 месяцев назад

This is a great tutorial, thank you. But I am having problems at the beginning of the project. I have a Google sheets workbook that our form feeds into, but each month we create a new worksheet. When I try to run the script, I get the following error code: Error TypeError: Cannot read properties of null (reading 'getSheets') getSheetById @ getSheetById.gs:7 main @ Code.gs:4 I have copied all of the scripting that you included, but I do have a different way to handle the emails being sent. I am using the form as a student sign in form, and I have it set up to select a teacher from a drop down list. When they click on the Submit form, I want it to trigger an email to be sent to the teacher indicating that the student has signed in at the office. I am hoping that if I can overcome this one error that the rest of the form will work. Any thoughts?

@mattbrigidi 4 месяца назад

@amctubagirl 4 дня назад

Thank you for this video. I also had the same error as some above. TypeError: Cannot read properties of null (reading 'getSheets') getSheetById @ getSheetById.gs.gs:7 main @ Code.gs:4 I copied your code so not sure what I am doing wrong. I tried to find your email in the about section but I must be blind. haha Did anyone else figure out this error?

@mattbrigidi 3 дня назад

hi - did you find my email? i received a question just like this today. i just want to make sure i’ve responded to you!

@Llethalinkk 6 месяцев назад

Thanks a lot! That's what I was looking for. Still I get an error when I launch it and don't know what the problem is : ReferenceError: sheets is not defined getSheetById @ getSheetById.gs:10 main@Code.gs:4 Do you have an idea?

@mattbrigidi 4 месяца назад