Hi @blueli!

I think it might be possible to set up a Zap that will do that, but it could get quite complicated.

The simplest way to achieve this would be to add ‘no’ in the Attendance column when people don’t attend. You could then set up a Zap that triggers when a row in the sheet is updated and use a Filter step so that the Zap only continues if the word ‘no’ is in the attendance column. 

Your next Zap actions would be a delay by Zapier step, which you would set to delay the next step of the Zap until a certain time. When you set up that step you can use the date and time they were due to arrive and type a space and then ‘+2h’ after it, which will mean that the Zap waits until 2 hours after they registered to arrive to continue to the next step. You can learn more about adding modifiers to date/time fields in this help guide. 

Would that work for you, to add a ‘no’ to the attendance column? If not, let us know and we’ll see if we can put together the Zap in a different way. 

I see. Let me make it simpler. 

So I have multiple rows that contain a date

e.g. 3/9/2022, 4/9/2022, 5/9/2022
If I want to send a email of today at 9pm. say. today is 3/9/2022
There are 30row col B contain date and time e.g. 3/9/2022 12:00. and with an email in col C. col D with the attendance “No”

How can I use the google sheet find many spreadsheet rows (only 10rows max.) to do the job? or should I use the lookup action..?

Thanks

Hi @blueli 

There are so many ways to go about that. For example: 

1. Create Google Calendar events for those rows (check this link for Google Calendar tips and tricks https://zapier.com/help/doc/google-calendar-tips-tricks). Make sure you add in the Description of the event the Email address so we can look for it in Google Sheets later
2. Create another Zap with the Trigger Event Start in Google Calendar
3. Add a Delay by Zapier for 2 hours after Event Start Date https://zapier.com/help/create/customize/add-delays-to-zaps
4. Add a Google Sheets steep to look for this particular email (which you should see in the description of the event). 
5. Add a Filter by Zapier where Attendance is not “yes” https://zapier.com/help/create/customize/add-conditions-to-zaps-with-filters
6. Add a Gmail step to send the reminder email                                      https://zapier.com/help/doc/how-get-started-gmail-zapier

That being said, I would like to also suggest an out-of-the-box solution :) 

Why not use something like Airtable.com (the free plan works for up to 1500 records so I think it would work in your case)? In such case you can do the following: 

1. You can create a view in Airtable where it filters what you need
2. You can setup a Trigger in Zapier based on Airtable View (i.e.: when a record enters a view it would trigger). You can read more about it here https://zapier.com/help/doc/how-get-started-airtable-zapier (there is also partner documentation available for that, you can find it in the same link). The filter in the view can include the Not Attended as well (not only thee date).

Let us know how this works for you :) 

Hey @blueli! I just wanted to check in here to see if you were able to try out any of @MohSwellam’s suggestions above. Let us know - we’re here to help!

@jesse  Hi I have not yet trying it yet. But seem to be a solid solution.

@MohSwellam  Thank you so much!!