How to create a Zap in Zapier to skip used gift card rows from Google Sheets?
Problem: I’m trying to create a Zap that looks up a gift card URL from a Google Sheet where:
The Denomination matches a value (e.g., 25).
The Recipient Name column does NOT already say “USED” (i.e., it’s unused).
Here’s the workflow I want:
Lookup a row where the Denomination matches.
If the Recipient Name column says “USED,” skip that row and lookup the next one that matches.
Once a valid row is found, update it to mark it as “USED” and use the URL in a later step.
What I’ve Tried:
Using the Lookup Spreadsheet Row action in Zapier and specifying both the Denomination and Recipient Name conditions.
Problem: The Lookup Step doesn’t allow a blank or conditional “Recipient Name” value.
Adding a Formatter by Zapier step to pre-process data.
This just paused my zap completely.
Current Behavior:
My Zap fails to continue.
Goal: I need Zapier to dynamically find the next available row where:
The Denomination matches (e.g., 25).
The Recipient Name column is blank
I need to update one spreadsheet to mark a URL as “USED” and a different spreadsheet as “SENT BY ZAP”
How can I configure Zapier to skip rows that are already used and find the correct one? Also, the current set up for the SENT BY ZAP update is part of an update spreadsheet row that works in the test environment but not in the zap itself. I’m stuck!
Any help or guidance would be greatly appreciated!
Page 1 / 1
Hi @emilyayley
An alternative would be to use one of these apps which have more search capabilities:
Help us have more info by posting these screenshots:
the top 3 rows of your GSheet worksheet being used
how your Zap steps are outlined and configured in EDIT mode
Zap action: GSheets - Lookup Row
Supports 2 lookup columns.
Make sure there is a column: USED = TRUE/FALSE
@emilyayley
I found this an interesting puzzle and I am convinced it is possible to get what you want. However, for me it took a little programming in my beloved python to finally solve.
@Troy Tessalone is correct in his suggestion on using a true or false value rather than a blank value and Used. This makes the filtering easier as any null or blank value tends to stop zaps in their tracks. It is also quite hard to find an empty value as they general get cleansed. Most of my zap failures in the past have come from null or empty values stopping zaps.
The way I solved your issue was by getting multiple rows from the google sheet. This will allow you to grab up to 500 rows at a time from your google sheet which seems plenty. Personally, I would sort the sheet to place the unused gift cards at the top or bottom just to make life easier.
My configuration was like this
From the output of this I read the raw row string into python. I would post the code for you but have found when I do so the post goes into a blackhole of moderation. I am happy to DM you with an explanation on how it works or to modify it to fit your needs if you wish.
I found if I took the raw row string it contained the empty data from columns not being looked at and the Recipient Name in need of checking, especially when empty.
What the Code Does:
The code takes a string containing rows of data and converts it into a Python list.
Each row in the list is converted into a dictionary with specific labels (like "Recipient Name" or "Denominator").
The code then looks through the data to find the first row where:
"Recipient Name" is not "Used".
"Denominator" is equal to "25".
If it finds such a row, it adds the row's number and returns it. If no match is found, it says so.
How It Works:
Step 1: Converts the input string into usable data.
Step 2: Labels each piece of information in the rows with meaningful names.
Step 3: Searches for the first row meeting specific conditions.
Step 4: Outputs the row and its number or an error if no row matches.
You may be able to replicate this using conditions in filters although I haven’t tried. My difficulty was I could not work out how to stop after finding a successful case. If I didn’t loop I wasn’t sure it would go through all the rows or stop on the first positive/negative case or continue to find additional successful ones. May be someone more knowledgeable can tell you how to set-up or break the loop.
To update the the sheet when done I added an update spreadsheet row and used the same row number I had taken from the python code. Then just added the new data into the necessary column.
I was satisfied I’d solved it here. .
Alternative Workaround. There is a workaround where you maintain a google sheet purely for the unused gift vouchers and as they are used you clear the row in the google sheet. You can take the last unused from the sheet and remove its row when done.
In parallel you could have another sheet in the workbook that you match up and update with used.
Giving each a unique ID number would help you to match by. You would then have a sheet of unused codes and a sheet telling you which codes have been used or not and if they have been sent by zap.
This would be a lot easier to set-up as it would only be a few steps.
I hope this useful and gives you somewhere to start.
