Let’s say you want to send someone a coupon code when they sign up for your mailing list. You’ve generated a list of coupon codes, downloaded a CSV...but now what? How do you find out if a coupon code has been sent out before? And how do we send a brand new one every time?
Well, with a couple of Google Sheets steps, we can both get the first unused code, and mark it as used so it never comes up again!
The first thing to do is to get those coupon codes into a Google Sheet, and make sure it’s set up properly. We only need two columns: Coupon, and Used. Coupon will track the coupon code, and then Used will track whether or not it has been used before. Make sure to put “no” into the Used column, so it looks like this:
Now that your Sheet is set up, let’s take a look at an outline for the Zap that will use these:
Step 1 Trigger: Anything you like. Maybe someone signs up for a list, or they write into a contact form - it’s up to you!
Step 2 Action: Lookup Spreadsheet Row in Google Sheets. Search for “no” in the Used column, and this will pull up the very first result. It should look like this:
Step 3 Action: Send Coupon Code to customer - you can use whatever system you like here as well. Just use the coupon code from the previous step.
Step 4 Action: Update Spreadsheet Row in Google Sheets. Using the Row ID from Step 2, change the “Used” column to “yes.” This will ensure that this coupon code is never used again. Here’s what that looks like:
So, every time this Zap runs, it will (a) get the first unused coupon code, then (b) mark that coupon code as used. It would look like this after running 3 times:
That way, the next time this runs, it will pull the coupon from Row 5, skipping the first three! You can also use the “Update Spreadsheet Row” step to insert other information if you like, including who you sent it to, their name, the date it was issued, etc. Just add more columns to your sheet, and fill it in with information from your trigger.
And you’re all set! Hopefully this helps. :)
Great solution! I had the same issue and we use WooCommerce. We wanted to create a referral program users could sign up to. My approach was to:
I have just had to do something similar here but I used Airtable and then used digits from the recordID of the customer record in the database
Just used the formula RIGHT(RECORD_ID(),6) to create a 6 digit code straight from the record itself
Seems to work well.
@MarijnVerdult and @Currently Jason!
Thanks for offering your solutions as well. There are lots of ways to tackle this one I like the Airtable one because it can use the record ID from directly in the app, but the code option is great too (especially when you have that example to go by).
FUN FACT: that link to https://zapiertutorials.wordpress.com/, Regan who created that site/tutorial, ended up getting hired at Zapier 1 week after publishing that
I’m working with SendinBlue and sheets..Step 3 above…..how do I ‘just use the coupon code’ from the Google Sheet and send it in a new transactional email? How to insert it? Thanks!
You would use the SendinBlue action Send Transactional Email. In the body of the email you can add information from previous steps of the Zap - in this case, you would add the coupon code from Google Sheets.
You can learn more about building Zaps in this guide: How to Create a Zap - getting started
If you need any more help, you can either ask a question in the Community or contact the Zapier Support Team using the Get Help form
Would it also be possible to search horizontally on a row, instead of vertical (in a column)?
I’m looking for a way to automatically scan a row and replace the first cell on that row that matches my criteria. For example, in order to put a name in the first empty cell on that particular row
I’m working with Mailerlite and would like this ZAP to start with a “SUBMIT FORM” option (but Mailerlite in Zapier only shows Triggered Automation or Added to a Group etc) but can work with the “triggered automation” process...BUT how do I ‘just use the coupon code’ from the Google Sheet and send it in a FIRST transactional/automated email? How to insert it?