I have two Google Sheets:
-
All Customers – this includes everyone who has signed up through our website, whether they’ve paid or not. Each new entry is automatically marked as FALSE in a “Paid” column.
-
Paid Customers – this includes only those who have completed payment.
I’d like to set up a Zap that compares these two spreadsheets daily. The goal is to:
-
Check the “All Customers” sheet for any entries still marked as FALSE.
-
If the customer appears in the “Paid Customers” sheet, update their “Paid” status in the “All Customers” sheet to TRUE and highlight their row green.
-
Ensure it checks every FALSE entry each day — as some customers delay payment by a few days — so nothing is missed over time.
-
The customers will be compared using email addresses
I’ve tried setting this up using looping and lookup steps but have run into issues, so I’m happy to start fresh with a new Zap if needed.
Thanks in advance for your help!
Hi @dnmgn
A few things to keep in mind here.
- Use the action “Lookup Spreadsheet Row”. This action only gets 500 at a time. You need to set “If multiple search results are found” to “Return all results as line items”
- You can then use the line items to do the looping
I do however think this is more of a Google Apps Script job.
Hi @dnmgn
A few things to keep in mind here.
- Use the action “Lookup Spreadsheet Row”. This action only gets 500 at a time. You need to set “If multiple search results are found” to “Return all results as line items”
- You can then use the line items to do the looping
I do however think this is more of a Google Apps Script job.
My flow was
Schedule
Get many line items
Code - runs a code to but all emails addresses from previous step into an array
Looping - Loops through all the emails
Look up - Looks through other spreadsheets
Update spreadsheet rows - Updates based on lookup outcome
So the issue here is if you ever get more than 1500 customers this wont work because you are not searching.
Not sure why you would need the code step though since they are already in an array as line items?
So the issue here is if you ever get more than 1500 customers this wont work because you are not searching.
Not sure why you would need the code step though since they are already in an array as line items?
I used code because the get many line items was not separating the emails addresses into individual lines and just outputting as one chunk of data. I tried the text splitting function but this didnt work
I got all the way to the lookup section and then the look up was only showing one piece of data despite the loop outputting 49 pieces and I could not get past that
In the testing environment you only get the first item in the loop, it doesnt actually loop on all items. If you think its working you should turn it on and then click Run Zap so it actually runs
In the testing environment you only get the first item in the loop, it doesnt actually loop on all items. If you think its working you should turn it on and then click Run Zap so it actually runs
I have tested fully and it still only looks up 1 looped item
Please share screenshots of the zap in edit mode so we can see what’s going on
Please share screenshots of the zap in edit mode so we can see what’s going on
The first screenshot is the whole flow and the 2nd is the option I get from the look up and this is the data that comes out of the looping step
Loop Iteration
1
Email
Robert@…....com
preview_loop_values
Preview Loop Values No Preview
Preview not available for loops with more than 50 iterations
Loop Id
5287635f-aacc-4532-b08a-15c68cb602e0
Loop Id And Iteration
5287635f-aacc-4532-b08a-15c68cb602e0--0001
Loop Iteration Is Last
false
Loop Iteration Last
61
Loop Total Iterations
61
This post has been edited by a moderator to remove personally identifiable information (PII). Please remember that this is a public forum and avoid sharing sensitive details.
I then click the email section for the lookup and it only looks up that email not any others from the loop
Hi @dnmgn ,
I see where the confusion is. Each Loop is a separate run in the history :) So you are currently seeing loop 1 of 49. If you check the zap run history for the run before or after that, it should have a different value.
Thank you that makes sense
It then runs for some but then hits a few results it cant change and then does this
This Google Sheets step hit an error
The app returned "Unable to parse range: 'Sheet1'!35,36:35,36".
In which step is that? from the data it means that the range is wrong, it looks like its getting 2 ranges instead of 1
In which step is that? from the data it means that the range is wrong, it looks like its getting 2 ranges instead of 1
With the help of Gemini I have managed to put some code in to stop this error but its now not updating the spreadsheet despite it running successfully
Please share screenshots of the Data In and Data Out of each step. Please make sure you remove any personal info from the screenshots.
Please share screenshots of the Data In and Data Out of each step. Please make sure you remove any personal info from the screenshots.
At this point I think it may be best starting fresh
Is it definitely possible to cross check one spreadsheet with another and then highlight the rows green if their email address appears on both? I feel like its a fairly simple task and ive been trying to set it up since Monday and just hitting problem after problem where I have made more complex runs before and not had such issues.
How would you advise the run to look ?
Hi @dnmgn,
Hi there! I understand this can be frustrating, but we're here to help. You can compare two Google Sheets and highlight matching rows using Zapier, though it takes a few steps. Start with a trigger for "New Spreadsheet Row" in your primary sheet. Then, use "Find Many Spreadsheet Rows" to search for matching email addresses in the second sheet. Use a "Formatter" step if needed for data prep, and an "Update Spreadsheet Row" action to mark matches—for example, by changing a cell's value that triggers conditional formatting (like turning a row green). Test everything thoroughly, and turn your Zap on once it works as expected. If you hit any issues, feel free to reach out!
