When connecting to a Google Sheets spreadsheet to send a mail merge campaign, you can now specify filter criteria to pull only certain email addresses that match the criteria.
You’ll notice a new Filter Rows box when connecting to a spreadsheet.
This post explains how to use the Filter Rows box and what to type to send email to just the rows that match your criteria. Specify one criteria per line, in the format:
ColumnName=Value
For example, if you have a spreadsheet column called Company, and you want to send a mail merge campaign to just everyone who’s Company is “Microsoft”, you would enter:
Company=Microsoft
Instead of the = sign, you can instead use the ~ operator to represent “contains”. For example, let’s say that your email addresses are in a column called Email. You want to send to only @yahoo.com addresses. You would enter:
Email~yahoo
meaning all rows where the Email value contains “yahoo”.
Full List of Operators
Along with = and ~ you can also use these operators:
!= for "not equals" > for "greater than" >= for "greater than or equal to" < for "less than" <= for "less than or equal to" ~ for "contains" = for "equals"
Examples
You can use the operators above to compare strings, numbers, and dates. For example, if you have a spreadsheet column called Age, and you wanted to email just the adults in the spreadsheet, you could set:
Age >= 18
Or, let’s say you didn’t have an Age column but instead had a DateOfBirth column. Then you could set:
DateOfBirth <= 1/1/1999
assuming that anyone born before 1/1/1999 is an adult.
Special Values
There are two special values you can use to represent date values. These are:
CurrentDate CurrentDateIgnoreYear
You can use these values to compare the data in your spreadsheet to the current system date (in the GMT time zone). For example, if you have a column in your spreadsheet called ShipDate, which represents when a customer’s order will be shipped, you could set:
ShipDate=CurrentDate
to pull just the rows where the order’s shipping date is today to let the customer know that their order has been shipped and will arrive soon.
Using CurrentDateIgnoreYear matches just the Month and Day parts of the date to the current Month and Day. For example, if you have a column called Birthday and you want to send an email to people only on their birthdays, but birthdays include the year the person was born, then this will be useful. If three of your birthday values are: 1/1/72, 4/5/80, and 3/1/90, then using “Birthday=CurrentDate” would never match the rows because the rows contain the birth year. Using “Birthday=CurrentDateIgnoreYear” however would match rows on a person’s birthday. Also, see the detailed guide to sending birthday emails with Gmail and GMass.
Multiple Criteria
You can also specify multiple criteria. Let’s say your spreadsheet has the columns Company and Position. Let’s say you want to send to everyone whose Company=Microsoft, and Position=Manager. You would enter:
Company=Microsoft
Position=Manager
Or, let’s say that in your actual spreadsheet, the Position column had values like “Product Manager” and “Technical Support Manager”, but you still wanted to email everyone at Microsoft that was some type of manager. In that case you would set the Position criteria to just “contain” the word “manager”. So:
Company=Microsoft
Position~Manager
In these cases, you want rows that match both criteria. So in these cases, the boolean operator should be set to AND. You might, however, want to switch to OR in certain cases. Let’s say your spreadsheet has all of your customers but you want to send a campaign to only customers with an email address at a consumer domain, like hotmail.com, yahoo.com, aol.com, and gmail.com. You would enter:
Email~gmail.com
Email~yahoo.com
Email~hotmail.com
Email~aol.com
And you would set the boolean dropdown to OR. Meaning you want to send to everyone where Email contains gmail.com OR Email contains yahoo.com OR Email contains hotmail.com OR Email contains aol.com.
As another example, let’s go back to our spreadsheet containing the Company column. You’re sending a mail merge campaign to executives at billion-dollar tech companies, so you want to only send to people where Company is either Microsoft, Apple, or Facebook. You would enter:
Company=Microsoft
Company=Apple
Company=Facebook
and set the boolean dropdown to OR. If you entered this criteria and set the boolean dropdown to AND, you would get an error saying that no rows could be selected, since there isn’t a single row where the Company is equal to all three of those values, as that would be impossible!
Send incredible emails & automations and avoid the spam folder — all in one powerful but easy-to-learn tool
TRY GMASS FOR FREE
Download Chrome extension - 30 second install!
No credit card required
Ajay is the founder of GMass and has been developing email sending software for 20 years.
I've got an error after selecting the google sheet (no optional settings has been added) :
"GMass: No recipients found for the specified Filter."
Please help
I can see the optional settings. Will test and feedback. Thanks for this new feature.
The same things is happening to me, preventing me from being able to use GMass.
This happens if your spreadsheet isn't in the right format. You must have column headings in the first row and the data must start on the second row. In the past, it was more flexible with misformatted spreadsheets, but with this new feature, spreadsheets do have to be in the right format. More info on how to make sure your spreadsheet is in the right format: http://blog.gmass.co/2015/10/mass-email-mail-merge-google-sheets.html
I believe I've already helped you via email, but the answer to this is:
This happens if your spreadsheet isn't in the right format. You must have column headings in the first row and the data must start on the second row. In the past, it was more flexible with misformatted spreadsheets, but with this new feature, spreadsheets do have to be in the right format. More info on how to make sure your spreadsheet is in the right format: http://blog.gmass.co/2015/10/mass-email-mail-merge-google-sheets.html
Hi Ajay. I have emailed you and support asking to please help me with branded tracking domain. Could you please take a look ? Thank you Greg
Hi, I want to filter by a column with null value.
I have tried
col1 = 0
and
col1 = null
but neither 0 or null worked.
How do I filter by empty value?
I got
"GMass: No recipients found for the specified Filter."
thank you
Dennis
+1
Have you tried col1 = “”
Hi,
I have a spread sheet which has multiple columns with email ids of people playing different roles in the given organisation. For eg, one column has CEO Contacts, one has fundraising contacts etc. Each row is one organisation. Is it possible for me to send only to a given column of ids?
Any chance to amend this to allow for exclusions? I.e., send this email to everyone in the sheet, EXCEPT those with the company name "Microsoft". I would think this would look like this:
Company<>Microsoft
However, this does not work.
Even more advanced would be "not containing Microsoft":
Company<>~Microsoft
This will be a nifty feature. 1 vote for that.
+1
Just added this! Please see the updates to this post.
This is awesome, but I still don’t see any updates to this post that specify how to make a criterion to send to rows that do not contain a specified entry. The closest thing I see is a way to set the filter to not equal.
Ajay where is it?
Ajay, the “not contains” is not on the list.
Is it !~ or ?
Thanks for the answer.
Raf
“not contains” is not on the list – “!~” seems not to work?
Love your tool!
Hi ! Any news about the “not contains” operator ?
something like !~ as others wrote above
it’s mandatory !
there are so many cases we need that
thanks. Gmass is awesome
Is there a way to filter on empty cells ?
You can fill the empty cells with any value and set the filter on that value, but you cannot filter on empty cells.
Hello, I want to work with your program, but I would like a tutorial how it works
Hey Lee – check out our Beginners’ Resources: https://www.gmass.co/blog/resources-for-gmass-beginners/
Shoot us an email at [email protected] with any questions that come up.
I’m having trouble with the Date Column=CurrentDate filter when setting this up and it is saying “string was not recognized as a valid date time” It works if I say Date=09/21/2017, but it needs to move automatically with our year.
Me too – same problem: it is saying “string was not recognized as a valid date time”
I have data formatted as
1| DateScheduled
———————————-
2| 16/10/2017
3| 16/10/2017
etc
And am using:
DateScheduled=CurrentDate
Was this ever resolved? I have the same problem. Specifically when I use greater than or less than.
I’m attempting to limit the number of records that are selected for merge. How can I select a range of rows to merge?
+1
You can add a new numbered column (in this example the header is named Row) as a guide that GMass can use, then use a filter
Row>1000
Row<2000
This doesn’t work still. Tried ‘Row>500’ and gives this error “GMass: System.FormatException: Input string was not in a correct format”. I look forward to your fix!
Hi Mostafa,
Please contact our support team through http://gmass.co/g/support with a screenshot of your spreadsheet. Please include the header row as well as a few rows down.
Me too – same problem: it is saying “string was not recognized as a valid date time”
Will you please email us a screenshot of the filter data you’re entering? [email protected]
I would like to filter based on only sending to a school with that has the name A – N. Is this possible. If I try sending to all of them, then I will go over my email limits. My filter will look like this
Div=I-AA and
(this is where I need help.)
Same problem as the others when I try to filter a > or or <.
Not sure where the rest of my comment went. Anyway, I’m getting the “string was not recognized as a valid date time” error with dates.
When using a date-based filter, and using the > or < signs, EVERY row has to contain a valid date. If you have blanks, that's where an error can be thrown, because GMass doesn't know how to compute the date's value of a blank cell.
Hi, I have 2 columns in my Google Sheets with email addresses (column 1 and 2). Is there any way I can select a specific column for the automated email to send the addresses to i.e. column 2 instead of 1.
Thank you.
I am also trying to accomplish what Zhhan is describing. Any insight would be very helpful!
I have problem to put my file from load content. I did not find it. How to resolve it.
Hi Irwan,
Load Content is intended to be used when you want to reuse the email body of a past campaign.
Thank you all @GMASS for your great support. I was wondering if there was a way to concatenate 2 operators to work together in same line. Case: I have a list of 1000 Rows and i will like GMASS to import “Rows>500 but Rows<701" (Meaning: Import rows 501 – 700) thereby selecting only 200 Emails from the spreadsheet. Thanks in advance for your help!
Ignore pls! AND solves this fine thanks
Hi Mostafa,
Glad you got it sorted out!
Is there a way to use these filters as a suppression feature?
For example:
I DO NOT want it to send an email to someone with “yahoo.com” in the domain.
Yes, please see https://www.gmass.co/blog/use-a-google-sheets-spreadsheet-as-a-dynamic-email-suppression-list/.
If you wish to suppress emails for an entire domain, please see https://www.gmass.co/blog/unsubscribe-a-domain/
I keep getting this message: GMass: System.Exception: There are duplicate columns in the given worksheet: Sheet1
Can you help?
Hi Lisa,
The reason for this message is you have the same column heading for 2 or more columns. Please check your column headers to ensure you’re only using each one once across all the columns in your sheet.
What if I want to do a merge sequentially for specified rows of the spreadsheet? For example, Rows 2-250 today, and 251-500 tomorrow, etc. And what if I want to automate these merge/sends for a week at at time? Is that possible in GMass?
Hi Glenda,
Please contact our support team at https://www.gmass.co/blog/how-to-get-support-from-the-gmass-team/ so we can address you directly.
I want to send based on dates eg Event Dates between 24 June 2019 and 30 June 2019. How do I code for that?
Hi Tracey,
Please see the detailed guide found here: https://www.gmass.co/blog/gmail-birthday-emails-automatically/
Are compound boolean statements supported?
e.g.:
(Col1=”true” OR Col2=”true” OR Col3=”true”)
AND
(Col4=”false” AND Col5=”false”)
If not, is there a work-around?
I’m also interested in such stuff !
how to combine both OR and AND boolean strategy in a single filter ?
thanks for your time and this awesome tool Gmass
I’d like to send to “Region” 2, and “Outcome” 1 AND 2. Is there a way to do this without having to do two separate mailings, one for “Region” 2 and “Outcome” 1, and one for “Region” 2 and “Outcome” 2?
Hi Jessica,
You can by filtering your Sheet first to show only Region 2 then copying it to a different tab in your Google Sheet. You can then set your filter criteria to Outcome=1 OR Outcome=2 to create a single campaign for those in Region 2 that’s either Outcome 1 or Outcome 2.
How do you indicate you’d like to filter a column whose header has a space or multiple lines or text in the cell?
“Current Status”=Contacted doesn’t work, neither does {Current Status}.
Please help!
Hello All, I have used the “named ranges” to name a column “closed”, I then marked each row with a “y” or a “n” in that column.
For the filter, I entered closed=n to email only customers who didn’t choose my company or didn’t let us know either way.
When I click through and get to the new email generated by GMass it shows 0 recipients. If I do not use the filter, I get 65 recipients.. what am I doing wrong?
TIA
PS: I have the standard plan. Don’t know if that matters or not..?
Solved: Just put the column name in row 1, no need to name the range… First Name, Last Name, Email Address, etc.
Row 2 begins the actual data.
4 years later and counting. ♂️⏱
Will GMass ever get the ability to mix “AND” and “OR” filters and do the basic “does not contain” filter that Word/Outlook mail merges have had forever?
how to choose specific row in mail merge?
eg. row 50 to 100?
Hi
I need some help in google sheets & Gmass
I need The Row called Timestamp Row to be filtered with out BoldText
so only normal text emails will be selected as recipients
Thank you
How do you set a blank. E g. Sending a followup to someone who hasn’t opened – OPENED=???
Hi
Could we have “Does not include”
Is there an operator for isblank, does not equal, or does not contain?
I have 200+ entries in a single sheet. I am currently using the demo version. Please guide which formula can be put to to select only 50 entries.
Hi Shilpi,
You would need to break your Sheet down to multiple worksheets that contain no more than 50 emails and send them manually each day.
Has there been a resolution for the filter column in GMass regarding only returning records that have a null value?
Like SentDate =”” or
SentDate = Null
I do not know why but neither example works,
I want to filter based on a date. I have the dates in US format, but keep getting this response:
GMass: System.FormatException: String was not recognized as a valid DateTime.
What’s wrong?
I am also trying this. One comment earlier says that every cell needs a valid date, but one time I was able to filter date with some cells blank, but this time I cannot. Not sure what is up. I get the same message
I am trying to send a gmass with a filter based on date. I am using date > the last date I sent gmass
Each cell has a valid date in it. Last time I did this it worked without needing a valid date in all of them.
I cannot get this to work now.