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 Products
Learn more about Zapier's new products.

Zapier Tables Zapier Interfaces

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.

  • Home
  • Get Help
  • How Do I
  • How do I utilize the Lookup Spreadsheet Row action to find a row in Google Sheets 10 days earlier than the current date?
Best answer

How do I utilize the Lookup Spreadsheet Row action to find a row in Google Sheets 10 days earlier than the current date?

  • 10 October 2022
  • 15 replies
  • 1913 views
B
Userlevel 1

Hi,

I want to use the Lookup Spreadsheet Row action to find rows in a google sheet that has a date more then 10 days earlier than the current date. What should I put in the lookup value field?

 

thanks

icon

Best answer by MohSwellam 26 October 2022, 14:15

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.

15 replies

rachael.silvano
Userlevel 5
Badge +7

Hi @bogi , thanks for coming to the Community with your question! 

 

So, I’ll give you the way I personally would approach this problem, but it may be different with your business needs. Anyways, here is my solution: 

 

I would start by adding a column in GoogleSheet that uses the =TODAY function: 

 

Next, you can use a pretty straightforward formula to calculate in GoogleSheets the difference between todays date and the date you have entered: 

 

Now in your Zap, you’ll want to add a Filter step, which will look like this: 

 

And that’s how I’d approach having a date lookup! I’m sure there are other ways to get to the same info using formulas, but this is the one I know :] 

 

Let me know if that helps get you on the right direction, and I look forward to hearing from you!

Best

Rachael

B
Userlevel 1

OK, so you are saying that I should calculate the value in a separate column and use that as a filter value. Great, thank you.

The purpose of this is that I want to filter my data like above and use email by Zapier to send out emails to recipients. The email addresses are in another column. I want to run this action every day with Schedule by Zapier. As an action I tried to use Lookup Spreadsheet Row in Google Sheets, but I’m not sure this is the correct method...

rachael.silvano
Userlevel 5
Badge +7

Hey @bogi - sounds like you’re on the right track! By using Filter *instead* of Lookup Spreadsheet Row, you are effectively doing the same thing- you’re saying the Zapier “only send emails to these folks”- the next step you’d do would be “if the filter picks up a row that is more than 10 days out, continue with the Zap”. 

 

If you want you can post some screenshots so we can take a look together and make sure you’re all set up! (Heads up if you are, that posting screenshots requires blurring any sensitive information just fyi!).

 

Let me know what you think- Rachael

B
Userlevel 1

I used the setup you can see on the image below. It sends out the emails, filter works, the only problem is that it sends out one email with multiple recipienst. What I want is to send individual emails to each recipient. Similar post here: 

 Do I have to use looping here?

 

 

rachael.silvano
Userlevel 5
Badge +7

Hmmm. That’s a good question. Looping isn’t going to get you where you need to go (I don’t think) but I do see that you aren’t working with the Filter step in your Zap above. That may allow you to set the Trigger to instead update on a row- and delay to set it up to send once a day. 

 

If you want to try that and let me know where you get, we can keep working at getting you set up! 

B
Userlevel 1

I’m sorry, what do you mean by that? “That may allow you to set the Trigger to instead update on a row- and delay to set it up to send once a day. “

MohSwellam
Userlevel 7
Badge +8

Hi @Rachael S , 

 

Why won't Looping work in this case? Isn't the data coming from Google Sheets as Line Items? I think if @bogi  sets the loop on the Send Email By Zapier it would work. 

Founder of GeekyAir.com
rachael.silvano
Userlevel 5
Badge +7

@MohSwellam  - It won’t take much for me to admit I’m wrong, I’m definitely not a Zapier pro by any means. 😅- 

@bogi - I suppose I misspoke earlier, my apologies (being a Community means we live and learn, right?). Sorry for the bad take, and let us know if you run into any other issues.  

B
Userlevel 1

@Rachael S, you don’t have to apologize at all, I’m grateful for your help anyway :)

@MohSwellam are you saying, that I should have a looping action before Email By Zapier, and I should use the loop value in the to: field?

Danvers
Userlevel 7
Badge +12

Hi @bogi!

Yep, that’s exactly it :) 

Add the loop before the email step and then use the email address field in the To Address. Just in case, I want to point out a couple of limitations with Email by Zapier: 

  1. You can only send 10 emails per hour, so if there’s more than 10 email addresses in the list, the Zap will stop.
  2. If one person unsubscribes from that email address, the Zap will no longer work for any email addresses. This might sound harsh, but it’s to prevent the app from being used to send spam. 

If you have access to another email, either an app like gmail or if you have access to your email server, you can send the emails that way using Zapier. 

 

​Let us know if you have any questions!

 

 

B
Userlevel 1

Thank you for your answer @Danvers. It is very important you pointed out that the Zap will stop in case someone unsubscribes. I can’t risk that, so I have to find another solution...

christina.d
Userlevel 7
Badge +9

Hey there, @bogi. Good call out!

If I’m understanding correctly, Danvers caveat for the opt-out only applied to Email by Zapier. So you should be able to use any other email provider and this work as expected. 🙂

B
Userlevel 1

@christina.d thank you for your answer. Yes, this is another solution I will consider. But in the meantime I ran into another problem. Instead of using the Find Many Spreadsheet Row trigger I use the New or Updated Spreadsheet Row and use a trigger column with a checkbox to send out the emails individually. Now as we know the Updated Spreadsheet Row only triggers for new values. If I change the value of the checkbox and then change it back, the trigger will never work again for that row. I made the mistake to put test data in several rows and then I deleted them. Now I can’t use these rows again with actual, valid data, because the checkbox was ones turned on and the trigger won’t recognize these as new value ever again. What to do now?

MohSwellam
Userlevel 7
Badge +8

Hi @bogi , 

 

There is a workaround for that :) 

 

Add all the checkbox value, turn off the zap, remove the checkbox values while the Zap is turned off, turn the zap back on and then click the checkbox again. 

 

Let me know if that works

Founder of GeekyAir.com
B
Userlevel 1

Thank you @MohSwellam, it works fine :)

Terms & Conditions