Skip to main content
Question

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.

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

3 replies

JammerS
Forum|alt.badge.img+6
  • Zapier Staff
  • 2203 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
  • New
  • 1 reply
  • 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
  • 2203 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.