Hi,
I want to create a zap that automatically triggers one week before the first tuesday of the month and this should repeat itself every month.
This zap is then supposed to take data from a google spreadsheet and send out Mails.
Is it possible to do something like this with Schedule and Formatter? If so, then how?
+6
Hey
The best solution i can suggest is write a Javascript code in Code by Zapier and than use filter in next steps.
Your Zap will be, Scheduler → Check if after a week it is first tuesday (Code by Zapier)→ If Yes (Filter)→ Get data from Google Spreadsheet and Send emails
Note - Consider hiring an expert for this. You can visit expert directory here (Or you can hire me also)
+14
Hi
Consider using Airtable for this and in place of GSheets: https://zapier.com/apps/airtable/integrations#triggers-and-actions
Airtable has Formula fields that can be used to determine the desired date: https://support.airtable.com/hc/en-us/articles/203255215-Formula-field-reference
Airtable has Views with Filters that can be used to trigger Zaps: https://support.airtable.com/hc/en-us/articles/202624989-Guide-to-views
Airtable has Automations: https://support.airtable.com/hc/en-us/articles/360050974153-Automations-overview
+1
Hey
Created this spreadsheet for you in Google Sheets. It has a formula that looks at the first day of the month, and then pulls out the first Tuesday of that month and subtracts 7 days from it to give you one week before the first Tuesday:
https://docs.google.com/spreadsheets/d/18_pBAmQ5bFNuHbYTqp5WLiOSN2aOMRVPkavcmLKpOyo/edit?usp=sharing
Feel free to make yourself a copy.
Now with Scheduler you can on the daily, check the current Date against the date in 7 day prior column and if they match trigger the rest of your zap.
Hey
Created this spreadsheet for you in Google Sheets. It has a formula that looks at the first day of the month, and then pulls out the first Tuesday of that month and subtracts 7 days from it to give you one week before the first Tuesday:
https://docs.google.com/spreadsheets/d/18_pBAmQ5bFNuHbYTqp5WLiOSN2aOMRVPkavcmLKpOyo/edit?usp=sharing
Feel free to make yourself a copy.
Now with Scheduler you can on the daily, check the current Date against the date in 7 day prior column and if they match trigger the rest of your zap.
Hi Aaron,
thanks a lot for this!
The first step is “Schedule: Look up every day”
Problem is that the second step Lookup Spreadsheet Row in Google Sheets says “We had trouble getting a matching spreadsheet row. :-(Error details:
Nothing could be found for the search.”
I attached a screenshot, maybe you can help?
Can anyone help with my last question?
+11
Hey
From the look of that screenshot it seems that the Lookup Spreadsheet Row step is searching the 7 Days Prior column for Oct 27, 2021:
Does the 7 Days Prior column in the Google Sheet contain the date in that specific format, or is the date formatted differently? If the date format is different then that could well be why it’s having trouble finding a match.
If that’s the case, then I’d suggest either changing the format of the dates in the Google Sheet to match the same format that you’re searching for. Or, you can add a Formatter (Date/Time > Format) step to convert the date into the same format that’s used in the Google Sheet.
Hope that helps! :)
Hey
From the look of that screenshot it seems that the Lookup Spreadsheet Row step is searching the 7 Days Prior column for Oct 27, 2021:
Does the 7 Days Prior column in the Google Sheet contain the date in that specific format, or is the date formatted differently? If the date format is different then that could well be why it’s having trouble finding a match.
If that’s the case, then I’d suggest either changing the format of the dates in the Google Sheet to match the same format that you’re searching for. Or, you can add a Formatter (Date/Time > Format) step to convert the date into the same format that’s used in the Google Sheet.
Hope that helps! :)
Hi Sam,
thanks for your answer! I tried using the formatter and I’m pretty sure that it’s now the same format as in google sheets but the lookup step still doesn’t work. It still says that it can’t find a matching row.
Any other ideas? :(
+11
Hey
Hmm, that’s odd. Can you send a screenshot of the row you would have expected the Zap to find? And a screenshot showing the value that it searched for?
Then we can compare the two to see if there are any other differences that are preventing a match from being found. :)
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
