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

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

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...

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

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?

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! 

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. “

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. 

@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.  

@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?

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!

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...

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. 🙂

@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?

Thank you @MohSwellam, it works fine :)