Google Forms is a great tool to use to make quick and easy sign-up forms, but sometimes you need it to do something else a bit more complex. In this short tutorial we’ll talk about how to extend a basic Google Form script that sends confirmation email to also send attachments. If you don’t know how to send the Google Form confirmation email, check out the first tutorial on that topic here:
Part 1: Sending a Confirmation Email with Google Forms>>>>>
Step 1: Get a Google Doc as PDF
The first thing we are going to add to the script will retrieve a Google Doc from your Google Drive App. Create a new variable that accesses the Drive App and gets a particular file by its id.
var file = DriveApp.getFileById("longidstringgoeshere");
You can find the id string of your document by looking in its URL in another tab
Now we have created a variable that opens your Drive App and gets a particular file by Id. Next we’re going to tell it specifically what file type we want. We will call some specific methods on the file variable to make that happen. Create a new variable to store our final Drive Doc as a PDF:
var pdfFile = file.getAs(MimeType.PDF);
Ok, this line of code just specifies the document as a PDF for our attachment. In Step 2, we’ll look at how to use this file as an attachment in our mail function.
Step 2: Add Attachment and Send Email from Google Forms
Now that we have our file built using the Drive App, we can add this attachment variable to our mail function so that an attachment is sent when someone submits something in Google Forms. To do this, we add another parameter to our initial mail function like the code example below:
MailApp.sendEmail(email, subject, body, {attachments:[liabilityWaiver]});
We are adding the attachment through a JavaScript Object (JSON) that we are passing as an additional parameter. A JSON object is created with curly braces and a number of key:value pairs seperated by commas.
{
attachments:[pdfFile]
}
In this example we use the key of “attachments” which accepts an array as a value. It accepts an array because we could also include multiple files here formatted as a JavaScript array.
{
attachments:[pdfFile, anotherFile, anotherFileStill]
}
After that, you should be ready to save out your code and send an email attachment. Just make sure to check your syntax when ending the mail function line if you get any initial errors.
Here is the whole script:
function myFunction(e) {
var name = e.values[1];
var phoneNumber = e.values[2];
var email = e.values[3];
var subject = "Thanks for submitting";
var body = "We look forward to racing with you. Don't forget to fill out and bring the attached waiver on race day.";
var waiver = DriveApp.getFileById("yourFileIdHere");
var liabilityWaiver = waiver.getAs(MimeType.PDF);
MailApp.sendEmail(email, subject, body, {attachments:[liabilityWaiver]});
}
Be sure to review Part 1:
Would it be possible to create a form and have it automatically send an email confirmation with a PDF but the confirmation is only sent if the a certain field (email) in on another Google sheet and the PDF that is sent is dependent on the date of submission.
Example.
I submit the form on 3/17/15 and I receive my confirmation email because my email is part of the membership email list. I receive the PDF that is for 3/18.
Thanks
Sorry for the late reply, but yes to all things. You are looking for an if statement, which checks a condition, like a date, and then executes some code in this case the mail.app function.
if (some condition == true) {
MailApp.sendEmail();
}
Post some code below if you have it so I can take a look.
Regards,
JE
Hi! am going crazy around here, i have the script, its working and everything BUT, what i want is to send the response from “me@mydomain.com” and i just find people saying “yes, if you have the right script you can” but no one post the an example .
Can you help? i have try a lot of thing and i´m not so good whit this script things
I think a few people have used Gmail Aliases to do it: https://developers.google.com/apps-script/reference/gmail/gmail-app#getAliases()
Hi, thanks for this helpful article. But I still have a question.
The pdf is only attached to the e-mail if the user (who fills out form) doesn’t tick the option “Send me a copy of responses”. How can I send the pdf even if the user ticks that box?
Thanks so much!
Please ignore it, I figured out my mistake.
Everything works as designed 🙂
Glad you figured this out!
how do i add new lines in the message body, as at the minute its just a big block of text.
ta in advance
You need to use the new line character “n”: http://stackoverflow.com/questions/5758161/how-do-i-create-a-new-line-in-javascript
Thanks very much, that’s all working now great.
One other question…. My form now sends its email with attachments to the form filler, but it would be great if it could also send me an email telling me a new entry has been made?
At the minute I have to keep checking to see if a new entry has been made or not.
Sounds simple… But I can’t seem to work out how to do it
You can just run the same MailApp method twice, this time to yourself with the subject “Form submitted.”
errrrrrr……
ok.. how would that look.
sorry, i have tryd to just duplicate it and just amend the mail address but i have ended up buggering it all up.
am i supposed to create a whole new seperate script and get the form to run both… or do i add it into the same one?
any chance you could spell it out for me.. sorry and very much appreciated
Sure thing Matt, please just post the script you have now below and I’ll show you what I mean.
i think i might have worked it out… i think its working. I have to create new email Var’s ect dont i?
but if you can check then i am grateful
function myFunction(e){
var userName = e.values[1];
var userEmail = e.values[11];
var date = e.values[0];
var subject = “Skirfare Bridge Barn booking form succesfully submitted”;
var message = “Thank you, ” + userName + ” for choosing Skirfare Bridge Barn for your next Yorkshire Adventure. n Please ensure you secure your booking by paying your deposit by bank transfer within 7 days. n All the details are enclosed in the attached copy of the terms and conditions of hire. n n n Best Regards n Matt & Bev n Skirfare Bridge Barn n tel – 07597 645254 n skirfarebridgebarn.co.uk ” ;
var file = DriveApp.getFileById(“1TRXgLvUmK0Jwbe_J84YxS1zF0zMPnJAtp7tUjdQE_qc”);
var pdfFile = file.getAs(MimeType.PDF);
MailApp.sendEmail (userEmail, subject, message, {attachments:[pdfFile]});
var userEmail2 = “skirfarebridgebarn@gmail.com”;
var subject2 = “Skirfare Booking Form Submitted”;
var message2 = + userName + “has just made a booking”;
MailApp.sendEmail (userEmail2, subject2, message2);
}
Yup, this should work! Glad you got everything going. Let me know if you have any problems down the road.
JE
still one thing is driving me mad.
when i get my email informing me the form has been filled, i get this response “NaNhas just made a booking”
i dont get the persons username as they filled it in on the form, which in my script looks right to me that it should.
this last bit is driving me mad.. any help would be great
NaN means not a number in JS. My bet is that the script is pulling in values from a different index. That can happen sometimes if you shift around inputs on a form. Try making a copy and testing that to see if it makes a difference.
Jeff I am getting the same NAN. I’m not sure what you mean by copy it and run it again.
Not sure if this is the same of a different question than the one we talked about on Google+. Let me know if you still need help.
When I get my confirmation email I get this:
NaNOlsonhas been added to the group.
Here is the code I’m using. Thank you!!!
var subject2 = + firstName +athletelastName + ” has been added to the group”;
NaN means not a number. Since you have that plus sign at the beginning of the line of code it is looking for a number in the firstName variable and it finds a string. Try this:
var subject2 = firstName + ” ” + athletelastName + ” has been added to the group.”;
That got it!!! Thanks Jeff!!
Great info! Is it possible to send a graphic intense PDF that has been uploaded to Google Drive as a PDF (as opposed to converting a google doc to PDF as shown in the video)? I’ve tried doing it, but not working, here is my code:
function myFunction(e) {
var userEmail = e.values[8];
var subject = “inGauge by TCA Online Demo”;
var message = “Thanks for completing the survey. PLease see attached”
var waiver = DriveApp.getFileById(“0B8Dj-4dYh0ZReUtiNnQ1TmM2bGc”);
var whitePaper = waiver.getAs(MimeType.PDF);
MailApp.sendEmail(userEmail, subject, message, {attachments: [whitePaper]});
}
Hmm…I don’t see why not. There is nothing in the docs that would suggest it wouldn’t work. What type of error message are you getting? Or is the email sending without the attachment?
There is a type in the line with var message; you’re missing a semi-colon. You also might not need to convert the file using getAs since it is already a PDF. Let me know more info about what is happening when you run the script.
JE
Hello,
I have tried your approach line by line and word for word but i keek getting this error message
TypeError: Cannot read property “values” from undefined. (line 2, file “”)
Help Please
Regards,
Charles
Hey Charles,
Sorry for the delayed reply, but you get that error when running the script in the script editor since there are no values from the event (form submission). Have you tried submitting a test form? If so, and you still get this error, can you post the script here so that I can take a look?
JE
Hi Thanks for this, I ve set up the script as you described and it works perfectly, I do have one small query though that I hope you can help with. I wanted to send a new form but having the responses for the 2nd form going to a new sheet on the original google doc. I unlinked the first form and went to create a new form from scratch.
When you create the form, or any time thereafter, you can change the response destination. If you click on responses in the form menu there should be an option to change the response destination.
How do I do a carriage return to create a new line in the plain text email using the script above as a template?
Thank you.
n is the new line character in JS when put inside a string.
thankyou for this info,
btw how do I make a PDF option in google form, so when the audience choose 1 from the option they will get the PDF according to their chosen?
thankyou and please replay
Oh, good question. If you created a dropdown option that contained say “PDF” and “DOCX” for example, you could check this value and create the blob differently based on the answers.
Jeff thanks so much for the tutorial. I’ve run in to snag on this. Here is my code:
function confirmationEmail(e) {
var athletelastName = e.values[1];
var athletefirstName = e.values[2];
var primaryEmail = e.values[9];
var waiver = DriveApp.getFilesByName(“TD Waivers PDF”);
var subject = “Welcome to the Group!!!”;
var body = “Thanks for registering ” + athletefirstName + ” for the Group. Please print the attached waiver. Sign, scan, and email it back to us if you have not done so already. Thanks!!!”;
MailApp.sendEmail(primaryEmail, subject, body, {attachments:[waiver]});
I set the trigger to run on form submit. When I run a test form submission with my email in it I am not getting the email. Not sure what I’m doing wrong. Thanks!!!
}
How to verify an email address that it’s real or not using google forms???
You can turn on data validation for certain inputs. Check out the docs here: https://support.google.com/docs/answer/3378864?hl=en
Can you help me with this code please.
I am making a test online. and I want the testers get their result on their emails.
here is the script
// This constant is written in column D for rows for which an email
// has been sent successfully.
var EMAIL_SENT = “EMAIL_SENT”;
function sendEmails2() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = 5; // Number of rows to process
// Fetch the range of cells A2:C20
var dataRange = sheet.getRange(startRow, 1, numRows, 5)
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (var i = 0; i -1)
sh.getRange(‘U2:AJ2’).copyTo(sh.getRange(‘U3:AJ20’));
})
} }
}
sorry but I think I forgot the secondary part
………..
// This constant is written in column D for rows for which an email
// has been sent successfully.
var EMAIL_SENT = “EMAIL_SENT”;
function sendEmails2() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = 5; // Number of rows to process
// Fetch the range of cells A2:C20
var dataRange = sheet.getRange(startRow, 1, numRows, 5)
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (var i = 0; i -1)
sh.getRange(‘U2:AJ2’).copyTo(sh.getRange(‘U3:AJ20’));
})
} }
}
Hmmm….what is happening now? Some sort of error I presume?
I can see that your for loop is missing an argument, but ultimately without knowing more about the structure of the data, I’m not sure that I can help. To get all of the values in a two-dimensional array, try using these lines:
var range = sheet.getActiveRange(); var values = range.getValues(); for (var i=0; i < values.length; i ++){ //this gets tricky since we need to look at two indicies //the first index is for the row. Second index for column var col0 = values[i][0]; }metin cetin (prof)
Sorry but I am not very good at scripts at all.
here is my form: https://goo.gl/phLI55
and here is the spreadsheet : https://goo.gl/J7qGDE
what I want is: after my students have finished the test, I want the test to be evaluated and the results are sent to the submitters’ emails. Could you help me fix this?
so far with this script I can evaluate the test and result emails are sent to the emails but the result area is
“undefined”. besides this script will not write whether the submitter has been sent before or not. Since I am new at scripts, I don’t know which script functions better for my request? Could you help me please?
the working script but no result is sent:
function myFunction(e) {
var firstName = e.values[1];
var lastName = e.values[0];
var userEmail = e.values[2];
var code = Math.floor((Math.random() * 9999) + 1000);
var subject = “Revision Submission Confirmation Code”;
var message = “Thanks ” + firstName + ” for KILLING THE CAT. YOU ARE IMPRISONED. YOUR CELL number is ” + code;
MailApp.sendEmail (userEmail, subject, message);
}
function copyBToA() {
SpreadsheetApp.getActive()
.getSheets()
.forEach(function (sh) {
if(sh.getName().indexOf(‘Form’) > -1)
sh.getRange(‘G2:L2’).copyTo(sh.getRange(‘G3:L20’));
})
}
I’ll take a look an see what advice I can provide. It may be a few days though, I have a busy week ahead 🙂
I want when user make survey and click submit, email will auto send to user’email + attach file (servey result of user)
You would just need to first create the file in Drive, populate that file with the user answers and then send it to them the same way I talk about in this video.
Hi,
I’ve used part 1 and it works fantastically thank you. Rather than using this to send out to other people who fill in the form I’m using it so they can send me an email in the correct format. There are two things I’d like to achieve and was hoping you can help me.
First:
I’d like the person filling in the form to be given an option to upload an image which will come with the email sent by the script as an attachment. I can seem to find how to do this.
Secondly:
The email that comes is in a the standard text format, is it possible to change the font and style that the email is sent with.
Any help will be greatly appreciated. Thank you.
I haven’t yet figured out a way to have users upload an image. There have been a few people before to ask for that, so I’ll keep it under consideration for a future tutorial.
You can send a styled HTML email instead of plain text using GmailApp.sendEmail() instead of MailApp.sendEmail(). Check out the official docs here: https://developers.google.com/apps-script/reference/gmail/gmail-app#sendEmail(String,String,String,Object)
It’s best practice to always send a plain text version in case the device doesn’t display HTML. Thanks for reading/watching! Hope that helps get you started.
Hello you can attach a .rar file instead of a .pdf file ?
Doesn’t look like the .rar is a supported MIME type in Google Apps Script: https://developers.google.com/apps-script/reference/base/mime-type
You might try just using the first step, get file by id, and see if that works. Otherwise, you might need to zip it. Let me know how that works out.
I am trying to get a form to email a notification with a google doc attached please could you help your video is very useful but just cant get a notification with a google doc attached would be really greatful if you could help.
Post the code you have and the error you are getting and I’ll take a look.
function myfunction(e){
var name=e.values[4];
var form=e.values[2];
var refemail=e.values[3];
var subject=”Senior Prefect Application”;
var message=”please fill in reference for +name”
var waiver=driveapp.getfilebyID(“1T-LjVza7Rcx6Ucp61PssWmqmZQNzIMGdA6vpP4hcjvg”);
var liabilitywaiver=waiver.getAs(mimeType.google_apps.document);
mailapp Sendemail(refemail,subject,message[attachments[liabilitywaiver]});
Ok, what error are you getting when this runs?
I’m also seeing a lot of syntax errors here on line 6, 7, and 8. For example, driveapp should be DriveApp. Similar with getFileById and MailApp and sendEmail. My recommendation is to use the autocomplete that Google Apps Script gives you. Start typing the line on a new line, and Google Apps should give you hints about the correct way to spell some of these functions. Post back if you still have issues.
function myfunction(e){
var name=e.values[4];
var form=e.values[2];
var refemail=e.values[3];
var subject=”Senior Prefect Application”;
var message=”please fill in reference for +name”
var waiver=driveapp.getfilebyID(“1T-LjVza7Rcx6Ucp61PssWmqmZQNzIMGdA6vpP4hcjvg”);
var liabilitywaiver=waiver.getAs(mimeType.google_apps.document);
mailapp Sendemail(refemail,subject,message[attachments[liabilitywaiver]});
hello,
following error is poped up
ReferenceError: “Mimetype” is not defined. (line 7, file “Code”)
i want to send a pdf in my google drive,
Make sure the code matches what I have in the example. I can see you are using “Mimetype” where it might need to be capitalized.
Thanks for the tutorial!
What is necessary to send a .mobi ebook?
I tried the code below but without success.
var Ebook = DriveApp.getFileById(“0B2zkDomu7ddwLVEtUW4xZUpIU1k”);
var EbookFile = Ebook.getAs(MimeType.MOBI);
Thanks,
Michael
I got it to send the file but only as a .bin file with the code below. Any thoughts on how to send the file and keep the extension the same?
var Ebook = DriveApp.getFileById(“0B2zkDomu7ddwLVEtUW4xZUpIU1k”)
var EbookFile = Ebook.getAs(“.mobi”)
Thanks,
Michael
Hey Michael,
That’s a great question. It doesn’t look like .mobi is a supported Mime Type as listed here in the Apps Script docs: https://developers.google.com/apps-script/reference/base/mime-type
Have you tried just attaching the results of your call to getFileById? In other words, using the variable Ebook as the attachment instead of EbookFile? That might be something to try.
The other workaround I could suggest would be to create a .zip file with the .mobi (and maybe a .pdf) inside and send that. The .zip file is a supported Mime Type, and most operating systems will automatically unzip a file for the user.
Let me know how it goes.
Thanks for reading, Jeff
Do you have to send the email from a gmail account or can you have the email send from a non-gmail account?
I’ve had a couple of people ask this question, and every time I’ve pointed them to Gmail Aliases as a potential way: https://developers.google.com/apps-script/reference/gmail/gmail-app#getAliases()
Not sure if it works or not since no one has ever reported back.
Hey there, hope that you still around.
Is it possible to make a tick box with an option to choose from several pdf files, and the the response mail will be send with the files that the user chose when filing the form?
And another one, can i use this form in my wix website?
Yes, you should be able to embed this form on any website of your choosing. As for the selecting files, this is possible, but you would need to write a lot of custom code for this. Basically, you would create an input with some choices, like a dropdown, then when the form is submitted, you look at that value and do some different stuff based on that value. If it is one value, you get a particular file; if another value, get a different file. Hopefully, that makes sense. I would think it could all be a series of if/else statements.
I am looking to send my google sheet to the person listed on the sheet.
function sendEmails(e){
var sheet = SpreadsheetApp.getActiveSheet() getURL();
var url = doc.getUrl(‘Name of the google sheet’);
var teamname = e.values[3];
var teamemail = e.values[2];
var subject = “Membership spreadsheet for team name”;
var message = “Please review the new member that has just registered”+ url;
MailApp.sendEmail(teamemail, subject, message);
}
I keep getting errors messages. What am I doing incorrectly? I do not want to send the spreadsheet as a PDF. I just want to send an email show the google sheet only when changes are made.
I think the first step would be to look at the message in the errors to see exactly what is going wrong. But, just from a quick glance I can see a few syntax errors. First, you can’t call
SpreadsheetApp.getActiveSheet().getURL()since the url is actually to the spreadsheet and not a sheet within a spreadsheet, so that should beSpreadsheetApp.getActiveSpreadsheet().getURL(). The line below that is likely erroring because there is no variable nameddocanywhere in your code. Hopefully that helps, but feel free to comment back if its still not working.Hello all and thank you for the valuable feedback so far!
This code works and fits my needs as long as the ID of the attachment remains the same (i.e. DriveApp.getFolderById(“1EQoAruexgigpQluyhY5jDOoXQlSDDDw5”). What I would like to do however is make an IF function for other cases where the ID needs to be different.
What I was hoping to create was an IF function that would pickup for those cases when a certain cell is recorded (e.g in Receipts!B21 = “Seek from drive instead”), and then to use the ID given in another cell reference to send as the attachment instead (e.g Receipts!F22 = “dynamic ID”)
Otherwise If Cell B21 is blank to continue using the ID given in DriveApp.getFolderById(“1ZmJi_VNg6-ukvceZORT2xizhT-qT-PctagBtGZj7NUM”); – which for 90 % time would be the case.
This is my current code (which is pretty much the same as the template given!)
function myFunction(e){
Utilities.sleep(5000);
var Name = e.values[3];
var Email = e.values[1];
var subject = “THANK YOU and your receipt”
var body = “Thank you ” + Name + ” for recent purchase 🙂 “;
var waiver = DriveApp.getFileById(“1ZmJi_VNg6-ukvceZORT2xizhT-qT-PctagBtGZj7NUM”);
var liabilityWaiver = waiver.getAs(MimeType.PDF);
MailApp.sendEmail(Email, subject, body, {attachments:[liabilityWaiver]});
}
Thanks and any help appreciated!!
That seems very doable. First, is the cell you are checking a part of the form submission? If so, you can grab it from the e.values[] array. If not, you’ll need to read it from the spreadsheet using the SpreadsheetApp to get the range values for that cell.
Then, I think your code should look something like this:
var otherId = e.values[21] || SpreadsheetApp.getActiveSheet().getRange(//range).getValues()
var idToSend = ''
if (otherId) {
idToSend = otherId
} else {
idToSend = '1ZmJi_VNg6-ukvceZORT2xizhT-qT-PctagBtGZj7NUM'
}
//Then just pass idToSend into the DriveApp.getFileById
Something like that should work. Just let me know how it goes and feel free to comment back if something doesn’t work as expected.
Hi BrownBearWhatDoYouSee!
Thank you for the quick response!
Yes this script is triggered by a form submit. The name, email and order number (which will dictate which attachment is sent) is collected via the form. I’ve created a vlookup to then extract the ID needed based on the order number given, which is found in another sheet: Receipts!F22 (so it looks like I need to read the spreadsheet?). In most cases however the following ID will be OK (1ZmJi_VNg6-ukvceZORT2xizhT-qT-PctagBtGZj7NUM), but in other cases my cell reference Receipts!F22 would give the right ID to used 🙂
And Thank you!! I can’t say how much i’m thankful for your help so far :))
Also BrownBearWhatDoYouSee, I should mention that i am a complete amateur on this 🙁
Thanks in advance!
Sorry for the spam:
This is my attempt at it….i made some notes in the script it self, but let me know if there is a better way!
function myFunction(e){
Utilities.sleep(5000);
var Name = e.values[3];
var Email = e.values[1];
//I created this line to obtain the ID from a specific cell in a specific sheet called “Receipts”. Not sure if I did this correctly…
var otherId = SpreadsheetApp.getActiveSheet().getSheetByName(“Receipts”).getRange(“F22″).getValues()
var idToSend = ” //is it possible to make this part read if otherId is blank (i am using a vlookup here that either reads an ID or leaves it blank i.e can revert to the default ID of “1ZmJi_VNg6-ukvceZORT2xizhT-qT-PctagBtGZj7NUM”
if (otherId) {
idToSend = otherId
} else {
idToSend = ‘1ZmJi_VNg6-ukvceZORT2xizhT-qT-PctagBtGZj7NUM’
}
var subject = “THANK YOU and your receipt”
var body = “Thank you ” + Name + ” for recent purchase :)”;
//not sure if i passed the idtosend correctly? I feel not…
var waiver = DriveApp.getFileById(idToSend);
var liabilityWaiver = waiver.getAs(MimeType.PDF);
MailApp.sendEmail(Email, subject, body, {attachments:[liabilityWaiver]});
}
Hi BrownBearWhatDoYouSee:
(Again my sincere apologies for the spam i n replies). This is my latest attempt at it:
function myFunction(e){
Utilities.sleep(5000);
var Name = e.values[3];
var Email = e.values[1];
//I created this line to obtain the ID from a specific cell in a specific sheet called “Receipts”. Not sure if I did this correctly…
var otherId = SpreadsheetApp.getActiveSheet().getSheetByName(“Receipts”);
var Id = otherId.getRange(“F22″).getValues();
var idToSend = ” //is it possible to make this part read if Id is blank (i am using a vlookup here that either reads an ID or leaves it blank), then use “1ZmJi_VNg6-ukvceZORT2xizhT-qT-PctagBtGZj7NUM”
if (Id) {
idToSend = Id
} else {
idToSend = ‘1ZmJi_VNg6-ukvceZORT2xizhT-qT-PctagBtGZj7NUM’
}
var subject = “THANK YOU and your receipt”
var body = “Thank you ” + Name + ” for recent purchase :)”;
//not sure if i passed the idtosend correctly? I feel not…
var waiver = DriveApp.getFileById(idToSend);
var liabilityWaiver = waiver.getAs(MimeType.PDF);
MailApp.sendEmail(Email, subject, body, {attachments:[liabilityWaiver]});
}
All of this look good at a quick glance. Have you tested it by submitting a form? What were the results?
Hello!
Didn’t exactly work out.
On the line that contains this code:
var Name = e.values[3];
i am getting the following error:
TypeError: Cannot read property “values” from undefined. (line 45, file “macros”)
It looks like there is no value is assigned to e? The form is triggered by a google form submit as well.
thanks for all your help so far!
Also in regards to the error, I can confirm the following
1. I have tested this by submitting a form
2. I am using zero indexing when referencing
var Name = e.values[3]; and for all other e. //(so i’m not too sure why its not capturing that value?)
3. The script works and sends the attachment successfully when it is for the more simple script below:
function myFunction(e){
Utilities.sleep(5000);
var Name = e.values[3];
var Email = e.values[1];
var subject = “THANK YOU and your receipt”
var body = “Thank you ” + Name + ” for recent purchase 🙂 “;
var waiver = DriveApp.getFileById(“1ZmJi_VNg6-ukvceZORT2xizhT-qT-PctagBtGZj7NUM”);
var liabilityWaiver = waiver.getAs(MimeType.PDF);
MailApp.sendEmail(Email, subject, body, {attachments:[liabilityWaiver]});
}
Thanks!
Hi Peter,
The error you reference happens when you run the form and it expects a form submission event. I’ve written more about that here: https://jeffreyeverhart.com/2016/08/06/fix-typeerror-cannot-read-property-values-undefined/
What error or behavior do you get when submitting the form?
JE
I can’t send a .ics file as an attachment. Is there a different file extension that is expected when attaching files of this type? (Outlook calendar events)
Hmm…it doesn’t look like that file type is forbidden by Gmail, as per this list, so what error message are you getting?
Any chance you can post some code here for us to review, along with the error message you get when trying to send the attachment?
Thanks for reading,
JE
Hi there,
How can I automate confirmation emails after a survey was submitted by a respondent, that has attachments tailored to their responses to the questions on GoogleForm? Is this possible?
Hello Andrea,
What you are describing is indeed possible, but it would require some variations on the script I have in this post. You’d either need to choose the right attachment based on some
if/elselogic, or alternatively you could generate a Google Doc for each submission as outlined in this tutorial. If you can post a code sample and some more specific details about what you want to do based on form submission, I can be more helpful.Thanks for reading,
Jeff
I have a google form that I want people to enter a staff email they are grateful for and another question includes a message they wish to send them for why they are grateful.
Can I have this google form auto send an email they add to the form with a decorative message including the message that was added in the google form?
Thanks
Hi Ashley,
I’d recommend checking out the first tutorial in that series, as that post describes the general pattern you are looking for. There is also a video on that post that walks through how to do the steps. If you get stuck, or aren’t sure about how to adapt the examples to your use case, post back in the comments with the code you have created so that I can offer some specific pointers.
Thanks for reading,
Jeff
Can you check this code, I was hope to send the email with their personal message, while dressing up the email with a boarder and image. Thanks in advance
function myFunction(e){
var userName = e.values[1];
var gratefulName = e.values[2];
var gratefulEmail = e.values[3];
var date = e.values[3];
var gratefulMessage = e.values[4];
var subject = “I’m Grateful For You”;
var message = “Thank you, from ” + userName + gratefulMessage;
var image = CardService.newImage().setAltText(“Thank You”).setImageUrl(“https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcSbLHdVTGRdfFuaP7EyL3o99hsGc3gSWnBCFTWnPuKXbsaVeouT&s”;
MailApp.sendEmail (gratefulEmail, subject, image, message);}
I changed it a bit so now it runs but not exactly how I was hoping. How can I space between userName & appreciates you?
The subject isnt showing up, instead it states IMAGE in the subject line.
The image isnt working in the email either.
How could the email be dressed up some how, with a background or boarder or image for something?
Do you have any suggestions to help? Thanks for all your help, I appreciate it!
Here is the Code:
function myFunction(e){
var userName = e.values[3];
var gratefulName = e.values[4];
var gratefulEmail = e.values[5];
var date = e.values[0];
var gratefulMessage = e.values[6];
var subject = “I’m Grateful For You”;
var message = userName + space + “appreciates you.” + gratefulMessage;
var image = CardService.newImage().setAltText(“Thank You”).setImageUrl(“https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcSbLHdVTGRdfFuaP7EyL3o99hsGc3gSWnBCFTWnPuKXbsaVeouT&s”);
MailApp.sendEmail(gratefulEmail, subject, image, message);
}
Thanks for the tips, JE!
I am a complete newbie when it comes to scrips, and the Google Suite for that matter. I like help on a project.
I am setting up a virtual Bingo game. Participants will register through a Google Form. When they submit the form, ideally, their confirmation message will include a virtual bingo card. But each participant must get a unique bingo card. I have 100 bingo cards (each a separate pdf file).
I set up a spreadsheet linked to the Form. Once a participant submits the form, the spreadsheet is copied to a second ‘tab’ in the spreadsheet where the bingo card file is pre-assigned in each row. If I can do a VLOOKUP on the participant’s name (an input from the form) I can select the unique bingo card file for that individual. Once I have the file selected, I can follow your script to send it to that individual.
However, I have no idea how to do a VLOOKUP script. Or perhaps there is a better method to achieve my goal? Thoughts?
Thanks,
Hi Sergio,
Thanks for reading. If you want to proceed with the VLOOKUP solution, you should take a look at Ben Collins’ site: https://www.benlcollins.com/?s=vlookup
He is way better at stuff with the actual spreadsheet than I am, so I’d point you there.
Regards,
Jeff
Thanks, Jeff. I didn’t use VLOOKUP. Instead, I used getLastRow function and got what I needed. It worked!
hello how can i able to get the attached doc filed by the user in an email notification right after they submitted the form.
this is the code i made so far
var summary = “PH-CEB-ITPE4 [PERMITS]”;
var body = formatDescription(response)
var attachments = attachments.getAttachments();
var pdfFile = file.getAs(MimeType.PDF);
{
attachments:[pdfFile, anotherFile, anotherFileStill]
}
Logger.log(body);
MailApp.sendEmail(to,summary,body,attachments);
}
appreciate your guidance on this.
Thanks!
What kind of error message are you getting now?
Thank you for the good work. I have a problem.
If I submit a form, expecting the pdf version to be sent to my temp files, I rather get back the template without any work done on text replacements.
Below is my code.
Thank you.
function sos(e) {
//e.values is an array of form values
var timestamp = e.values[0];
var emailAddress = e.values[1]
var surnameFirstname = e.values[2];
var university = e.values[3];
var course = e.values[4];
var semester = e.values[5];
var academicReporting = e.values[6];
var isyourtranscriptready = e.values[7];
var uploadtranscripthere = e.values[8];
var nextsemesterandbeyond = e.values[9];
var extraCurricularActivitiesReporting = e.values[10];
var generalinformation = e.values[11];
var dateandPlace = e.values[12];
var fullName = e.values[13];
var signature = e.values[14];
//file is the template file, and you get it by ID
var docFile = DriveApp.getFileById(“1wnX7BQ_TXkI-Gs90GbpVyyKCSe7bOY-a”);
var tempFolder = DriveApp.getFolderById(“176AsTa7XNHdXqbx_BmFxsqp7W6536e_A”);
var copy = docFile.makeCopy(fullName + ‘,’ + semester + ‘,’ + timestamp, folder);
var doc = DocumentApp.openById(copy.getId());
//Then we call all of our replaceText methods
body.replaceText(“{{timestamp}}”,timestamp);
body.replaceText(“{{surnameFirstname}}”,surnameFirstname);
body.replaceText(“{{university}}”,university);
body.replaceText(“{{course}}”,course);
body.replaceText(“{{semester}}”,semester);
body.replaceText(“{{academicReporting}}”,academicReporting);
body.replaceText(“{{isyourtranscriptready}}”,isyourtranscriptready);
body.replaceText(“{{uploadtranscripthere}}”,uploadtranscripthere);
body.replaceText(“{{nextsemesterandbeyond}}”,nextsemesterandbeyond);
body.replaceText(“{{extraCurricularActivitiesReporting}}”,extraCurricularActivitiesReporting);
body.replaceText(“{{generalinformation}}”,generalinformation);
body.replaceText(“{{dateandPlace}}”,dateandPlace);
body.replaceText(“{{fullName}}”,fullName);
body.replaceText(“{{signature}}”,signature);
body.replaceText(“{{emailAddress}}”,emailAddress);
//Lastly we save and close the document to persist our changes
doc.saveAndClose();
}
function myFunction(e) {
var subject = “Semester report received”;
var body = “If you have not sent your transcript, please let us have as soon as you got it from your school. All the best in your studies.”;
var docFile = DriveApp.getFileById(“1wnX7BQ_TXkI-Gs90GbpVyyKCSe7bOY-a”);
var semesterSubReport = docFile.getAs(MimeType.PDF);
MailApp.sendEmail(email, subject, body, {attachments:[semesterSubReport]});
}
It looks like you are missing a line where we get the document body as a variable. It should be something like this:
var doc = DocumentApp.openById(copy.getId());
var body = doc.getBody();
//Then we call all of our replaceText methods
body.replaceText(“{{timestamp}}”,timestamp);
Is there a way to select an individual attachment, in that each response would require a different file to be added to the email? Many thanks. In advance
Hi Dave,
Yes, that is very much possible. If you want to check the value of one form field, then decide the attachment, you would create a series of if/else code blocks: https://www.w3schools.com/js/js_if_else.asp
Feel free to post back if you have any other questions.
Regards,
Jeff
Hi,
My Google form accepts an attachment in the form of a response to one of the questions. In the spreadsheet, I get the drive link to the attachment against each response. Can I send these attachments as PDFs? basically can the function DriveApp.getFilebyID take a variable key rather than a constant copy pasted one?
Yes, you could do that. You’d need to parse the id from the attachment link, then call getFileById and then getAs(‘application/pdf’).