I have a google sheet that I use to record all the edited images I edit for a client.
Column A - on my sheet - is the image name and looks like.
G9466-02.jpg
G9466.jpg
G10102-02.jpg
G11076-02-2.jpg
The client shares their google sheet with me and their column A ( Stock Number) looks like this
G9466
R8799
G10102
G11076
What I need the Zap to do is when I update my Column A each day with the completed work, that it goes to their sheet’s “Stock Numbers- column A” look to see if anything on my list matches anything on any of their sheets’ column A ( they have multiple sheet in the workbook ( do I need a zap for each sheet?) Then I want it to place a X in the row of that item in the “edited” column F.
My Row headers
Edited file name
Date Completed
Invoiced
Paid
POSTED
Their Row Headers
STOCK Number
DATE
PRICE
DESCRIPTION
PHOTO TAKEN
EDITED
INVENTORY
ON WEB
ON INSTAGRAM
NOTES
X
Best answer by Liz_Roberts
Hi @TheStyleCounsel ,
Thanks for reaching out!
I see that you worked with Tyler in Support on this question. Posting their response below to help other users who may be seeking a similar workflow! Please let us know if you have any additional questions
First, you would want to trigger the Zap with the "New or Updated Spreadsheet Row" trigger for Google Sheets:
That will trigger once for each new or updated row.
Then, because your file names seem to be longer than the numbers on your client's worksheet, you would want to use an "Extract Pattern" action to get just the first part of the filename:
Once we have the right text to search for, we can use a "Lookup Spreadsheet Row" action for Google Sheets. You mentioned that there are several worksheets to look in, so what I would recommend is added one "Lookup Spreadsheet Row" for each worksheet.
Once all of the search actions have searched, we can add a Filter step to see if any of them were successful:
Using the Filter's "OR" condition, you can add multiple conditions, and allow the filter to pass if any of them are met. In this case, that would be like saying "IF the first search returned a value, OR the second one returned a value" and so on.
After the Filter succeeds, you can add an "Update Spreadsheet Row" action, which will go back and update the same row that triggered the Zap, adding an "X" where you need it.
That last step is where you'll have to be careful, as it might trigger the Zap and cause the whole thing to run again. The way to solve for that is to tell the trigger to use a particular "trigger column", so that it only runs when that column is updated:
I see that you worked with Tyler in Support on this question. Posting their response below to help other users who may be seeking a similar workflow! Please let us know if you have any additional questions
First, you would want to trigger the Zap with the "New or Updated Spreadsheet Row" trigger for Google Sheets:
That will trigger once for each new or updated row.
Then, because your file names seem to be longer than the numbers on your client's worksheet, you would want to use an "Extract Pattern" action to get just the first part of the filename:
Once we have the right text to search for, we can use a "Lookup Spreadsheet Row" action for Google Sheets. You mentioned that there are several worksheets to look in, so what I would recommend is added one "Lookup Spreadsheet Row" for each worksheet.
Once all of the search actions have searched, we can add a Filter step to see if any of them were successful:
Using the Filter's "OR" condition, you can add multiple conditions, and allow the filter to pass if any of them are met. In this case, that would be like saying "IF the first search returned a value, OR the second one returned a value" and so on.
After the Filter succeeds, you can add an "Update Spreadsheet Row" action, which will go back and update the same row that triggered the Zap, adding an "X" where you need it.
That last step is where you'll have to be careful, as it might trigger the Zap and cause the whole thing to run again. The way to solve for that is to tell the trigger to use a particular "trigger column", so that it only runs when that column is updated:
