Create a mail merge using Gmail and Google Sheets
Ғылым және технология
Everything you need to get anything done, now in one place: introducing Google Workspace, formerly known as G Suite.
Whether you are in sales, marketing, education, project management, data analysis, etc., it’s common for people to want to send emails to multiple recipients with information that is customized to each. In this episode, learn about Martin Hawksey's mail merge solution, which helps anyone send emails with customized content from a Google spreadsheet, and Gmail.
Create a mail merge using Gmail and Google Sheets → goo.gle/30hBeQE
Learn more about Gmail Service → goo.gle/35QkvF6
Learn more about what you can do with Spreadsheets goo.gle/2uLyv6s
Current email quotas → goo.gle/2QQxL8x
Blog post on coding pattern used to conditionally read and write Google Sheets data → goo.gle/2TgLDL7
Previous episode → goo.gle/30gKtkd
Sheets to Apps playlist → goo.gle/SheetstoApps
Customizing Google Workspace playlist → goo.gle/2Rp30br
Subscribe to Google Workspace → goo.gle/GSuite
Product: Cloud G Suite Development, Sheets, Gmail, Apps Script; fullname: AGV, Martin Hawksey;
#GoogleWorkspace
#SheetsToApps
#CustomizingGoogleWorkspace
Пікірлер: 171
Try Google Workspace For Free Now → goo.gle/2HwiYOE
@alverniagolfer73
2 жыл бұрын
this is for a business page. How to I make one for casual mail merges?
@apurbaislam1984
Ай бұрын
Q 0:29
So easy and worked like a charm. Love the idea that it links to a draft email rather than having to create the whole email content within the script.
Hi all, a really useful tip. When drafting your email, make sure you've changed to plain text mode, otherwise the {{}} tags in the body of your email won't get read by the script and will appear blank when you hit send. Like me, if you want your email to have a fancy footer and some nice fonts, you can copy and paste your plain text email body into a new draft in normal mode. As long as the {{}} tags are in plain text the script will read them and the merge will work. It's basically taken me about a day to work this out, in which time I could have written all the emails I needed to send out by hand!
@momentsofmind1234
Жыл бұрын
Thank you, it almost took me a day as well until I read this comment! Thank you very much
@anandamayakrishnadas657
Жыл бұрын
Thank you for this tip. I thought I was going insane when it was not working
@sam_safari
5 ай бұрын
i faced the same problem how can you help me with this
Very frustrated that Google included a template spreadsheet with a script that has not been verified by Google. They don't mention this being a typical step in the process or let us know that this sheet script was developed by Google or even how to verify that it is sound and sanctioned by Google. Has anyone gone ahead and continued through verification and allowing it to view your contacts? Was it fine? Did you mail merge work?
Worked for me, awesome!
yeah... it does't work and I don't know why. It says "end line too short" I googled it and nothing came up. Any idea would be extremely appreciated.
This worked well! You can even add columns after the last one and it will insert them as well. Can you add columns before the eamil date sent comn and get it to work?
Do not copy cell names from Sheets. I had the same issue that many were complaining about. If you copy to notepad, and then copy to your draft, it has worked 100% of the time for me.
That's a brilliant tool! Thank you. I have a question though: Is it possible to use multiple sheets in which they may include different recipients (email addresses)?
I want to mail merge my spreadsheet into my docs, could you help me?
Very well presented! Amazing great job
Thanks to every employees of Google sheets. It helps me a lot.
I can't get around a warning that pops that this is not a verified app. and the screen where I put in the draft subject line never comes up. Very Frustrted. HELP.
Thank you :)
This is great, thank you! Is there a way to see which emails were opened?
When recording a macro, I want to apply it to all of my excel sheets, not just one
It worked! It really actually worked!!! BTW to get past the warning, not verified, you must click the "Advanced" hyperlink and then the next hyperlink with the "(Unsafe)" option beside. Then you must click on the allowance to let the script touch . You can change 3rd party access later too under settings.
@TromboneAl
3 жыл бұрын
Many thanks! That solved it for me.
This only works for personal gmail accounts? I am not able to see the mail merge option in my business gmail acount.
"Cannot read property 'replace' of undefined" is the content in the email sent field what is the reason
Can Google please increase the Email quota? The current quota is too low.
"Mail Merge" isn't an option in the Google Sheet. Something in these instructions, both video format and text is missing a key piece of information.. HOW do we get, "Mail Merge" option on Google Sheets?
@jbkrobin
Ай бұрын
By going to the link, they put in the description.
Does anyone know if there's a max limit of how many emails can be sent from a spreadsheet? Looking to do ~1,400. Will it work?
How is the read receipt part seen?
Mail merge with sheet and form responses. Is it possible in app script?
Is it possible to add a second email address to send to for each contact
how can we attach a PDF to this mailing?
The First name won't show on the mail, how to solve this issue?
Hello! I have a private Google account, when I try to use this app, I Receive a security message blocking the application, even though a moment before I approved the script, what do I need to disable for it to work?
I tried and it it says failed to send. Composed email in gmail. Types in subject line of an open email draft and it populated failed to send. Tried again and it said the starting number was too low. Can you help?
Where is the link to access Google Sheets for Mail Merge ?
This worked once, when I copy pasted {{value}} from the spreadsheet to notepad and the copied there to the draft. But the second email I wanted to send this did not work any more. The sheet does not get populated and the mail goes with a blank instead of the value. Have tried typing copying to notepad and just copying. changing the bold formating but now it does not work any more.
When I click on "mail merge" in the xls file, it says "authorisation required, a script attached to this document needs your permission to run". If I click on "continue" I received a message saying "this app isn't verified". How can I solve this?
@zq_77
3 жыл бұрын
I had that problem. If you're using Chrome browser, make sure you are signed in on Chrome with the same user you intend to do the mail merge from. I was facing this issue because I was signed in to Chrome with my personal gmail trying to do a mail merge from a company google workspace account.
@TromboneAl
3 жыл бұрын
I have the same problem, but zqush's suggestion didn't work for me.
@TromboneAl
3 жыл бұрын
Here's the solution I found (from a comment here): BTW to get past the warning, not verified, you must click the "Advanced" hyperlink and then the next hyperlink with the "(Unsafe)" option beside. Then you must click on the allowance to let the script touch . You can change 3rd party access later too under settings.
Any tutorial video on how I can make my own Mail Merge application like GMASS, Mergo, YAMM?
I don't get the "mail merge" option in the Sheet.
Does it stop at a 100 emails if the list is sheet has more than a 100 emails?
where can I find the mail merge button? not showing up on my google sheets
@user-tr1zt1mq7h
4 ай бұрын
same here.
Great. Now how can I do this with Google Docs?
I can't see the script editor in tools, has it been moved? Thanks
I cannot add the mail merge tab onto my google sheets. Is there something specific i should be doing ?? - please help
Where can I find that google sheet?
Thank you
Excelent, I'm working as a teacher and this is very useful to send personalized communications to my students. But I have a question, I would like to send them qualifications pdf by email merging. Is there any way to link different pdf attachments from everywhere (excel cell or folder path) to the list of emails? Thanks in advance!
@bjameel79
Жыл бұрын
Add new column and call it Qualification, add all the qualifications in Google drive and make each with shareable link. Add the link for each in Google sheet under Qualification column for each student.
I wonder if we can do this in google chat instead of email.
maybe im just a novice, but when i tried to do the mail merge, it said that Google has yet to verify it and that it may be unsafe. How can that be if they made a video about it and it is on their site gallery?
@nicholasniles1996
4 жыл бұрын
Same here. Is there a solution for this?
@adrianagoldenberg4862
4 жыл бұрын
@@nicholasniles1996 me too, let me know if you solve this!
@lala89op
4 жыл бұрын
Once you get the error window, you must click the "Advanced" hyperlink and then the next hyperlink with the "(Unsafe)" option beside. Then you must click on the allowance to let the script touch . You can change 3rd party access later too under settings.
Help. For some reason the fields aren't being filled in. I have made sure that the columns and fields are titled the same and used {{}} with no extra spaces. It generates and sends the email but with blanks where tge fields were.
@jacu707
3 жыл бұрын
Yep. I faced the same issue too
@LouisWongPhysics
3 жыл бұрын
same
@shrutisinghi7215
3 жыл бұрын
faced same issue, what you can do is from a blank cell copy paste the format and use that format on heading (like first name) then retype the heading (first name). Copy paste the heading (first name) in note pad and in the email copy paste from note pad instead of the spreadsheet. It worked for me.
@mariarocioramirez116
2 жыл бұрын
@Deepak Gupta stated the following "Hi, I got a solution. Both heading and the codes you enter in email draft should be same in format, same case, same spaces if one is bold /italics the code should also in bold /italics."
How do I enable the Mail Merge submenu? I dont have it.
Is there a quick way to copy/paste multiple email addresses in the mail merge sheet or do you have to do it one by one? I have over 100 email addresses I'd like to put into a mail merge sheet and was hoping not to have to copy/paste each one which would take forever.
@MidouTV
Ай бұрын
Does sending emails for 100 addresses make my emails sent to spam section?
Very nice explanation. How to including customize attachment to mail merge?
there is no mail merge option in the spread sheet
@nashleyjimenez617
3 жыл бұрын
right
@vedprakash-zz6hb
3 жыл бұрын
Open in desktop or click "desktop site" in mobile browser to see mail merge option
How can I use this formula with a dropdown feature? The formula didn't understand the dropdown feature. Plus, for some reason, it didn't include the first name.
when i send an email with emojies they dont appear to the sent email..they appear like a questionmarks ?????
what am i missing i dont have a mail merge option on my sheet
How to insert an image from the google sheet ?
I'm getting an error message that the Gmail cannot be found, but it has been created in Gmail. Any ideas?
hOW DO YOU ADD URL AS SOMETHING THAT CAN BE MERGED? I HAVE BEEN TRYING FOR 3 HOURS AND IT WON'T LOAD HTTPS OR FB EVENTS
Really strange to me that email quota is only 100 recipients per day, anyone know how to raise this? I need to send an email using mail merge to 760 recipients...
pricing please!
Is it possible to incorporate an image into the email draft? When I did the received email showed the 'broken image' icon.
@christyolivianny7801
4 жыл бұрын
yes i tried and i worked
@rodrigotmontenegro
3 жыл бұрын
I have the same issue, I tried several times even modify the size of image but, it's keep showing "broken image" icon, it sad bc the code work perfectly, great solution btw... Anyone can help us...
@rodrigotmontenegro
3 жыл бұрын
I could resolve doing this... attaching .png as URL .. (sites.google.com/site/scriptsexamples/available-web-apps/mail-merge/documentation/known-issues/images-are-broken-in-emails-sent) Thank you! he he he!
I cant add column. The program wont run
Hey I am not able to sent the E mail to more then 101 people is there any way to add few more people
Works with the Sheet attached but gives Exception: The starting column of the range is too small. when i create my own sheet
@manuelalopez-valentin6489
3 жыл бұрын
I had the same issue and to fix it I had to make sure that I had both a "Recipient" and a "Email Sent" column. Another things is that if you have a column for the emails with a different name, then you need to change the name in the script to match the name of your column.
@misssharik
3 жыл бұрын
@@manuelalopez-valentin6489 Thanks Manuel, this worked for me!
when clicked on sccript- it says not available at this time
Hello, great video. I have lists of email addresses which are mainly domain emails but there are also gmail emails mixed through the lists. Can anyone tell me how i can remove the gmail emails please.
Fabulous
Body of email is not working its not picking up field. please help
I have never come across such a soft and polite American !! i went ahead and heard her 3 times !!. Nice who ever she is .
Mine showing exception- starting column of range is too small
Would be nice to be able to add recipient specific attachments. I guess you could add a hyperlink to the attachment if it is stored in Drive and shared properly. Also, it would be nice to create Google Docs using Mail Merge. I guess it's just a matter of writing the appropriate Apps Script?
@MartinHawksey
4 жыл бұрын
Linking to a shared Google Drive would work - if you still need to include an attachment I've added a couple lines of code to this version that does this docs.google.com/spreadsheets/d/1fBD78IfXjqN9EcCZl_wOeNDXtP1b3owP_Sg1Hr14Aqs/copy
@andrzejzarzycki
3 жыл бұрын
@@MartinHawksey Hello, How to make it work as an inline image not an file attachement?
@MartinHawksey
3 жыл бұрын
@@andrzejzarzycki people have reported that this technique works sites.google.com/site/scriptsexamples/available-web-apps/mail-merge/documentation/known-issues/images-are-broken-in-emails-sent (written for a different mail merge add-on solution but also works for this one)
@MylesPalma
2 жыл бұрын
@@MartinHawksey hi why is it that the first name of the recepient doesn't work? It's just the Hello , comma that appears as I tested it out on my email. Thank you. 😌☺️
@MartinHawksey
2 жыл бұрын
@@MylesPalma Usually the issue is hidden formatting or not using case sensitive tokens in the Gmail draft. If token names exactly match column headings then points to hidden formatting. To fix this select the token including {{ }} in your Gmail draft and press Ctrl+\ or use clear formatting from the toolbar
Hello, This works perfectly fine, Can you please help me with, how can i mark cc to someone?
@ShihanQu
Жыл бұрын
The script actually already has CC functionality. It's commented out in the script, but you can uncomment if it's just one email address to CC. If you have a whole row of CC addresses, you can have chatgpt modify the script for you
Does anyone else get a warning from Google saying this app has not been verified?
@Jszachara
4 жыл бұрын
Yes. Any solutions yet???
@dogmammoth
4 жыл бұрын
@@Jszachara when we get the "This app isnt verified" page, click "advanced" on the bottom left to expose the advanced options. Then click "Go to Gmail/Sheets Mail Merge (unsafe)" - that worked for me!
Hi, It's a very good feature and you explain it very well, I also want to attach different files for every recipient. kindly help me out
How can i change my alias?
@zq_77
3 жыл бұрын
did you find a way?
Good 🙏
Thanks
This will send emails to the recipients, but it won't populate {{}} data. It just leaves it blank. Can anyone help?
@dennisemunoz843
3 жыл бұрын
Same here...:/
{{First Name}} {{bla}} {{bla}} doesn't work on the main body. Only it's working for the subject part. Am I doing any mistake?
Update: Tools > Script Editor has moved to Extensions > Apps Script
Does anyone know how to CC others using this template? I tried the following, to no avail: In my draft email, I added an email address to the CC space, hoping that the CC'd person would be CC'd on all mailmerged emails, but that did not work. Does anyone have any experience doing this?
@ShihanQu
Жыл бұрын
This should work. Just make sure it's uncommented by removing the double backslashes
Please help. This is not working for me at all. It works in the subject line, but not in the body of the message. I am using the example provided but I still can't get things to work. For example: Hello {{First name}}. Here is your discount code {{Discount code}}.
Same question as others below: is this safe?
@dansaleemdo7893
4 жыл бұрын
If it's not, they won't have someone from google talk/advertise about it or post it into their channel, otherwise, we will see lawsuit.
Hi Thanks for Video In my case {{Name}} is not appearing and sent timing is different than actual. Could you please help to sort out this issue
@katem6159
2 жыл бұрын
same - none of the fields are appearing, I cant see where my error are.
@shrirampful
2 жыл бұрын
@@katem6159 After surfing enormously on internet I have following observations 1. We need to have chrome extension(third party application). They charged yearly fees based on volume of mails. But we need to give them full access to our mailbox. That is the other side of story. 2.While putting description from excel /google sheet column head in mail body right click and select Paste as plain text. Example : Dear {First Name} Put First Name as plain text. 3. If you want to have emojis & to make mail colorful then in google sheet---->tools/script editor Open Script editor go to line no 90 remove syntax // and change text GmailApp for MailApp. Things worked at my end but still Email sent duration displayed is vague.
@katem6159
2 жыл бұрын
@@shrirampful thanks so much for sharing this
How can I change the sender's name?
@zq_77
3 жыл бұрын
did you find a solution?
Script editor is missing in my case!?
Hi, I appreciate your effort. I am facing a problem that the data in the body of the email is not fetched from the google sheet. What can be the reason? Data in Subject is ok and mails are also going. But there is a problem in the body of the email.
@josephhearn9587
4 жыл бұрын
deepak, did you solve, i'm having same issue...
@deepakgupta9277
4 жыл бұрын
@@josephhearn9587 Not yet, I got a mail from Gsuite and I will follow it on weekend.
@deepakgupta9277
4 жыл бұрын
@@josephhearn9587, I will send you the solution once solved
@deepakgupta9277
4 жыл бұрын
@@josephhearn9587 Hi, I got a solution. Both heading and the codes you enter in email draft should be same in format, same case, same spaces if one is bold /italics the code should also in bold /italics.
During test I only used the {{First name}}, it sent the emails but the first names didn't appear. When I want to try it a second time the emails dont send at all. Any idea why??? Thanks
@josephhearn9587
4 жыл бұрын
Darren, I'm having the same problem, did you work it out?
@Marbella-Sands
4 жыл бұрын
Joseph Hearn hi I did sort it. What you have to do is leave the first two names unchanged then list all your own names underneath them. You can change the email of the first two to your own but you can’t substitute the name. A simple solution but not obvious. Good luck 👍
@deepakgupta9277
4 жыл бұрын
@@Marbella-Sands Both heading and the codes you enter in email draft should be same in format, same case, same spaces if one is bold /italics the code should also in bold /italics.
@deepakgupta9277
4 жыл бұрын
@@josephhearn9587 Darren Sands Both heading and the codes you enter in email draft should be same in format, same case, same spaces if one is bold /italics the code should also in bold /italics.
@Marbella-Sands
4 жыл бұрын
Deepak Gupta many thanks Deepak 😉
Me gusta mucho lo que asen ❤️💜💚
it stopped working today
not working in body of the mail
Could not make it work. I added a couple of data fields and ended up with the same boring and inexplicable error message every time "Exception: The starting column of the range is too small." Could anyone with a coding head descramble this please? No wonder CRM packages and marketing automation tools sell so well. :-)
@Chaser90EK
3 жыл бұрын
You need a field call Recipient and another called Email Sent. Leave Email Sent blank and it populates when that email sends.
Does Google Workspace even care about your questions? I don’t see any replies from them 🤷🏾♀️
It didn't work for me.
the new line isn't working with plain text!!
I lied, their sample worked but when I copied my data in I got this error message: Exception: The starting column of the range is too small.
@angiesuszek3313
2 жыл бұрын
Partial Fix: Column heading should be Email Sent, both words capitalized. So it sent emails with partial info but not all of my merged fields, very frustrating.
@angiesuszek3313
2 жыл бұрын
So after much tinkering here is what I learned: This process is very sensitive to your formatting (bold, italics, etc.). In your email, type your column heading exactly. If using bold in your headings, the brackets {{ }} and letters must be in bold as well.
for a google channel, the tutorials could be improved
Great video, really helpful. But it didn't tell me I could only send 100 emails a day.
This has stopped working for me!
An amazing idea to pull out data from google sheet to your Gmail template. Sadly it did not work for me. The subject line was working fine but the body contents came out blank.
@PrincessToni.
3 жыл бұрын
SAME!!! Did you solve it?!
@shielabonite9711
3 жыл бұрын
@@PrincessToni. I used a different mail merge. One that will allow me to use text with hyperlinks in the body of the email. I mean no offense to the author... just my honest feedback that it doesn't work.
@PrincessToni.
3 жыл бұрын
@@shielabonite9711 After hours of trying, it worked! I typed the code in the body instead of copying it from the sheet. I think the problem was that it was in the bold format. Try one more time and see!
@dennisemunoz843
3 жыл бұрын
@@PrincessToni. you typed {{First name}} ?
@dennisemunoz843
3 жыл бұрын
NVM it worked
This didn't work. I'm getting an error messages that says oops email didn't send.
Cute
such a waste of time. GOOGLE DO BETTER