Comparing Google Sheets and highlighting the same data

Hi ​@dnmgn 

A few things to keep in mind here.

1. 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”
2. 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? 

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

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 

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

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 

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.

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!