Best answer

Schedule daily lookup of Google Sheets column to check if date matches TODAY's date, then send email

  • 4 June 2021
  • 5 replies
  • 1858 views

Userlevel 1

Hi!

I have a Google Sheet with details in each row such as email and date of booking.

I am trying to run a daily schedule to check through a Google Sheet’s column which has the respective date of booking. If it matches today’s date, it would trigger emails to different parties based on the email in each row’s email address. I tried for many hours troubleshooting and reading different Zapier threads but to no avail.

Was wondering if there are any alternative ways of getting this done? My efforts on this were:

  1. I know this issue has been addressed in another thread (link below). This method works with my file open and as long as the TRUE boolean is triggered on a new untainted row/cell, it works. By untainted, I mean when the cell changes from FALSE to TRUE once, it works. If it switches back to FALSE then TRUE again, it won’t get recognised. If I want to have that row “cleansed” and trigger again, I need to recreate a new ZAP. Took me some time to figure that out.

 

  1. Would like to have the trigger without having to opening the Google Sheets every day. I know the way to supposedly address this is to Google Sheets > File > Spreadsheet Settings > Calculation > On Change and Every Minute. However, it doesn’t seem to work for me.

Am not sure if it’s not working well because:

  1. I use IMPORTRANGE for the data (originally in another sheet) in a separate sheet, so I can formularise in this new IMPORTRANGE sheet on the right columns =if(DateCell=TODAY(),True,False) to get a TRUE/FALSE answer if today matches the date in DateCell. I use IMPORTRANGE and structured the sheet as such because my original sheet’s data comes from WooCommerce - each time a new order comes in, a new row with details are populated in Google Sheets. In order to automatically generate the aforementioned IF formula automatically to get TRUE/FALSE, I have to use IMPORTRANGE so I don't have to drag rows downwards each time a new Woocommerce order comes in.

Thanks alot in advanced! Tried for more than 5 hours but can’t seem to get it right.

icon

Best answer by Troy Tessalone 5 June 2021, 01:50

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

5 replies

Userlevel 7
Badge +14

Hi @zhhan

Consider using Airtable instead of GSheets.

Airtable has Views: https://support.airtable.com/hc/en-us/articles/202624989-Guide-to-views

Views can have filter conditions such as Date = Today.

When records enter Views that can trigger Zaps: https://zapier.com/apps/airtable/integrations#triggers-and-actions

Userlevel 1

Hi @zhhan

Consider using Airtable instead of GSheets.

Airtable has Views: https://support.airtable.com/hc/en-us/articles/202624989-Guide-to-views

Views can have filter conditions such as Date = Today.

When records enter Views that can trigger Zaps: https://zapier.com/apps/airtable/integrations#triggers-and-actions

Thanks for this! I suppose the principle is to isolate in a separate sheet or column with those specific rows of data that exclusively appear only when filter conditions are met > Date = Today.

I think I may be able to continue using Google Sheets but utilise the FILTER(X,column=today()) to draw out only rows that match the criteria. 

Thanks so much for this! Am trying not to move to Airtable as I have built Google Sheets quite extensively and the entire workflow is on it. As such, moving migrating it to Airtable could be quite a hassle, though I can probably integrate Google Sheets and Airtable using Zapier so new data flows in there (but takes up tasks limit). Will give this a shot!

If there are more alternatives from others, do keep them coming!

Thanks Troy! Appreciate it!

Userlevel 1

I managed to formularise the Google Sheet with IMPORTRANGE such that only rows those with Date=Today appear as per screenshot below. Believe I’m almost there.

But the problem is this. Each time a new row from the original sheet is create, the IMPORTRANGE seems to work perfectly well. But Zapier can’t seem to trigger actions. I have to manually go into the sheet with the IMPORTRANGE and type something into the next row before it triggers. 

I have already set Google Sheet to be “On change and every minute”. Not sure where I may have gone wrong.

Also included the screenshot below on the triggers and action I used in my Zap.

 

 

Userlevel 7
Badge +14

Hi @zhhan 

Couple things to note…
GSheets help articles: https://zapier.com/apps/google-sheets/help
(All the more reason to consider Airtable instead of GSheets)

  1. GSheets need to be configured in a certain way in order to work properly with Zapier
    1. Article: https://zapier.com/help/doc/how-to-set-up-your-google-spreadsheet-to-work-with-zapier
      1. The first row must have titles for any column headers you want to be able to see in your Zap.
      2. The second row must have content for any columns you want to be able to see in your Zap.
      3. There can be no completely blank rows before the end of your spreadsheet.

 

  1. GSheet triggers are NOT instant...

The Google Sheets trigger is marked "instant" but it still takes a few minutes to trigger

The triggers for Google Sheets are unique among Zapier triggers.
When there is a trigger event in the spreadsheet, Zapier gets a notification webhook from Google about this.
After that, Zapier sends Google Sheets a request for new data, so it uses both the polling and instant trigger methods.
This process takes about 3 minutes overall.

While not being "instant", these triggers are faster than regular polling ones, as they don't depend on the polling interval of the plan your account uses.

 

  1. The "Updated Spreadsheet Row" trigger stopped triggering

    If you're watching for changes to a column, we only see new values in that column. If you update the column of an existing row to a value that Zapier previously saw in that column, in that row, we won't see the new value. You will need to either choose a column that will only have unique values, or make a new one which will.

    For example, if you had X in the Trigger column, then changed it to Y, the Zap would trigger. If you then changed it back to X it would not trigger again, because the Zap has already seen X in that column.

Userlevel 1

Hi @zhhan 

Couple things to note…
GSheets help articles: https://zapier.com/apps/google-sheets/help
(All the more reason to consider Airtable instead of GSheets)

  1. GSheets need to be configured in a certain way in order to work properly with Zapier
    1. Article: https://zapier.com/help/doc/how-to-set-up-your-google-spreadsheet-to-work-with-zapier
      1. The first row must have titles for any column headers you want to be able to see in your Zap.
      2. The second row must have content for any columns you want to be able to see in your Zap.
      3. There can be no completely blank rows before the end of your spreadsheet.

 

  1. GSheet triggers are NOT instant...

The Google Sheets trigger is marked "instant" but it still takes a few minutes to trigger

The triggers for Google Sheets are unique among Zapier triggers.
When there is a trigger event in the spreadsheet, Zapier gets a notification webhook from Google about this.
After that, Zapier sends Google Sheets a request for new data, so it uses both the polling and instant trigger methods.
This process takes about 3 minutes overall.

While not being "instant", these triggers are faster than regular polling ones, as they don't depend on the polling interval of the plan your account uses.

 

  1. The "Updated Spreadsheet Row" trigger stopped triggering

    If you're watching for changes to a column, we only see new values in that column. If you update the column of an existing row to a value that Zapier previously saw in that column, in that row, we won't see the new value. You will need to either choose a column that will only have unique values, or make a new one which will.

    For example, if you had X in the Trigger column, then changed it to Y, the Zap would trigger. If you then changed it back to X it would not trigger again, because the Zap has already seen X in that column.

Thanks Troy! I switched to Airtable instead and it worked perfectly for what I wanted to achieve. Google Sheets probably is possible but there's alot of pain involved in getting it to work. 

 

Thanks so much!