Best answer

Need Zap to Send Automated Emails (Gmail) by dates in Google Sheets.

  • 26 September 2022
  • 7 replies
  • 1071 views

Hey Guys, I was using a premium scheduler for my business. It would send annual reminders to people about their renewals coming up. I accidentally deleted the calendar the ability to do this. So now I’m trying to do it through Zapier. 

 

I have the Google Sheets with the data (name, email, date, etc.) I want to integrate GMAIL and Google Sheets so that it reads the spreadsheet daily to see who needs the automated  email sent to them.  The only trigger criteria is the date. If the date is 10 months from the date on the spreadsheet, I want gmail to send an email.

 

How can I do this? Would appreciate any help you guys can offer.  Thank you

icon

Best answer by Danvers 5 October 2022, 12:29

View original

7 replies

Userlevel 4
Badge +6

For this kind of complex workflows I would highly recommend you to look at using Airtable to store Date as the field and then create Views with the Condition as Date is Today Filter.Later you can trigger Zaps with Airtable and send email through Gmail App.

Userlevel 7
Badge +9

Hey there, @surfer808! I wanted to stop in and see how you were coming along with this?

Keep us posted - we’d love to know! 🤗

No I didn’t do it yet. Is this my only option to send automated emails by using google sheet and gmails for my project?

Userlevel 7
Badge +12

Hi @surfer808!

Using Airtable isn’t the only option, but @Satya09 is right that it would be the simplest way. With that, Airtable would do all the work of finding and filtering the views and your Zap would trigger when there’s a new record in the view so it can send an email. 

 

I appreciate that you might not have the time to learn a new app, or you might not want to, which is totally fine. To do this in Google Sheets, you could set up a Zap similar to this:

  1. Schedule by Zapier: Trigger Zap once a day
  2. Google Sheets: Find Many Spreadsheet Rows (With Line Item Support) 
  3. Gmail - Send email

The Find Many Rows action can find up to 10 rows that meet the search criteria, will that work for you? Would you have more than 10 subscribers that need reminders? 

The tricky part of this will be finding the correct search criteria to match the rows that you need. The Google Sheets Find Rows doesn’t allow for search functions like ‘OR’ so you could only search for one date at a time. 

Does that help you to get pointed in the right direction? If not, could you confirm exactly what data is in the Google Sheet and when you need to remind users to renew their subscription? Thanks!

Yes that could work. I wouldn’t need to send more than 10 a day. Also, finding a specific date will work for each day too. Example, it could search for dates 300 days ago and if they column had that date, it would send an email to the person reminding them of their appoint comings up 

Userlevel 4
Badge +7

Hey again @surfer808 !

Yeah that would work -- that said, it would be hard to generate a date 300 days in the past but one could use a Code by Zapier step an puzzle out an algorithm.  This is necessary since dates roll over at the beginning/end of the year and it is hard to tell a computer what to do, manage leap years, etc. 

 

If your needs were such that you needed to generate a date in the past, there may be easier ways to do what you wish here.

 

One example would involve Transfer by Zapier and a few other Zaps.  If you had a spreadsheet of renewal dates and the users emails already generated, you could set up a Transfer into a brand new Google Calendar for this purpose to generate an initial calendar that Zapier can refer to for your needs. 

Once that Calendar is created you can use the Google Calendar Event Start action which will let you say how many days before the event you want the Zap to start.  Combine that with whatever actions you wish!

Beyond that initial import, you could then setup another Zap to monitor that sheet for updates and changes with a Zap like so to keep the calendar up-to-date:

Google Sheets New/Updated Row trigger => Google Calendar Update Event action

At the end of the day, Zapier can help you do the same thing in multiple different ways depending on your exact needs.  Hopefully this gives you some ideas =)

I need to send one email after a google sheet date and I have no idea how to do it! 

Reply