About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Tools Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

Best answer

Updating one google sheet based on content in another.

  • 9 April 2020
  • 2 replies
  • 739 views
T

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                                  
icon

Best answer by Liz_Roberts 10 April 2020, 18:03

View original
This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

2 replies

Liz_Roberts
Userlevel 7
Badge +8

 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 :relaxed: 

First, you would want to trigger the Zap with the "New or Updated Spreadsheet Row" trigger for Google Sheets:

https://zapier.com/apps/google-sheets/integrations#triggers-and-actions

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:

https://zapier.com/help/create/format/find-text-with-regex-in-a-formatter-step

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:

https://zapier.com/help/create/customize/add-conditions-to-zaps-with-filters

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:

https://zapier.com/apps/google-sheets/help#updated-row-trigger
 

T

Thank you, however, I am still lost at the 3rd step- Look UP row.   I keep getting Bargle Nothing found.

 

Terms & Conditions