Question

Send Gmail e-mails based on a specific date and time set in Google Sheets

  • 23 November 2022
  • 7 replies
  • 39 views

Hello! I use Google Sheets and Gmail in order to send automated emails based on the “new row” inserted in the Google Sheets file. It works perfectly. 

 

Now, I have to set a challenger step: to send automated emails based on a specific date and time inserted in a Google Sheets column

 

Here the column headers I set (you see in the last column the date and time desired for the sending of the email):

 

I read some topics in this community. So I set these steps: 

  1. Every Hour In Schedule by Zapier
  1. Lookup Spreadsheet Rows (output as Line Item) in Google Sheets 
  2. Send Email in Gmail

 

 

But there are some problems. 

When I set the trigger, the test outputs this automated values: 
 

 

and when I set the Lookup Value it returns only data from selectable fields of the Trigger and not of the Google Sheets Column. 

 

In fact, it returns error. 

 

Where am I wrong? Which is the correct flow? 


7 replies

Userlevel 4
Badge +6

Hi @chiraginstanomic, this page is a great resource to test out and get some inspiration on Google Sheets integrations that can help you accomplish your end-goal! 

@blueseashell I would rather suggest to use Google Apps Script’s time based trigger and call the zapier webhook from that trigger for your automation.

Userlevel 7
Badge +8

Yes, there is no trigger available for Delay by Zapier. The Zap will be as follows:

 

  1. Trigger > New Row updated in Google Sheet (like you originally had it)
  2. Action > Delay by Zapier > Delay Until > Insert the date. it only allows you to delay for 30 days in the future. IF its more than 30 days, then this won't work (this is why I was asking how long is the delay)
  3. Action > Send New Email in Gmail 

     

Hi MohSwellam

thank you for your quick reply. The date of sending time can be tomorrow or 1 weeks or 1 month later. 

I try to see Delay for Zapier but it seems to be not available…

 

Userlevel 7
Badge +8

Hi @blueseashell 

 

How far in the future is the Date of Sending Time? Because you can always use Delay by Zapier for that. 

Hi MohSwellam

unfortunately I can’t use Airtable, and I need to use exclusively Google Sheets and Gmail. 

So, are there other apps (zapier apps)  I can use as trigger? 

 

Userlevel 7
Badge +8

Hi @blueseashell 

 

I dont think this is the best practice. There are several ways to do so: 

 

  1. Use Airtable instead of Google Sheets

In this scenario, Airtable gives you the ability to trigger zaps based on Views. You can set filters in the view based on NOW value (using a formula) , so the record enters the view only when the Sending Time = NOW. This can be used as the trigger in Zapier and sends the email. 

 

  1. Use Google Calendar 

In this scenario, when you create the Google Sheets row (im assuming its coming from another zap?) you can then create an Event in Google Calendar. In the Event description, you will add the email body and the event name would be the email address for example 

 

You can then have a zap that triggers based on Google Calendar events to send the email. 

 

Personally I would use Option 1 because it gives you more control. 

 

Hope this helps.

Reply