Skip to main content
Best answer

Compare each row of Google Sheets with a static date


I have a Zap that creates rows in Google Sheets. Example entries below.

 

Produced Insured Contact Name E-mail address Policy Eff Policy Exp Addtl Insured Certificate Holder File Name
02/02/2024 Gilbert XXXXXXXX Ryan XXXXXXX XXXXXXXX@XXXX.com 02/04/2024 02/04/2025 Y Breven Homes LLC Tough Guys Plumbing_COI_Exp2-2-2025.pdf
03/20/2024 JAZ TRIM INC UNIVERSAL INSURANCE SERVICES XXXXXXXX@XXXXXXXXX.COM 05/14/2023 05/14/2024 Y    
08/23/2024 Lantz's Lakeside Plumbing LLC Jackie XXXX@XXXXXX.com 05/01/2024 05/01/2025 Y Breven Homes LLC Cert - Breven Homes LLC(1).pdf

 

 

I am trying to create another zap that does the following.

Trigger: June 1 of each year

  1. Compare the Policy Exp field in each row to June 1 of the prior year. For example, if it’s June 1, 2025, I want to compare the Policy Exp to June 1, 2024.
  2. If the Policy Exp date is before June 1, 2024, then I want to move the file (file name is in column the far right column) to another folder in Google Drive.
  3. If the Policy Exp is after June 1, 2024, then do nothing.

Here is my Zap thus far.

 

 

I get to the point where I’m comparing the output dates swapped boolean. If it’s true, that means the Policy Exp is AFTER June 1 and I do not want to do anything. If it’s false, I want to move the file to another Google Drive folder.

 

With the current sample entries above, I get this as the output of step 4.

The issue is that I want the Zap to continue for record 2 only (where it’s false). How do I do this? What am I missing? Thank you in advance.

This post has been edited by a moderator to remove personal information. Please remember that this is a public forum and to remove any sensitive information prior to posting.

Best answer by SamBBest answer by SamB

Hi there, ​@burnzie 👋

I agree, using a lookup table and a filter to check multiple dates at once won’t work for your use case here. Filters in Zapier require all conditions to be met simultaneously, so checking multiple dates in a single step won’t give the desired results. Instead, you’ll need to check each date individually using loops. With loops, you’d need to move the Filter and Move File actions inside the loop so the Zap can evaluate each date separately. The Zap will then either move or ignore each file based on whether its expiry date is before June 1 of the prior year. And since Filters can check if a date is before or after a specific date/time, there’s no need to use a Formatter step to compare the dates.

Also, the trigger of your Zaps says Run Every June 1, but there’s no annual trigger option available for Schedule by Zapier. You’d need to use a monthly trigger and follow that up with a filter to only allow the Zap to continue if the month is June. Here’s what I’d suggest based on your current Zap set up:

  • Trigger: Every Month (Schedule by Zapier) - set to trigger on the first of each month.
  • Action: Only continue if... (Filter by Zapier) - set to only continue if the Date Month field (Text) Exactly matches the value 6, which would indicate that the month is June.
  • Action: Get Many Spreadsheet Rows (Advanced) (Google Sheets) - retrieves multiple spreadsheet rows.
  • Action: Create a loop from line items (Looping by Zapier) - takes the spreadsheet rows details that are supplied as line items and creates a separate loop for each row. Check out Loop your Zap actions to learn more about loops.
    • Action (inside loop): Only continue if...  (Filter by Zapier) - uses the (Date/Time) Before condition to check whether the expiry date is 1 year before the current day. You’d need to map the Pretty Date field from the trigger step and use a date modifier to subtract 1 year from the current date (-1y). To account for dates that expire on June 1, 2024 you’d also want to have an OR filter rule that uses the use the (Date/Time) Equals condition to check the two dates match. 
    • Action (inside loop): Find a File (Google Drive) - searches for the file by name.
    • Action (inside loop): Move File (Google Drive) - in the File field, use the custom value option and select the ID supplied by the previous Google Drive action to reference the correct file.

Alternatively, a simpler approach could be to create an expiry date event in a Google Calendar for each file when a new policy is added to the spreadsheet (which uses the filename as the name for the event). Then have a Zap that triggers on the expiry date, searches for the file name and moves it. That way the files are moved on the day that they expire. 

Hope that helps to get you pointed in the right direction. If you run into any issues or questions on that just let us know! 🙂

View original
Did this topic help you find an answer to your question?

7 replies

JammerS
Forum|alt.badge.img+6
  • Zapier Staff
  • 2312 replies
  • February 11, 2025

Hi ​@burnzie,

 

Welcome to the Community.

 

Zapier processes one row at a time, which can cause issues when multiple results are filtered. To handle this, use the "Formatter by Zapier" → Date/Time → "Compare Dates" action to check if the Policy Exp date is before June one of the previous year. If so, output "Move"; otherwise, output "Skip." Then, add a "Filter by Zapier" step to continue only if the output is "Move," ensuring only expired policies are processed. Finally, use the Google Drive "Move File" action to transfer the file to an "Expired Policies" folder. Alternatively, if handling multiple rows, use "Looping by Zapier" to process each row individually before filtering.

 

Let me know if you need further assistance.


  • Author
  • Beginner
  • 3 replies
  • February 11, 2025

Thanks for the quick response, ​@JammerS . I am not sure how to do the “Formatter by Zapier” step. Here is what I have.

How do I do an “IF” statement within this step and have the output be “MOVE” or “SKIP” so I can filter on these outputs in the next step?

 

 


JammerS
Forum|alt.badge.img+6
  • Zapier Staff
  • 2312 replies
  • February 12, 2025

Hi ​@burnzie,

 

To set up an "IF" statement using Zapier's "Formatter" app, use the "Utilities" action and select "Lookup Table." Configure the table to define conditions (for example, if a value is greater than or equal to 50, output "MOVE"; if less, output "SKIP"). After testing the Formatter step, use the output in a subsequent step, like a filter, to determine the following action based on the result (for example, continue only if the output is "MOVE"). This method allows you to create conditional logic within your Zaps.

 

I hope this helps. Let us know if you need further assistance.


  • Author
  • Beginner
  • 3 replies
  • February 18, 2025

Thanks again. I’m not following how a lookup table helps me create conditional logic for two dates that I’m comparing. Is there a more simple way to do this? I’d love this automation but not sure how feasible it is without some custom code.


SamB
Community Manager
Forum|alt.badge.img+11
  • Community Manager
  • 7415 replies
  • Answer
  • February 18, 2025

Hi there, ​@burnzie 👋

I agree, using a lookup table and a filter to check multiple dates at once won’t work for your use case here. Filters in Zapier require all conditions to be met simultaneously, so checking multiple dates in a single step won’t give the desired results. Instead, you’ll need to check each date individually using loops. With loops, you’d need to move the Filter and Move File actions inside the loop so the Zap can evaluate each date separately. The Zap will then either move or ignore each file based on whether its expiry date is before June 1 of the prior year. And since Filters can check if a date is before or after a specific date/time, there’s no need to use a Formatter step to compare the dates.

Also, the trigger of your Zaps says Run Every June 1, but there’s no annual trigger option available for Schedule by Zapier. You’d need to use a monthly trigger and follow that up with a filter to only allow the Zap to continue if the month is June. Here’s what I’d suggest based on your current Zap set up:

  • Trigger: Every Month (Schedule by Zapier) - set to trigger on the first of each month.
  • Action: Only continue if... (Filter by Zapier) - set to only continue if the Date Month field (Text) Exactly matches the value 6, which would indicate that the month is June.
  • Action: Get Many Spreadsheet Rows (Advanced) (Google Sheets) - retrieves multiple spreadsheet rows.
  • Action: Create a loop from line items (Looping by Zapier) - takes the spreadsheet rows details that are supplied as line items and creates a separate loop for each row. Check out Loop your Zap actions to learn more about loops.
    • Action (inside loop): Only continue if...  (Filter by Zapier) - uses the (Date/Time) Before condition to check whether the expiry date is 1 year before the current day. You’d need to map the Pretty Date field from the trigger step and use a date modifier to subtract 1 year from the current date (-1y). To account for dates that expire on June 1, 2024 you’d also want to have an OR filter rule that uses the use the (Date/Time) Equals condition to check the two dates match. 
    • Action (inside loop): Find a File (Google Drive) - searches for the file by name.
    • Action (inside loop): Move File (Google Drive) - in the File field, use the custom value option and select the ID supplied by the previous Google Drive action to reference the correct file.

Alternatively, a simpler approach could be to create an expiry date event in a Google Calendar for each file when a new policy is added to the spreadsheet (which uses the filename as the name for the event). Then have a Zap that triggers on the expiry date, searches for the file name and moves it. That way the files are moved on the day that they expire. 

Hope that helps to get you pointed in the right direction. If you run into any issues or questions on that just let us know! 🙂


  • Author
  • Beginner
  • 3 replies
  • February 23, 2025

This worked. Thank you very much!


SamB
Community Manager
Forum|alt.badge.img+11
  • Community Manager
  • 7415 replies
  • February 24, 2025

YAY! That’s awesome news, ​@burnzie! 🎉🎉🎉

I’m so glad that did the trick. If you need help with anything else do reach back out. In the meantime, happy Zapping! 😁⚡