Hi @danielle879235 

Some options:

• Use Airtable instead of GSheets
  • Airtable has Date field types
  • Airtable has Views which are segments of data (sort, filter, group, show/hide fields, reorder fields)
    • e.g. Date is 2 days from now
  • Airtable Views can be used to trigger automations
    • Zaps
    • Airtable has native Automations
• Use this Zap trigger: Schedule - Daily (at specified time)
  • Action: Formatter > Date & Time > Add/Subtract Time
    • -2 days
    • Help:

       

  • Action: GSheets Lookup Rows
    • Search by date output from Formatter step
  • Action: Filter
    • Make sure there are rows to process
  • Action: nAPP] - EVENT]
    • Up to you!

Hi @danielle879235,
 
We just wanted to see how everything is going with your Zap. Did Troy's recommendation get the job done? Feel free to reach out if you need further assistance with your Zap. We're glad to address any concerns and assist you.
 
We're looking forward to your response.

Hi… Sort of. Stuck on the formatter/lookup steps

Step 1: new or updated ss row. Question: (trigger column)- is end date the correct column?

• In new or updated sheet, (first step), trigger column is “end date” with sample data showing end date of 7/25/2024… 

Step 2: formatter

• Transform (add/subtract time),
• input (blank),
• Expression (+8 days),
• to format (MM/DD/YY).

Step 3: Lookup

• lookup column: end date,

• lookup value: output from formatter

• supporting lookup column: end date

• bottom up: blank

• success: no

Questions:

1. Step 1 (trigger column)- is end date the correct column?
2. Step 2: Expression. I want an email 2 days in advance. When i did + or - 2 days, it was a weird output. By making it +8 days is when i would essentially get output of 7/23/2024 during testing. Why is 8 days?
3. Step 3: Getting error message and getting this error: 

• Failed to find a spreadsheet row in Google Sheets

  • Error from halted execution: Nothing could be found for the search.

  • Click on the Troubleshoot tab below or learn more in our help center.

@danielle879235 

For us to have more context, post screenshots with how your Zap steps are outlined and configured in EDIT mode.

Thank you. See screenshots

@danielle879235 

Did you try this approach?

Steps

1. Trigger: Schedule - Daily
   1. Specify the time
2. Action: Formatter > Date & Time > Add/Subtract Time
   1. -2d
3. Action: GSheets Lookup Rows
   1. Search by date output from Formatter step
4. Action: Filter
   1. Make sure there are rows to process
5. Action: Gmail - Send Email

Amazing!! It worked… Took a bit of trial and error on the formatting step, but so happy it works! Almost...

Here is how the subject looks now:

1 day trip approaching (TEST-125,TEST-124,TEST-123)

I want it to filter only for data that is 1 day Trip in column A. It still populated all.. Am i missing something in the filter step? Attached screenshots. TEST-125 shouldnt have returned if it was filtered correctly. 

i have it to look for multiple rows from bottom up (20 rows). Since 3 (really only 2) match the criteria (screenshot), it sent all 3 routes in 1 email.. I’d like it to be in separate emails. Is there a way to allow for that?

ALso, not sure why, but i want to be able to populate driver name (column M) in the action email, BUT i dont see that as an option to click (rows only gave me up to  column F- see screenshot)

@danielle879235 

Make sure every GSheet Column has a header in row 1. (e.g. Row D is missing)

You’ll need to add these steps after the Filter step:

• Action: Looping - Create Loops from Line Items
• Action: Filter
  • Use the conditions below

Thank you. I found the ability to add driver column as a field for the email

BUT, for the looping step, im still confused. The only option to use the conditions above is attached in the screenshot and pulling data from the Gsheets lookup rows-- not from the filter step.

See attached ss if im doing it right 

@danielle879235 

Step 4 should move after the Looping step and check for variables OUTPUT from the Looping step.

A Filter step should be added after the GSheet step to make sure there are rows returned to process via the Looping step otherwise the Looping step will encounter an error.

Help link for the Looping app: https://zapier.com/apps/looping/help