🎉Are you interested in a Bootstrapping Tools Community? Let me know! forms.gle/4tzvffHiRqS7pVZY6 🎉 I also released a new video that covers Drip Campaigns (aka email sequences) - check it out here: th-cam.com/video/Oej2jPut07Q/w-d-xo.html
yup, "namedValues" is still available in the Event Object. If you're getting an "undefined" error, that's likely b/c the Event Object wasn't available when you ran the code. When testing/debugging triggered based scripts, make sure to go through the same flow as the user so that the Event Object is passed. I actually just released a tutorial video on the topic of Event Triggers. It'll help you better understand all of the nuances that go into it so that you can leverage it in any automation you're trying to build: th-cam.com/video/fsU1qOhMnAI/w-d-xo.html
I am unable to get the named_values to work but the values did. The email is in position one. What would it look like instead of writing "What\'s your email?" in the code? Would it be $1 in its place?
The code is built to compare the question name, so instead of 'What\'s your email?' - you can replace it with the text you're using for the question. Just make sure to escape any single quotes if you're assigning the string with single quotes.
is it possible to send a google form for answering simple question yes or no with a gmail account. I trying from the google form directly but only allowed 20 to send. I am looking to send 500, I will be able to split up into two separate emails. This email list I have is from a separate exel list.
It sounds like you want to send batches of emails on a schedule (or when you manually tell it to) and not automatically as soon as someone submits the form, right? Cause otherwise, you'd only be sending one email at a time per form submission. I just released a video that does something similar to what you want - it's not exactly what you want, but it's really close. Basically, instead of comparing the date fields like I do in this video, you'll want to compare the YES/NO field instead. And then route the emails accordingly. th-cam.com/video/Oej2jPut07Q/w-d-xo.htmlsi=VTztgXJHCzqnIzHI
Hmm... I'm not 100% sure what you're exactly asking.... but it kinda sounds like you want to pre-populate an email address in your google sheet based on what userName is entered? That would only work if you already have a separate dataset that maps userName to Email. If you have that, then you can set up your onFormSubmit trigger function to take the user's entered userName and filter your dataset for the email address by using the Array.filter() method.
Excellent tutorial, thanks! One question though: is it possible to send an HTML formatted email as a response? Maybe by pulling the email body from an external file? If so, a sample code would be greatly appreciated.
Hi Arek! You mean like passing html code through the body of the email? I think that should be possible... I haven't tested this out yet (maybe I'll cover it in a future video!), but I would suggest creating a new html file in your apps script project and then using HtmlService.createHtmlOutputFromFile('file_name') to reference the html code. Then you can use .getContent() to extract the code to use as your email body. e.g. const recipient = 'youremail@example.com' let subject = 'email_subject' const emailTemplate = HtmlService.createHtmlOutputFromFile('file_name') let message = emailTemplate.getContent() GmailApp.sendEmail(recipient, subject, message)
Hi Austin, thanks for reaching out! In order to have the "From Spreadsheet" option, you would need to tie the Apps Script project to the spreadsheet. To do that, you will need to open up the script through the spreadsheet. In the menu bar go to "Extensions" and then select "Apps Script" . If you're already written some code in your other Apps Script project, just copy it over. If you would rather keep them separate, you could also import the other script project as a library into the new one by deploying the original script project as an API executable -- you'll need a Google Cloud Project to do that though. The first option of just copying over your scripts is probably going to be easier/faster. Let me know if you run into any issues with it.
Great video, I am not a programmer at all and I was able to follow what you did successfully. I was hoping that this video would help me send the information from the form to a recipient. Basically, a person in a network giving another person feedback using google forms, and that person getting notified about the things they said in the form. Is this something easy to include?
Looks like you're trying to run the script directly on the editor instead of triggering it from the sheet. When working with event triggers, you need to run it the same way a user would so that the event object is passed through. Here's an in-depth video on this subject: th-cam.com/video/fsU1qOhMnAI/w-d-xo.html
Dude. How are you not famous yet. You're saving lives here. Business lives. 😁😂 Seriously though, this will us a lot. Thank you! Just a quick question if I may, is there a way to include a unique code in the email response?
Thank you . Nice. May I asked , how if I want to attach google doc file to the email. I can upload the url of the file in the suitable column but still don't know how to attach the doc file in the email. Thank you for your kindness
Ah yes, this one is a bit tricky. What you essentially need to do is locate the Google Doc file using DriveApp and then include it as an "attachment" object in the options parameter of the sendEmail() function. The tricky part is making sure that you set the file to a certain type when you do that. Example: const file = DriveApp.getFileById(YOUR_GOOGLE_DOC_ID) GmailApp.sendEmail( RECIPIENT_EMAIL, EMAIL_SUBJECT, EMAIL_BODY, { attachments: [ file.getAs(MimeType.PDF) ], name: FILE_NAME } )
Hey Joe, thanks for the excellent video. I was wondering if it would be possible to obtain data beyond what is in the form submitted? For example I have additional columns to V-Lookup values based on the form submission and would like to include the V-lookup values into the body of the email. I found that the namedValues function only provides information submitted in the form. Was wondering if this is possible?
Yeah, you can totally do that. To get info beyond what was in the form, you would need to reference the row that the form entry was made into. Since you're already able to get the namedValues, that means you're already accessing the event trigger data successfully. Using that event object, get the range (e.range) and then getRow() --> e.range.getRow() Once you have the row, you'll need the sheet object (e.g. e.source.getActiveSheet() -- or something like that) and then get the range using the row --> sheet.getRange(e.range.getRow(), 'COLUMN_NUMBER_WITH_THE_DATA_YOU_WANT').getValue()) That's basically what you would do. Hope this helps!
Great tool. You mention being able to choose certain Form answers, or columns from the sheet, to include in the email. How would you do this? Or personalize the email with the respondant's name? Thank you.
Once the form is submitted, it creates a row in the google sheet which is referenced as the triggered event when you create the auto-response email. You can use that event object to get the answers to specific questions - so if you asked for the respondent's name, it would be a part of the event object you receive on the form submission. So for example, if you set your event object to "e" - e.g. onFormSubmission(e) - and someone submitted the form which triggers the script. e.range.getValues() should give you the information from the form submission. e.range.getValues() should give you an array that you can parse through.
Great video and very easy to follow. After following you everything is working except automated email shows my email id as a sender not the name. any pointer?
Hi Archna/Ashish! Thanks for reaching out, I'm glad you're enjoying the videos! There's a fourth parameter you can pass in the GmailApp.sendEmail() function which let's you set the sender's name. For example, it could look something like this: GmailApp.sendEmail('feedback@bootstrapping.tools', 'Demo Subject', 'The sender name is specified in the parameter to my right.', { name: 'My Custom Sender Name' }); Here's a link to the documentation on it: developers.google.com/apps-script/reference/gmail/gmail-app#sendemailrecipient,-subject,-body,-options Feel free to reach out if you have questions!
Joe thanks for the helpful reply, but also stuck on how to add these selected fields into the body of the email.. The structure is GmailApp.sendEmail(recipient,"Community Payment Notification","Lots of data") - where 'Lots of Data' is the body of the email. How do I add the form responses in here? You gave me help "let firstAnswer = formResponse[1] let thirdAnswer = formResponse[3] " which I get but how do I insert these in the body of the email? Help appreciated as always. Tony J
To insert those into the body of the email, it really depends on how you'd like to do that. If you're just looking to write up a paragraph and include the data, you can use tick symbols and throw in the variables using "${variable}" For example: If... firstAnswer was set to 'something' and thirdAnswer was set to 'somethingElse' then... saying let emailbody = `This is ${firstAnswer} and this is ${thirdAnswer}` would end up evaluating to: "This is something and this is somethingElse" I run through this scenario in Example 2 of my latest video: th-cam.com/video/fsU1qOhMnAI/w-d-xo.html You can also use HTML for the email, but that's a bit more involved.
Greetings! Is it possible to send to the customer the number of responce with this script? I got number of responces stored in Google Sheets. So, i just need to somehow drag the responce number from the last collumn and send it to the customer who submitted the form
Dear Joe, Hope you're doing well. Just to ask for your kind help to create a video where you could send email with PDF attachment using Form Submission, but also with certain condition to meet. Thanks alot Stan
Hi Alvin! To send a QR code in the email reply, you could display it as an image in the email's body using HTML, or you can send the image of the QR code as an attachment in the email. Either way works, but the HTML method is probably the most user friendly way since it'll be pretty apparent when they open up the email.
thanks bro, it worked really well & super easy, by the does it have any limit because in my google form there will be 2000 + participants and is there any way of customising the confirmation message with their name???
Hi, great content! It helped a lot. I have a question, how can I send the same e-mail to several recipients, considering I have multiple e-mail fields within the Google Form?
If you already know who all of the recipients should be you can just hardcode them into a list with a comma to separate each one. Then just pass that list through under the "cc" object as the options parameters for the sendEmail() method. If you're pulling the emails from the form itself, you'll just have to get the form response from the triggered event and parse it out for the values entered into whichever question captures those emails. After that, it'll be the same thing of making a comma-separated list to pass through the cc object. Here's an example of how to use the options parameter with a cc object: sendEmail('recipient@example.com', 'Some Awesome Subject', 'An even more awesome body of text', { cc: 'email2@example.com, email3@example.com, email4@example.com' })
Yes - very nice and nicely explained. Are you able to provide pointers as to how to select speccific form responses and add them in the body of the email? eg:- form has 15 fields completed but I would want to display just two of them within the body. Layout isnt very important - one field per line. Thanks if you can respond. Tony
When you get the form response back, it'll be in an Array. By using array positions, you can pull out just the data you want to include in something else. For example: if the form response came back as: let formResponse = ['09/03/2022', 'Answer to the first question', 'Answer to the second question', 'Answer to the third question'] and you just wanted the timestamp, the first answer, and the third answer, you would do this: let timestamp = formResponse[0] let firstAnswer = formResponse[1] let thirdAnswer = formResponse[3] Key thing to remember is that in an Array, the positioning begins from 0 - that's why the timestamp is set to formResponse[0] even though it's in the first position of the array. Once you have the variables set for the data you want, you can reference them specifically in other places.
Yeah, there's a few ways to do that - both require knowing the submission date which is added by default by Google Forms as the timestamp. - One way is to add in a column in the sheet that adds a year to the submission date (let's call it "reminder date") and then have the script check to see if today's date is equal to that reminder date. "new Date()" will give you today's date in javascript - Another way is to just do it all in code. So if you've already assigned the timestamp to a variable (let's just say "date") - you'll do something like this: let today = Utilities.formatDate(new Date(), 'EST', 'yyyy-MM-dd') let sendDate = Utilities.formatDate(new Date(new Date(date).getFullYear()+1, new Date(date).getMonth(), new Date(date).getDate()), 'EST', 'yyyy-MM-dd') if (sendDate == today) { //Do Stuff }
Hi Joe, I could really use your help in this matter. I have this spreadsheet linked to my google forms. When the speadsheet is populated with responses, it calls information from another sheet, populating new columns in the forms sheet. The email addresses are on those new columns (not filled by the forms). How can I sen an email to these addresses?
This is a great example but I have set up my form where the I am notified and a response is sent to the submitter of the form. I want to expand this a little bit and hoping you can guide me with it. I want to be able to notify two to four other people (in our small retiree association) notified that a form has been submitted and they may have additional actions. Thanks for the great service to the community.
Yeah, that's totally possible. It just depends on where your drafted email is stored. So if you have it stored in a Google Sheet where one column holds the subject, another holds the body, and another has a link to the attachment file URL, you can use SpreadsheetApp to grab the information from the Google Sheet and pass it through to the code that generates the email. It's a little trickier for the attachment, but you can leverage Google Drive to make it easier to reference and create the attachment file for.
@@BootstrappingTools What I am looking to do is Create a Draft email within Gmail. I can then add an attachment easily to that draft email. I Would then just initiate that draft email and fill in the mailtags.
You would need to take in the timestamp, add in the days until the next drip email, and then compare that calculated date to the current date -> if it's the same, then you send the email, else you skip. To adjust dates, you can do something like this (** I'm free writing this so you might need to tweak the date calculation methods a bit for it to work): let today = Utilities.formatDate(new Date(), 'EST', 'yyyy-MM-dd') let submissionCreatedDate = 'CODE_FOR_GETTING_THE_TIMESTAMP_FROM_THE_SHEET' let twoDaysLaterEmail = Utilities.formatDate(new Date(submissionCreatedDate.getFullYear(), submissionCreatedDate.getMonth(), submissionCreatedDate.getDate()+2), 'EST', 'yyyy-MM-dd') let fiveDaysLaterEmail = Utilities.formatDate(new Date(submissionCreatedDate.getFullYear(), submissionCreatedDate.getMonth(), submissionCreatedDate.getDate()+5), 'EST', 'yyyy-MM-dd') if (today == twoDaysLaterEmail) { //send drip email 1 } else if (today == fiveDaysLaterEmail) { // send drip email 2 } else { // don't do anything }
Just released a video on doing drip campaigns (email sequences) via Google Sheets. hope it helps! th-cam.com/video/Oej2jPut07Q/w-d-xo.htmlsi=wuelqjx0nCMWDUFn
Yup, you can add attachments to the email. The easiest way would be to store the attachment in google drive first, and then reference it using the DriveApp library. Once you have the file references, you can add in the options parameter to your sendEmail() call. For example: let file = = DriveApp.getFileById('your_google_drive_file_id') GmailApp.sendEmail(recipient, subject, body, { attachments: [file.getAs(MimeType.PDF) })
This is great. I have a question, is it possible to send a image or video file and it is stored in google drive? Or maybe the whole google drive folder and send it as "anyone with the link can view" folder permission?
Hi Eleven! Yeah, totally. You can send emails with attachments in them if you want. You can also add in the link if that's preferred. If the links are inputted through the form, you should be able to retrieve it using the DriveApp library. You would just need to know what the File ID or Folder ID is so that you can reference it. There's a good amount of documentation on this library: developers.google.com/apps-script/reference/drive/drive-app Lemme know if you run into any issues trying to reference the DriveApp
@@BootstrappingTools thank you, another question, is it possible to add a else if statement on the script? for example i have multiple choice question, then the responder answers "6th grade" then the mail sends a specific image file for that response.
@@eleven2636 yup totally. After the form submission triggers your auto email code - you'll have access to all of the responses to each field. You'll just have to add in the IF/ELSE statement somewhere before you generate the email content/body. If you have a bunch of different values you want to curate an image for, I would suggest using a SWITCH statement instead.
Once you have the form entries set as variables, you'll be able to put them into whatever format you want in the email. Hmm... maybe I should make a separate video for creating a nicely designed email template...
@@BootstrappingTools do you have how to response with formal email format? thank you so much about this video make my day but now my problem is i need to send it in formal letter
Great information and it is amazing how "easy" you made for us. I am starting in learning and somehow Google is not allowing me to call the API for GmalApp and send me an error. Do you think I need to do any kind of approval in administration for my workspace? Usually in the past, when running the script first time it ask me for permit and that's all..
🎉Are you interested in a Bootstrapping Tools Community? Let me know! forms.gle/4tzvffHiRqS7pVZY6 🎉
I also released a new video that covers Drip Campaigns (aka email sequences) - check it out here: th-cam.com/video/Oej2jPut07Q/w-d-xo.html
Does namedValues still work? "TypeError: Cannot read property 'named_values' of undefined" is what I get everytime.
yup, "namedValues" is still available in the Event Object. If you're getting an "undefined" error, that's likely b/c the Event Object wasn't available when you ran the code. When testing/debugging triggered based scripts, make sure to go through the same flow as the user so that the Event Object is passed.
I actually just released a tutorial video on the topic of Event Triggers. It'll help you better understand all of the nuances that go into it so that you can leverage it in any automation you're trying to build: th-cam.com/video/fsU1qOhMnAI/w-d-xo.html
I am unable to get the named_values to work but the values did. The email is in position one. What would it look like instead of writing "What\'s your email?" in the code? Would it be $1 in its place?
The code is built to compare the question name, so instead of 'What\'s your email?' - you can replace it with the text you're using for the question. Just make sure to escape any single quotes if you're assigning the string with single quotes.
is it possible to send a google form for answering simple question yes or no with a gmail account. I trying from the google form directly but only allowed 20 to send. I am looking to send 500, I will be able to split up into two separate emails. This email list I have is from a separate exel list.
It sounds like you want to send batches of emails on a schedule (or when you manually tell it to) and not automatically as soon as someone submits the form, right? Cause otherwise, you'd only be sending one email at a time per form submission.
I just released a video that does something similar to what you want - it's not exactly what you want, but it's really close. Basically, instead of comparing the date fields like I do in this video, you'll want to compare the YES/NO field instead. And then route the emails accordingly. th-cam.com/video/Oej2jPut07Q/w-d-xo.htmlsi=VTztgXJHCzqnIzHI
I want that when we select the userName, the email should automatically appear in the Email field.
Hmm... I'm not 100% sure what you're exactly asking.... but it kinda sounds like you want to pre-populate an email address in your google sheet based on what userName is entered? That would only work if you already have a separate dataset that maps userName to Email. If you have that, then you can set up your onFormSubmit trigger function to take the user's entered userName and filter your dataset for the email address by using the Array.filter() method.
It worked! You saved me from a bunch of crappy subscription services :)
Glad I could help!
Excellent tutorial, thanks! One question though: is it possible to send an HTML formatted email as a response? Maybe by pulling the email body from an external file? If so, a sample code would be greatly appreciated.
Hi Arek!
You mean like passing html code through the body of the email? I think that should be possible... I haven't tested this out yet (maybe I'll cover it in a future video!), but I would suggest creating a new html file in your apps script project and then using HtmlService.createHtmlOutputFromFile('file_name') to reference the html code. Then you can use .getContent() to extract the code to use as your email body.
e.g.
const recipient = 'youremail@example.com'
let subject = 'email_subject'
const emailTemplate = HtmlService.createHtmlOutputFromFile('file_name')
let message = emailTemplate.getContent()
GmailApp.sendEmail(recipient, subject, message)
I'm seriously waiting for this Video, a video that builds on this one to explain how to send HTML mails.
I want that when we select the userName, the email should automatically appear in the Email field.
7:00 the Select event source dropdown shows 'From Spreadsheet' Did you do anything to enable that as an option, I don't see it as an option...
Hi Austin, thanks for reaching out!
In order to have the "From Spreadsheet" option, you would need to tie the Apps Script project to the spreadsheet. To do that, you will need to open up the script through the spreadsheet. In the menu bar go to "Extensions" and then select "Apps Script" .
If you're already written some code in your other Apps Script project, just copy it over. If you would rather keep them separate, you could also import the other script project as a library into the new one by deploying the original script project as an API executable -- you'll need a Google Cloud Project to do that though. The first option of just copying over your scripts is probably going to be easier/faster.
Let me know if you run into any issues with it.
Great video, I am not a programmer at all and I was able to follow what you did successfully. I was hoping that this video would help me send the information from the form to a recipient. Basically, a person in a network giving another person feedback using google forms, and that person getting notified about the things they said in the form. Is this something easy to include?
what we have a paragraph in body instead of just one line
The script does not work. Error message: (1) e: undefined (2)named_values: undefined (3) values: undefined
Looks like you're trying to run the script directly on the editor instead of triggering it from the sheet. When working with event triggers, you need to run it the same way a user would so that the event object is passed through.
Here's an in-depth video on this subject: th-cam.com/video/fsU1qOhMnAI/w-d-xo.html
Dude. How are you not famous yet. You're saving lives here. Business lives. 😁😂
Seriously though, this will us a lot. Thank you!
Just a quick question if I may, is there a way to include a unique code in the email response?
Ha, I appreciate that!
Great video. It has helped me with the project I'm working on.
Excellent!
Thank you . Nice. May I asked , how if I want to attach google doc file to the email. I can upload the url of the file in the suitable column but still don't know how to attach the doc file in the email. Thank you for your kindness
Ah yes, this one is a bit tricky. What you essentially need to do is locate the Google Doc file using DriveApp and then include it as an "attachment" object in the options parameter of the sendEmail() function. The tricky part is making sure that you set the file to a certain type when you do that.
Example:
const file = DriveApp.getFileById(YOUR_GOOGLE_DOC_ID)
GmailApp.sendEmail(
RECIPIENT_EMAIL,
EMAIL_SUBJECT,
EMAIL_BODY,
{
attachments: [ file.getAs(MimeType.PDF) ],
name: FILE_NAME
}
)
Hey Joe, thanks for the excellent video. I was wondering if it would be possible to obtain data beyond what is in the form submitted? For example I have additional columns to V-Lookup values based on the form submission and would like to include the V-lookup values into the body of the email. I found that the namedValues function only provides information submitted in the form. Was wondering if this is possible?
Yeah, you can totally do that. To get info beyond what was in the form, you would need to reference the row that the form entry was made into. Since you're already able to get the namedValues, that means you're already accessing the event trigger data successfully. Using that event object, get the range (e.range) and then getRow() --> e.range.getRow()
Once you have the row, you'll need the sheet object (e.g. e.source.getActiveSheet() -- or something like that) and then get the range using the row --> sheet.getRange(e.range.getRow(), 'COLUMN_NUMBER_WITH_THE_DATA_YOU_WANT').getValue())
That's basically what you would do. Hope this helps!
Great tool. You mention being able to choose certain Form answers, or columns from the sheet, to include in the email. How would you do this? Or personalize the email with the respondant's name?
Thank you.
Once the form is submitted, it creates a row in the google sheet which is referenced as the triggered event when you create the auto-response email.
You can use that event object to get the answers to specific questions - so if you asked for the respondent's name, it would be a part of the event object you receive on the form submission.
So for example, if you set your event object to "e" - e.g. onFormSubmission(e) - and someone submitted the form which triggers the script. e.range.getValues() should give you the information from the form submission. e.range.getValues() should give you an array that you can parse through.
Great video and very easy to follow. After following you everything is working except automated email shows my email id as a sender not the name. any pointer?
Hi Archna/Ashish! Thanks for reaching out, I'm glad you're enjoying the videos!
There's a fourth parameter you can pass in the GmailApp.sendEmail() function which let's you set the sender's name. For example, it could look something like this:
GmailApp.sendEmail('feedback@bootstrapping.tools', 'Demo Subject', 'The sender name is specified in the parameter to my right.', {
name: 'My Custom Sender Name'
});
Here's a link to the documentation on it: developers.google.com/apps-script/reference/gmail/gmail-app#sendemailrecipient,-subject,-body,-options
Feel free to reach out if you have questions!
Joe thanks for the helpful reply, but also stuck on how to add these selected fields into the body of the email..
The structure is
GmailApp.sendEmail(recipient,"Community Payment Notification","Lots of data") - where 'Lots of Data' is the body of the email.
How do I add the form responses in here? You gave me help "let firstAnswer = formResponse[1] let thirdAnswer = formResponse[3] " which I get but how do I insert these in the body of the email?
Help appreciated as always.
Tony J
To insert those into the body of the email, it really depends on how you'd like to do that. If you're just looking to write up a paragraph and include the data, you can use tick symbols and throw in the variables using "${variable}"
For example:
If... firstAnswer was set to 'something' and thirdAnswer was set to 'somethingElse'
then... saying let emailbody = `This is ${firstAnswer} and this is ${thirdAnswer}`
would end up evaluating to:
"This is something and this is somethingElse"
I run through this scenario in Example 2 of my latest video: th-cam.com/video/fsU1qOhMnAI/w-d-xo.html
You can also use HTML for the email, but that's a bit more involved.
@@BootstrappingTools Joe,
Many thanks, I got there!
All works fine.
I really appreciate the help.
Many thanks Tony J
Greetings! Is it possible to send to the customer the number of responce with this script? I got number of responces stored in Google Sheets. So, i just need to somehow drag the responce number from the last collumn and send it to the customer who submitted the form
Dear Joe,
Hope you're doing well.
Just to ask for your kind help to create a video where you could send email with PDF attachment using Form Submission, but also with certain condition to meet.
Thanks alot
Stan
Hi Stan! (or is it Rustan? - your message and youtube user name confuse meeeeee)
I love this suggestion. Gonna add it to my handy dandy queue list :)
@@BootstrappingTools thanks alot, Joe! Sure to look for your new video tutorials!
Great Content !!! Is there any chance to send each qr code(google sheets) through email reply?
Hi Alvin!
To send a QR code in the email reply, you could display it as an image in the email's body using HTML, or you can send the image of the QR code as an attachment in the email. Either way works, but the HTML method is probably the most user friendly way since it'll be pretty apparent when they open up the email.
i have this error : Error Exception: Gmail operation not allowed. : Mail service not enabled
at sendEmails(Code:24:12)
thanks bro, it worked really well & super easy, by the does it have any limit because in my google form there will be 2000 + participants and is there any way of customising the confirmation message with their name???
Hi, great content! It helped a lot. I have a question, how can I send the same e-mail to several recipients, considering I have multiple e-mail fields within the Google Form?
If you already know who all of the recipients should be you can just hardcode them into a list with a comma to separate each one. Then just pass that list through under the "cc" object as the options parameters for the sendEmail() method.
If you're pulling the emails from the form itself, you'll just have to get the form response from the triggered event and parse it out for the values entered into whichever question captures those emails. After that, it'll be the same thing of making a comma-separated list to pass through the cc object.
Here's an example of how to use the options parameter with a cc object:
sendEmail('recipient@example.com', 'Some Awesome Subject', 'An even more awesome body of text', { cc: 'email2@example.com, email3@example.com, email4@example.com' })
Yes - very nice and nicely explained. Are you able to provide pointers as to how to select speccific form responses and add them in the body of the email? eg:- form has 15 fields completed but I would want to display just two of them within the body. Layout isnt very important - one field per line.
Thanks if you can respond.
Tony
When you get the form response back, it'll be in an Array. By using array positions, you can pull out just the data you want to include in something else.
For example:
if the form response came back as:
let formResponse = ['09/03/2022', 'Answer to the first question', 'Answer to the second question', 'Answer to the third question']
and you just wanted the timestamp, the first answer, and the third answer, you would do this:
let timestamp = formResponse[0]
let firstAnswer = formResponse[1]
let thirdAnswer = formResponse[3]
Key thing to remember is that in an Array, the positioning begins from 0 - that's why the timestamp is set to formResponse[0] even though it's in the first position of the array.
Once you have the variables set for the data you want, you can reference them specifically in other places.
Can you put a time delay on this? Say, a year post submission?
Yeah, there's a few ways to do that - both require knowing the submission date which is added by default by Google Forms as the timestamp.
- One way is to add in a column in the sheet that adds a year to the submission date (let's call it "reminder date") and then have the script check to see if today's date is equal to that reminder date. "new Date()" will give you today's date in javascript
- Another way is to just do it all in code. So if you've already assigned the timestamp to a variable (let's just say "date") - you'll do something like this:
let today = Utilities.formatDate(new Date(), 'EST', 'yyyy-MM-dd')
let sendDate = Utilities.formatDate(new Date(new Date(date).getFullYear()+1, new Date(date).getMonth(), new Date(date).getDate()), 'EST', 'yyyy-MM-dd')
if (sendDate == today) {
//Do Stuff
}
Hi Joe, I could really use your help in this matter. I have this spreadsheet linked to my google forms. When the speadsheet is populated with responses, it calls information from another sheet, populating new columns in the forms sheet. The email addresses are on those new columns (not filled by the forms). How can I sen an email to these addresses?
This is a great example but I have set up my form where the I am notified and a response is sent to the submitter of the form. I want to expand this a little bit and hoping you can guide me with it. I want to be able to notify two to four other people (in our small retiree association) notified that a form has been submitted and they may have additional actions. Thanks for the great service to the community.
Hi thanks for the tutorial. Really helpful, may I know the response Mails are going to spam what could be the reason.
Can we send an formated Draft email through this system as well? Instead of putting the subject and body in the code?
Do you mean like pull the subject and body from somewhere else?
@@BootstrappingTools Yes. So that we can create a draft email with attachment already there and send the email.
Yeah, that's totally possible. It just depends on where your drafted email is stored.
So if you have it stored in a Google Sheet where one column holds the subject, another holds the body, and another has a link to the attachment file URL, you can use SpreadsheetApp to grab the information from the Google Sheet and pass it through to the code that generates the email. It's a little trickier for the attachment, but you can leverage Google Drive to make it easier to reference and create the attachment file for.
@@BootstrappingTools how about pulling from a draft email that stays in your Gmail draft folder.
@@BootstrappingTools What I am looking to do is Create a Draft email within Gmail. I can then add an attachment easily to that draft email. I Would then just initiate that draft email and fill in the mailtags.
Thanks for sharing this! How could we start an email sequence that uses Gmail to drip an email a day to the user once submitting the Google form?
You would need to take in the timestamp, add in the days until the next drip email, and then compare that calculated date to the current date -> if it's the same, then you send the email, else you skip.
To adjust dates, you can do something like this (** I'm free writing this so you might need to tweak the date calculation methods a bit for it to work):
let today = Utilities.formatDate(new Date(), 'EST', 'yyyy-MM-dd')
let submissionCreatedDate = 'CODE_FOR_GETTING_THE_TIMESTAMP_FROM_THE_SHEET'
let twoDaysLaterEmail = Utilities.formatDate(new Date(submissionCreatedDate.getFullYear(), submissionCreatedDate.getMonth(), submissionCreatedDate.getDate()+2), 'EST', 'yyyy-MM-dd')
let fiveDaysLaterEmail = Utilities.formatDate(new Date(submissionCreatedDate.getFullYear(), submissionCreatedDate.getMonth(), submissionCreatedDate.getDate()+5), 'EST', 'yyyy-MM-dd')
if (today == twoDaysLaterEmail) {
//send drip email 1
} else if (today == fiveDaysLaterEmail) {
// send drip email 2
} else {
// don't do anything
}
Just released a video on doing drip campaigns (email sequences) via Google Sheets. hope it helps! th-cam.com/video/Oej2jPut07Q/w-d-xo.htmlsi=wuelqjx0nCMWDUFn
@@BootstrappingTools awesome! I’ll check it out, thank you.
how can I add attachment to an auto response to a form?
Yup, you can add attachments to the email.
The easiest way would be to store the attachment in google drive first, and then reference it using the DriveApp library. Once you have the file references, you can add in the options parameter to your sendEmail() call.
For example:
let file = = DriveApp.getFileById('your_google_drive_file_id')
GmailApp.sendEmail(recipient, subject, body, { attachments: [file.getAs(MimeType.PDF) })
hello bro can i get their email without them requirin g to check the box, or inputing their email
This is great. I have a question, is it possible to send a image or video file and it is stored in google drive? Or maybe the whole google drive folder and send it as "anyone with the link can view" folder permission?
Hi Eleven!
Yeah, totally. You can send emails with attachments in them if you want. You can also add in the link if that's preferred.
If the links are inputted through the form, you should be able to retrieve it using the DriveApp library. You would just need to know what the File ID or Folder ID is so that you can reference it. There's a good amount of documentation on this library: developers.google.com/apps-script/reference/drive/drive-app
Lemme know if you run into any issues trying to reference the DriveApp
@@BootstrappingTools thank you, another question, is it possible to add a else if statement on the script? for example i have multiple choice question, then the responder answers "6th grade" then the mail sends a specific image file for that response.
@@eleven2636 yup totally.
After the form submission triggers your auto email code - you'll have access to all of the responses to each field. You'll just have to add in the IF/ELSE statement somewhere before you generate the email content/body. If you have a bunch of different values you want to curate an image for, I would suggest using a SWITCH statement instead.
Will you be able to sent email with a different email? since there are multiple people we want one email to use to send the email trigger
Is it a shared email that you want to use?
@@BootstrappingTools yes, wanted to use a shared email
@@clarklaurentdumon8404 If you have that shared email set up as an alias, you can send as that alias email using the GmailApp.getAliases() method
This is awesome... Thank you. This is a great head start for me ... Now to try to figure out how to put the responses into a format in the email.
Once you have the form entries set as variables, you'll be able to put them into whatever format you want in the email. Hmm... maybe I should make a separate video for creating a nicely designed email template...
@@BootstrappingTools do you have how to response with formal email format? thank you so much about this video make my day but now my problem is i need to send it in formal letter
great!
Great information and it is amazing how "easy" you made for us. I am starting in learning and somehow Google is not allowing me to call the API for GmalApp and send me an error. Do you think I need to do any kind of approval in administration for my workspace? Usually in the past, when running the script first time it ask me for permit and that's all..
Excelent!!!