Last year I had a ‘mail merge using Gmail and Google Sheets’ contribution accepted on the G Suite Developers Solution Gallery. Just over 6 months on there has been lots of useful feedback and requests for solutions to some of the limitations of the original published script. In the meantime Google has also made the new V8 runtime for Google Apps Script generally available. Given this it seemed appropriate to revisit the original solution updating it for V8 as well as covering the most commonly asked for changes. In this post I’ll highlight some of the coding changes as well as some easy modifications.
To get started if not already you can follow these setup instructions:
Setup
- Create a copy of the sample Gmail/Sheets Mail Merge spreadsheet.
- Update the Recipients column with email addresses you would like to use in the mail merge
- Create a draft message in your Gmail account using markers like
{{First name}}
, which correspond to column names, to indicate text you’d like to be replaced with data from the copied spreadsheet. - In the copied spreadsheet, click on custom menu item Mail Merge > Send Emails.
- A dialog box will appear and tell you that the script requires authorization. Read the authorization notice and continue.
- When prompted enter or copy/paste the subject line used in your draft Gmail message and click OK
- The Email Sent column will update with the message status.
Next steps and easy modifications
Additional columns can be added to the spreadsheet with other data you would like to use. Using the {{}}
annotation and including your column name as part of your Gmail draft will allow you to include other data from your spreadsheet. If you change the name of the Recipient or Email Sent columns this will need to be updated by opening Tools > Script Editor.
Advanced Send Parameters (cc, bcc, sender name/from, replyTo)
As part of the GmailApp.sendMail()
method there are advanced options for sending emails with cc, bcc and more. These parameters have been included in the shared script but commented out. For example, to change the name the email is from you would uncomment* the name parameter and add a value:
*To uncomment remove the //
at the start of the line
// @see https://developers.google.com/apps-script/reference/gmail/gmail-app#sendEmail(String,String,String,Object)
// if you need to send emails with unicode/emoji characters change GmailApp for MailApp
// Uncomment advanced parameters as needed (see docs for limitations)
GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {
htmlBody: msgObj.html,
// bcc: 'a.bbc@email.com',
// cc: 'a.cc@email.com',
// from: 'an.alias@email.com',
// name: 'name of the sender',
// replyTo: 'a.reply@email.com',
// noReply: true, // if the email should be sent from a generic no-reply email address (not available to gmail.com users)
attachments: emailTemplate.attachments
});
More details on the relevant advanced parameters, limitations and usage from the official documentation is copied below:
Name | Type | Description |
---|---|---|
bcc | String | a comma-separated list of email addresses to BCC |
cc | String | a comma-separated list of email addresses to CC |
from | String | the address that the email should be sent from, which must be one of sender’s account aliases. |
name | String | the name of the sender of the email (default: the user’s name) |
noReply | Boolean | true if the email should be sent from a generic no-reply email address to discourage recipients from responding to emails; this option is only possible for G Suite accounts, not gmail.com users |
replyTo | String | an email address to use as the default reply-to address (default: the user’s email address) |
Sending emails with emoji/unicode
If you like to include a little bit of 🤪 or 📈 in your emails a feature (or bug) of GmailApp
is that it can’t handle most of the modern unicode/emoji characters (details in this SO thread). Fortunately, there is an easy solution by switching GmailApp
to MailApp
by opening the script editor in your copy of the solution and changing line 90 from:
GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {
to
MailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {
If you are wondering why the script doesn’t just use MailApp
while the MailApp advanced parameters are similar there is no from
parameter and from personal experience we use from
more than emojis.
Sending emails to filtered rows
There are various ways to detect if a row has been hidden by a sheet filter. One method is isRowHiddenByFilter()
which uses Google Sheets filter. If you would like to modify this solution so only emails are sent to rows that are visible when filtered you can change line 83 from:
if (row[EMAIL_SENT_COL] == ''){
to (this assumes the header row is only row 1):
if (row[EMAIL_SENT_COL] == '' && !sheet.isRowHiddenByFilter(rowIdx+2)){
As this method is potentially expensive in terms of script run time an alternative approach is using the Advanced Sheets Service to make a batch call to the Sheets.Spreadsheets.get
method that gets all the hidden rows, which can then used this to supplement the source data:
/**
* Add hidden row identifier to sheet data.
* @see https://sites.google.com/site/scriptsexamples/learn-by-example/google-sheets-api/filters#TOC-Get-filtered-rows
* @see https://tanaikech.github.io/2019/07/28/retrieving-values-from-filtered-sheet-in-spreadsheet-using-google-apps-script/
* @param {string} ssId of the spreadsheet
* @param {string} range of the sheet
* @param {Object} sourceData of sheet as object
* @return {Array} of data with hidden row identifier.
*/
function addFilteredRows_(ssId, range, sourceData) {
// limit what's returned from the API
const fields = "sheets/data/rowMetadata/hiddenByFilter";
// make Sheets API call
const sheet = Sheets.Spreadsheets.get(ssId, {
fields: fields,
ranges: [range]
}).sheets[0];
// get the row metadata
const data = sheet.data[0].rowMetadata;
// update sourceData with hidden row status
data.map((ar, i) => {
(ar.hiddenByFilter) ? sourceData[i].hidden = true: sourceData[i].hidden = false;
});
return sourceData;
}
A variant of this solution that implements addFilteredRows_()
is available in this commit.
Scheduling/triggering bulk emails
If you’d like to send bulk emails at a scheduled time or trigger sending based on another event the following functions can be copied into the code in your copy of the sheet via Tools > Script editor. The sendScheduledEmails_()
function allows you to define the sheet
and email draft subjectLine
you want to use.
If you’d also like to then schedule the send you can either use the Edit > Current project triggers dialog in the Script Editor or edit and run the setupScheduledSend()
function.
/**
* The sheet and email draft to send
*/
function sendScheduledEmails_() {
const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet2'); // <-- Insert sheet name to use data from
const subjectLine = 'Your personal discount code {{Discount amount}}'; // <-- Insert email draft subject line
sendEmails(subjectLine, sheet);
}
/**
* Create a script trigger to run sendScheduledEmails_
*/
function setupScheduledSend() {
const triggerDay = new Date('April 17, 2020 23:20:00'); // <-- date/time to send emails
ScriptApp.newTrigger("sendScheduledEmails_")
.timeBased()
.at(triggerDay)
.create();
}
Coding changes
As well as some V8 runtime syntax changes when revisiting the original mail merge solution code there were a couple of common requests I thought worth incorporating, plus some areas where I felt the code could be refactored. As some of these code changes might be useful in your own projects I’m highlighting them below:
Using formatted cell values for currencies, dates and more
Thanks to a comment from Andrew Roberts this solution has been updated to use the SpreadsheetApp
service call to .getDisplayValues()
, which “takes into account date, time and currency formatting, including formats applied automatically by the spreadsheet’s locale setting”. For reference, the original Sheets.Spreadsheets.Values.get
method is included below:
When using the SpreadsheetApp
service and calling .getValues()
the data is returned as either Number
, String
or Date
. For example, if cells are formatted with currencies these are converted to numbers and dates turned into a Date
object. The solution was to switch to the Advanced Sheets Service, which defaults to the formatted cell values when calling Sheets.Spreadsheets.Values.get
.
// Fetch values for each row in the Range
// @see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get
const data = Sheets.Spreadsheets.Values.get(SpreadsheetApp.getActive().getId(), sheet.getName()).values;
Cleaner token replacement
I was never happy with the token replacement used in the original solution and when I came across this efficient JavaScript string replacement snippet on Stack Overflow it was easy to modify for the {{}}
notation used in this mail merge solution:
/**
Fill template string with data object
@see https://stackoverflow.com/a/378000/1027723
@param {string} template string containing {{}} markers which are replaced with data
@param {object} data object used to replace {{}} markers
@return {object} message replaced with data
*/
function fillInTemplateFromObject_(template, data) {
// we have two templates one for plain text and the html body
// stringifing the object means we can do a global replace
let template_string = JSON.stringify(template);
// token replacement
template_string = template_string.replace(/{{[^{}]+}}/g, key => {
return data[key.replace(/[{}]+/g, "")] || "";
});
return JSON.parse(template_string);
}
Summary
Hopefully you find this solution useful. Please keep in mind that this script is designed to be a sample solution that demonstrates how you can solve common problems with G Suite. You are welcome to build and modify the shared code as you need. Feel free to reach out to me if you have any questions.
Klaus
Thank you, this is simple & great, just what I was looking for. And came just at the right time 🙂
Sean
Thanks Martin. You are a lifesaver, this has solved a massive problem for me, great work!!
mhawksey
Thanks Sean – glad this update had been useful
Andrew Roberts
Very nice. I’m sure that is going to come in very handy.
Re. “Using formatted cell values for currencies, dates and more”. You can use SpreadsheetApp.getDisplayValues() to avoid having to go into the Advanced service.
I spent many an hour formatting the values before I discovered that function – although I didn’t know you could do it with the Advanced service too.
Another thing I’ve come up against was missed case-differences in the placeholders, so you can use (i) to make them case-insensitive (https://stackoverflow.com/questions/3939715/case-insensitive-regex-in-javascript).
This is my take on the popular mail merge everyone should have in their store cupboard: http://www.andrewroberts.net/2014/10/google-apps-script-create-pdf/.
Martin Hawksey
… not sure how I missed
.getDisplayValues()
and this change will make this a lot cleaner. With this I think it makes sense to switch to just usingSpreadsheetApp
to limit the scopes.Thanks also for highlighting how to make placeholders case-insensitive. Personally, I prefer having them case sensitive but other might want to make this mod.
Merging for doc is also a common use case I see so thanks for sharing the link to your post 🙂
Andrew Roberts
If we merge your mail merge and my doc merge we could have the best open source “merge” script out there!
Chris Paine
Really useful tool, but inline images get converted to attachments – any way to avoid this?
Martin Hawksey
Hi Chris – I think this might be how images are added to the draft email. When copy/paste images from another source they should be included inline for each email. If not let me know and I can investigate further
Nambirajan
I tried several times, yet everytime the images gets converted into attachments
1. Tried Drag and drop keeping inline
2. Tried uploading
3. Tried Copy paste
Josh
I am experiencing the same behaviour – inline images aren’t show in the email either – only the file name is shown. Any news on a fix for this?
Josh
Hello all, I found a solution to show inline images and remove the attachment:
Follow the steps in the section titled: ‘How to Properly Upload Your Images in Gmail?’ – https://sites.google.com/site/scriptsexamples/available-web-apps/mail-merge/documentation/known-issues/images-are-broken-in-emails-sent
Martin Hawksey
For others coming to this thread I recently found a better way to include inline images which has been updated in the shared Google Sheet from the Solution Gallery and this post (for those interested in the detail written up in this post).
Martin Hawksey
Data Privacy when using this mail merge solution
I’ve had a couple of people ask about privacy/security with this published solution and here is an explaination:
This solution uses Google Apps Script which is a scripting language that runs on Google servers https://www.google.com/script/start/
Apps Script can be used by developers to build 3rd party Add-ons for Docs, Sheets, Slides and Forms. There are add-ons for Google Sheets which can be used for mail merges https://gsuite.google.com/marketplace/search/mail%20merge
With these add-ons users can’t see the Apps Script code that runs them and potentially they can pass the data you use via their own servers. As a layer of security Google has a verification process which requires 3rd party developers to publish a privacy policy.
The mail merge in the solutions gallery is different in that it’s not shared as an add-on. Instead all the Apps Script code used for this application is attached to the copy of the Google Sheet you make in the setup instructions. The Apps Script code is attached to the copy of the Google Sheet and I as the original sheet owner don’t have access to either the copied Google Sheet or the copy of the Apps Script code attached to it.
In the copy of the solution you can see all the code by opening Tools > Script editor… I appreciate that not being a developer it might not make much sense hopefully it gives you some comfort to see the code that is being executed. A test you can do is make another copy (copy B) of the original copied solution (copy A). If you edit the code in the Script editor for copy A and save it, when you open copy B the script is unchanged (this means when you run this solution there is no way for me as a the original developer to change the code and do something malicious).
In terms of this solution and where the data goes, as Google Apps Script runs on Google servers it remains in your Google account, the script essential runs as if you were at the keyboard (see Script Authorisation docs https://developers.google.com/apps-script/guides/services/authorization). In terms of Apps Script additional terms these are also available from Google’s website https://developers.google.com/apps-script/terms.
The last couple of sentences are useful guidance “Using or importing any Script or Add-ons is at your own risk. You should only run the script if you trust the developer of the Script or Add-on, as you are solely responsible for any compromise or loss of data that may result from using this Script or Add-on.”
In terms of trustworthiness I would highlight the code is visible to you and with the experiment noted above hopefully it reassures you that once you make a copy of the solution only people with edit access to your copy of the sheet can change the code. This solution has also been published in the G Suite Solutions Gallery https://developers.google.com/gsuite/solutions/mail-merge. With this solution all the code is run on Google servers. Ultimately it is however up to you to decide whether you are happy to use this solution.
Jeremy
Can you give an example of how to use a 2nd recipient column to send emails by cc?
How would the line below from the script be changed if the 2nd recipients for each email were in a column named Recipient2 ?
// cc: ‘a.cc@email.com’,
Martin Hawksey
Hi Jeremy – if the column is called Recipient2 you should be able to change that line to:
cc: row['Recipient2'],
Sean
Hi Martin. I’m trying to include a link in my mail merge.
The easy solution is to put the URL in Google Sheet and include this as a parameter in the draft email, but this results in the email consisting of a long ugly URL.
It would be better and less spammy, to have a parameter which is text with the URL embedded in the text. Is there are way to do that? Perhaps with fomatted cells?
Martin Hawksey
Hi Sean, the way I do it is inline links in the draft and add `{{}}` tokens to the link:
Sean
Awesome thanks Martin, I’ll give that a try.
Louis
Hey Martin, this has been so useful. Thank you very much.
Two things.
1. Could you please share the script for an onEdit/onChange trigger of send emails? i.e. when Recipient column becomes populated
2. Is it possible to set the trigger for multiple sheets?
Really appreciate your help with this.
Louis
Martin Hawksey
Hi Louis – someone asked a similar question. There is a variant you can copy here which has a function to install an onEdit trigger. This variant uses ‘Yes’ in column G to trigger the emails (this version also requires you to specify the Gmail draft template to use in column H – you could hardcode this in the script editor with a minor change). This example also works with multiple sheets in the Google Sheet as long as columns with the headings `Recipient`, `Send Email`, `Email Template` and `Email Sent` are included (doesn’t matter on where they are or what order they are in.
Louis
This is spectacular. Thank you Martin, while(true){{We’re not worthy}}
Nicole Wolfgang
I thought I had it all set, but when trying to send the emails, I get this returned in the “email sent” column:
Cannot read property ‘replace’ of undefined
What am I doing wrong?
Freddie
I am seeing the same error. Any suggestions?
Martin Hawksey
This error would appear if a column was referenced in the email draft that didn’t exisit. I’ve modified the code in the solution. If you take a new copy do you still get this error?
Nicole Wolfgang
Thank you so much! I took the new copy and it worked great. I truly appreciate this script. Hopefully, my students will turn in their overdue work now! 🙂
Michael
I have tried and works perfectly ! Thank you very much!
Michael
I commente too soon, I just put in the draft {{First Name}}, and when test went out it just left a blank,
is there something I am missing?
Nicole Wolfgang
When mine went out I had {{First name}} {{Last name}} and my emails only included the First name. So, I’m not sure what I did wrong there… In your case, is it the capitalization of Name in {{First Name}} maybe? I also used a field called {{grade}} and it did not populate in the email either, but I realized that it may have been because in the spreadsheet it was a capital letter: Grade.
Freddie
The new sheet works perfectly! THANK YOU!
Michael
I got the same message
Philipp Günther
Hi Martin,
thanks for sharing this!
Can I also make the subject line of the mails variable?
Martin Hawksey
Hi Philipp – yes merge values work in the subject line e.g.
Your personal discount code - {{Discount code}}
. When you are prompted for the subject of the draft message use the subject line of your draft message as normal.Francis Guevara
Hi! Thank you so much for this!
I’m trying to attach files from Google Drive to these emails. These files have unique file names.
Is it possible to have a column for their filenames, what should the content of that column be (ex. Folder/Filename.JPG), and do I have to modify the script to make it run?
Thank you again for this!
Martin Hawksey
Hi Francis,
The way I would approach this is two columns one with the name you want to appear as the link text and the other column with the Google Drive filename links. To setup the email draft see the note here further up this thread https://mashe.hawksey.info/2020/04/a-bulk-email-mail-merge-with-gmail-and-google-sheets-solution-evolution-using-v8/#comment-187502
In terms of getting Drive links and file names you’ll have to find another script solution or add-on to help you do that.
Hope that helps
Martin
Rob
Hi – this solution works great! I was wondering if there is an easy way to add in some sort of tracking support or to request read receipts?
Martin Hawksey
Hi Rob, I’m not aware of a way to do read receipts with this solution. I know this is a feature of the Yet Another Mail Merge add-on for Google Sheets and might be a better solution for you
CZ
this is great!!! it’s worked wonderfully for me so far!! thanks!!! and i have been sharing it with my friends also 🙂
though i need a lttle help – i am trying to add a different attachment to each email (i.e. each email has a unique attachment, not all emails having the same attachment)
but i can’t seem to figure out the code for this…and where do i tag + place this attachment.
Francis Guevara
Hi CZ!
This was the problem that I faced a few days ago. I was able to get around it by modifying this solution to fit Martin’s script:
https://stackoverflow.com/questions/48831263/how-to-attach-google-drive-file-with-sendmail-function-in-apps-script
On top of adding this, you’ll have to add a column named “Attachment” and to place all the filenames there. Make sure that all your filenames are unique though or else it’ll retrieve the same file for multiple emails.
Good Luck!
Martin Hawksey
Thanks for sharing Francis 🙂
CZ
Thanks Francis. Let me have a shot at this.
Possible to share a snippet of your script pls? So i could refer to the “modifications” you made? I’m pretty noob at this so need a bit more handholding.
Thanks!!
Francis Guevara
Sure no problem. I didn’t even know what Google Apps Script was a week ago (and I’m never touching it again!).
https://script.google.com/d/1_QBwhTexF6BL6L0rq0AHjNezj5-K8IHslMCOgIASP44n6IWw58G6z-1Y/edit?usp=sharing
We’ve isolated the important part that makes the sending of attachments work. As I said earlier, you’ll have to create a column called “Attachment” for the unique filenames of your attachments.
Further, we added an if statement so that if you want to use this code to send emails without attachments, it’ll still work.
Let me know if you have any other questions, or if you manage to make a more user friendly interface for Scheduling Emails. (Typing the date and time is prone to errors! I wish there was a way to click on a calendar)
Best Wishes!
CZ
Thanks Francis and Martin!! This works like magic.
No I haven’t figured a more user friendly interface for scheduling emails 🙁
I made a little change to the script to remove the .pdf part…so that I can send all sorts of attachments. This means though that within the gsheet “Attachment” column I have to add the file type….but small effort.
Thanks a million guys!!
Hanusha
Hi Martin and Francis,
I tried the original script together with the modification to send individual PDFs, but it did not work.
I got “Invalid argument: attachments” in the column Email Sent.
Can you kindly advise why I might have got this error or can you provide the whole script?
Thanks.
Dan Saleem
Hello,
Would you pleaes let me know how can I also include atachments to my drafted email? I am trying to send the same attachment for every email but when I do the mail merge, it only sends the drafted email without the attachement
Thank you,
Roger
For anyone getting the “Invalid argument: attachements” error, try removing +”.jpg” so that it says DriveApp.getFilesByName(row[“Attachment”]); This worked for me. Thank you!
Sónia
Hi,
I have the email draft structured like this (one field per line):
{{name1}}
{{name1_info}}
{{name2}}
{{name2_info}}
(and so on, for 18 more fields)
Whenever one of this fields/cells is empty in the sheet, I get a blank line in my email. If I have a lot of empty cells, I get a lot of empty lines in my email. Is there a way avoid this from happening?
Thanks in advance
Is there a way to avoid blank lines in the email when the cell
Mohit
Dear Martin,
Could you help merging the schedule send code with full code. I tried making few changes in script as per above suggestion but could to achieve schedule working, all emails were sent immediately.
What i am trying to achieve.
The script should schedule mails based on date and time mentioned in a column , In case its blank send emails immediately. any suggestions?
Martin Hawksey
Hi Mohit – there is a copy of the Sheet example with the scheduling code setup here https://docs.google.com/spreadsheets/d/1oCUxAkKP088J9hiNoLA8NvzUG6FzJh9yG33A0mfTP-o/copy
It doesn’t do exactly what you are looking for in terms of looking for a date column but you could setup the `sendScheduledEmails()` function to automatically keep running with a timed trigger and then modify the `if (row[EMAIL_SENT_COL] == ”){` condition to do a date based calculation e.g.
if (row[EMAIL_SENT_COL] == ” || (row[EMAIL_SENT_COL] !== ” && new Date() >= new Date(row[EMAIL_SENT_COL]))){
Cliff Stockton
Hi Martin,
Thank you for the great script, I have been using the mail merge for some time and it is great. Once a week send like 90 emails and it just works.
But, boy I am struggling to get the scheduled emails to work. I just copied the one above and tried to make it work. But, it just keeps sending them immediately. I feel that I doing some order of operation wrong. Can you walk a newbie through the setup for the triggers. Heck even a short youtube video would be fantastic.
Any help would be appreciated.
Cliff
Martin Hawksey
Hi Cliff – what I think might be happening is your timezone might mean the date/time you use might need to be adjusted (the script defaults to GMT). Here’s a short demo for setting up and checking the scheduled send https://youtu.be/d1mZlXqBFa4 Let me know if you are still having problems 🙂
Nabnub
Hi Martin,
Thanks a lot. Great code that you’re sharing here, while studying your previous code, I was trying to achieve the same goal than this post, which ultimately I’ve found as I visit very often your sites.
For the sake of learning, how can we push the emails so that multiple rows are sent for a single email (i.e. repeated emails receive all rows together instead of receiving one email for each row). Ideally it would be within a table and I don’t know if this table should be ready or not within the draft?
Thank you for any response that you may provide, or perhaps you could include this suggestion in your code, which I believe is very handy in some scenarios.
Jen Perlee
I’m wondering if this is possible as well. I have a list of classes for students and would like to send one e-mail per student w/ a list of their classes.
This would be most useful.
Rajiv B
Tried many different approaches for the images to show up inline including the solution referenced above (and the updated version of the solution) for the YAMM tool. Also tried inserting the images directly, via incognito-vetted URLs, from Photos, from my computer, etc. None of these worked for me. In every case, the emails showed up with broken links for the images in the email body but were included as attachments. Image size is not an issue AFAIK since both images were around 100Kb each.
Would greatly appreciate some assistance.
David
Is there a daily limit?
After around 100, it won’t send anymore. Message is: Service invoked too many times for one day: email.
Martin Hawksey
Yes – there is a 100 limit for consumer @gmail.com accounts. Quotas are higher for G Suite accounts, details are listed here https://developers.google.com/apps-script/guides/services/quotas#current_quotas
Funmi
Thank you so much for this solution! Saved me from having to buy something from the Marketplace. I have two questions:
1) how do i write the code (// name: ‘name of the sender’,) so that the sender name defaults to the user name instead of hard coding a particular name in there? i want other non-tech people in my org to use the script and i want to do that by sending them a “ready to use” file. If they have to go in and change code, that could be an issue.
2) is there a way to adjust the code or my email so it is not perceived as bulk mail by some email servers? I tested the script to go to a gmail account and an outlook account. The email that went to Outlook ended up in Spam folder.
Thanks for your help.
Martin Hawksey
On #1 you could modify the script so it uses a dialog prompt to take setup values from a user like display name. Apps Script has a built-in propertery service you can use https://developers.google.com/apps-script/guides/properties
On #2 nothing that can be added to the script to prevent this happening. My recommendation would be to follow ‘best practices’ guidance for bulk email distribution e.g. https://support.google.com/mail/answer/81126
Andrew
Hi Martin, thanks for your work with the MailMerge script, it’s really helpful and works a treat. I’ve tried the hidden rows variant and this is throwing an error (Sheets not defined). I’m sure this is simple to fix, but I’m not familiar enough with the scripting environment to do this easily. Hope you can have a look.
Thanks, Andrew
Martin Hawksey
Hi Andrew – you might have better luck replacing
if (row[EMAIL_SENT_COL] == ''){
with
if (row[EMAIL_SENT_COL] == '' && !sheet.isRowHiddenByFilter(rowIdx+2)){
Ciara Janay
Thanks for this! This was exactly what I needed. I am a super beginner!! I had to add a menu item and function that allows me to clear the Emails Sent column so that I don’t have to manually delete the data in the column. Is it supposed to delete automatically? I couldn’t figure out if it was supposed to do it on it’s own because mine wasn’t.
Carrie
How can you send the draft email (after mail merge) to Gmail without automatically sending it out? Send through Gmail after review.
Martin Hawksey
Hi Carrie – to only create draft messages you should be able to open the Tools > Script Editor and change
GmailApp.sendEmail
toGmailApp.createDraft
Rick
Hi Martin – thanks for the well documented code and useful examples. Really helpful and a great solution.
Question: Many of our column names contain a space (ie Monthly Usage / Access, Gas Used) etc. The merge works as designed on all fields where the column name is one-word. Is there an escape character I can use in the merge document for these other columns? I’ve tried single quotes such as {{‘Monthly Usage / Access’}}, double quotes, back slash etc. to no avail.
Rick
Rick
As a follow-up and sanity check … the merge does work on multiple word column headings.
Our issue is that we’ve used line breaks (ALT+Enter) within the Column Name cell for formatting (such as the below), and that is why its not being recognized as a merge value.
Monthly
Usage / Access
brian
the script works beautifully – EXCEPT when there are emoji’s in the email
title – and they are changed to question marks. . .
Martin Hawksey
Still an issue of you follow the tip in the section of the post titled ‘Sending emails with emoji/unicode’?
Rick
Set Header Row to Row 2 (or 3) ?
Hi Martin – in the section Sending emails to filtered rows you mention “this assumes the header row is only row 1.”
Is there a variable we can change in the script that would always look for the header on row 2 … or 3? This is not so much for filtering but general functionality; ideally we need to include additional information at the top of the sheet (above the column names) simply for context.
Many thanks –
Doug Gibson
Dear Martin (and Lance, Daniel and Francis for the attachment code).
Thank you so much this!
It has not only solved my problem with much less work that I had expected, but also introduced me to the power of Gsuite apps.
When I use this for real I am going to need to sent out around 500 e-mails. Do you know if the system will cope with this, or will I have to split it up into batches?
Thank you for any advice
Doug
Martin Hawksey
Hi Doug, good question. There are daily maximum sends. For a gmail.com account you are limited to 100/day but if you have a G Suite/Workspace account then 1,500/day or (2,000/day if sending mails within your email domain). More details https://developers.google.com/apps-script/guides/services/quotas#note1
Elizabeth Ov
Dearest Martin or another Gmail Script Guru,
I can see that many solutions have been offered and described here. Unfortunately, my script/coding experience runs as far as Vlookup formulas in excel. To say more, this is the first time I am using Gmail to such a great extent – never really used it before for professional purposes.
So I wondered, if there is a kind person that could, perhaps share with me a Spreadsheet file that includes the attachment column that allows you to attach documents by name and doc type like Name.pdf, Name.docx
I would be deeply grateful for your help.
Presumable columns:
Sender email (if possible, as there are different accounts)
Cc
Bc
Recipient email
Attachment (as in Nme.pdf, Name.txt and ect, taken from drive)
Email status (as in sent or not)
Time sent
Schedule date time
Email template
Thank you again for your time and help.
Khanh
Hello- My email draft has {{First name}} in the body of the email and it matches the column name in the spreadsheet, but when the email went out it left it blank. Is there something I am missing?
Martin Hawksey
Hi Khanh – something that can happen is behind the scenes there is broken formatting. One thing you can try is selecting the text in your draft message and removing formatting
Himanshu
You solved my issue too. I had formatted my mail template to have larger text, so any numbers or links were not being shown in the email. Thanks for this.
Himanshu
Hi Martin,
Your mail merge was exactly the workflow I was looking for. Thanks a ton for your effort.
One question – If the input in a row is a number (say 50) or a URL to a website, then it is not showing up in the mail. How do we get past this?
Thanks in advance.
Kim
How could I include multiple cc addresses based on column data? It seems to only be able to handle one at a time. For example, I declare columns as const in the same area where the other columns are declared, and I have tried:
cc: row[IQ_COL],row[RELOC_COL]
but this creates an error. Sending to one at a time works, but not both.
Martin Hawksey
The quick way to do this is combine the CC addresses in one cell using separating each with a comma. If you have multiple CC addresses in your row a quick way to build this in using the JOIN formula
Paul W
Hi, Thanks for the doing the heavy lifting and creating this! I’m having an issue with the “First name” not pulling into my draft email. I’ve tried several different things to see if I can make it work, but no luck. Any ideas? Thanks again!
Martin Hawksey
Hi Paul – if other tokens are being replaced without issue then sometimes if there is multiple formatting in the same token it isn’t detected. You can test this by clearing formatting on a replacement token (including the {{ }} brackets)
Phil
I’m getting an error when using Tools > Script Editor.
“Sorry, unable to open the file at this time.”
What am I missing? I’m not a coder, but am OK getting in to edit the ‘from’ and ‘reply to’ fields.
Thank you!
Martin Hawksey
Sometimes Google seems to fail to copy the script with the Google Sheet – taking a new copy usually works
Andrew
This is a very timely update. Thanks
Was wondering whether there’s a way to specify the sender as a different user(s) within our Google Workspace domain.nAnd even go further to rotate senders from a list of users.
Martin Hawksey
If the account setup to use this solution can send as other users by setting up aliases then this would be possible. I’m not sure if rotating senders without setup as an aliase send is possible
Sara D
Martin! I’ve tried to run this app several times and keep getting an error message reading “Exception: The starting column of the range is too small.” and none of the emails send. I don’t know what this is referring to – any ideas?
Martin Hawksey
Hi Sara – typically this error happens if the ‘Email Sent’ column name has changed. You can change the column name but you have to also open Tools > Script editor and update the column name on line 24
Janerry M. Espiritu
Good morning Sir!
Kindly guide how to resolve this message:
“Google hasn’t verified this app
The app is requesting access to sensitive info in your Google Account. Until the developer zzz@gmail.com) verifies this app with Google, you shouldn’t use it.
Martin Hawksey
Unfortunately for @gmail.com users there is no way to verify the script. You can still use the solution by clicking the ‘Unsafe’ link at the bottom of the dialog. I’ve included a comment on data privacy/security you can read for more information
Lacey Chase
Thanks for the great Script! I’m really new to this and wondering if there is a way to have the email sent column reset each time you send it without having to manually delete the last date/time?
Any help is appreciated!
Logan
The markers I use don’t work, I’m not sure why. I watched a guide walking through the Google Workspace post and they said to copy and paste the headers into the markers and that finally got the emails to send, but it only worked for recipients. None of the other markers worked. Any idea why?
Logan
Never mind, checked your other comments and removed formatting and it worked. Thanks. I do need a mail merge that works from an Outlook email though, I know this isn’t your area of expertise or anything but do you know of one? Tried using the basic one from Word/Excel and it did not work.
Julie
This has saved me so much time and energy – thank you! I’m now trying to find a way to stop all users of the spreadsheet having access to the mail function. Is that possible? I thought perhaps of prompting for a password when the process is started.
Martin Hawksey
Hi Julie – with the way this solution is setup that’s not entirely straightforward as any of the Sheet editors can edit the code in the script editor. A quick fix is to open Tools > Script editor in your copy and rename the function
onOpen()
to anything else. This will prevent the ‘Mail Merge’ menu appearing but to run the merge you will need to open Tool > Script editor each time and click Run before switch back to your Google Sheet tabLacey Chase
Hi Mark,
I there a way to have the email sent date recorded in a new column each time vs overwriting the same column? We’d like to track each time the customer was emailed vs just the last time.
Thanks!