Best answer

How can I automate Slack notifications for all active employee holidays in Google Calendar?

  • 9 November 2023
  • 13 replies
  • 245 views

I’m trying to automate info about employees holidays on Slack. I tried Event Start in Google Calendar → Send Channel Message in Slack and it works only for events starting on a given day but it doesn’t show me the full list of people who are off on that day.

Example:

  • There are 3 people on holidays:
    • Bob (from 9th to 10th November),
    • Jane (9th November only),
    • Miranda (10th November).
  • On the 9th, I got a Zap saying that Bob and Jane were off.
  • On the 10th I got a Zap saying that Miranda is off but not Bob.

 

Yes, I’m aware that with “event start” trigger I’m not going to see that Bob is off on the 10th, because his holiday event starts on the 9th. Unfortunately, I didn’t find any trigger that would fetch a list of all active events on each given day, regardless of their start/end date.

Does anyone know any workaround for this? 

icon

Best answer by SamB 17 November 2023, 15:33

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.

13 replies

Userlevel 7
Badge +11

Hello and welcome to the Community, @Monika! 🎉 

Ah, yes the Event Start trigger would only return the event details for an event that matches the search terms. So if the event spans two days and you’re searching for an event that starts that day then it’s not going to match.

I wonder if you’d be better off using the Schedule by Zapier app as the trigger and then follow that up with a search action that searches for all holiday events taking place that day. Although, it doesn’t appear that there’s a “Find Multiple Events” action for Google Calendar. So perhaps you could maintain a list of the holiday events for each day in a Google Sheets spreadsheet. You could populate the spreadsheet using another a Zap that triggers when the event is created and adds the details to the spreadsheet. Then in the existing Zap, you could use a Google Sheets search action to search for any events that are occurring that day and send the channel a Slack message with the relevant details. 

That said I should point out that adding a search action to the Zap would make it a Multi-step Zap which is only available on paid plans. So if you’re not on a paid plan then this might not be a great solution.

 

If you are on a free plan, the alternative may be to ensure that the holiday events do not span more than one day. Obviously that adds a bit more manual work on your side which isn’t ideal but should ensure that the existing Zap is able to correctly spot each day’s holiday events.

Hope that helps to get you pointed in the right direction. Please do keep us updated on how you get on with this!

Hmmm @SamB that’s tricky, because the holidays list comes as a Google Calendar that’s automatically populated from a third party app. So, if someone takes more than one day off, it will come in that calendar as an even spanning over a number of days. I’m not sure if I can convert this to a Google Sheet, and definitely can’t imagine splitting everyone’s holiday manually into single calendar events. 

I’m stuck for now, but still grateful for your response. :) 

Userlevel 7
Badge +11

Ah I see. Thanks for clarifying that, @Monika. 🙂

Hmm, can I ask what app is adding the events into Google Calendar? Just wondering if you might be able to cut out the Google Calendar step here and connect that app to Slack instead. 

Could that be an option?

@SamB Thanks for getting back to me. 

We’re using this platform for all things HR, including booking our days off https://www.bamboohr.com

From there, I fetched an iCal feed link that I imported to a newly created calendar, that I’m not using in my Zap. Each person’s holiday is shown there as an event lasting over few days, and I have no control over its format. 

Userlevel 7
Badge +11

Thanks for getting back to me @Monika.

There’s an integration with BambooHR on Zapier and it’s got a New Time Off trigger which looked promising but it doesn’t return a list of the dates as line items. If it did then you could pass that over to a Create Multiple Spreadsheet Rows (Google Sheets) action to create the separate rows to log each day of the holiday for each employee.

That said, I’ve been playing around with a Code by Zapier action and used our Generate with AI feature to product some code that takes the start and end date for the holiday request and output those dates (and any in between) as line items:
1afca08ec1ef63d9220cabb0e0b30e0f.png

 Here’s the code I got it working with:

// Function to get all dates between a start and end date
function getDates(startDate, endDate) {
// Create an empty array to store the dates
var dates = [];

// Convert the start and end dates to Date objects
var currentDate = new Date(startDate);
var stopDate = new Date(endDate);

// Loop through all the dates from the start to the end date
while (currentDate <= stopDate) {
// Add the current date to the array
dates.push(new Date(currentDate));

// Move to the next day
currentDate.setDate(currentDate.getDate() + 1);
}

// Return the array of dates
return dates;
}

// Get the start and end dates from the input data
var startDate = inputData.startDate;
var endDate = inputData.endDate;

// Call the getDates function with the start and end dates
var dates = getDates(startDate, endDate);

// Create an empty array to store the output
var output = [];

// Loop through all the dates
for (var i = 0; i < dates.length; i++) {
// Add each date to the output array as a line item
output.push({ line_item: dates[i] });
}

// Set the output variable to the array of dates
output = { dates: output };

Then I selected the line items for the date values like so:
48630b3b7fcba2c9a50a6f666759ccfa.png
Which allowed the Google Sheets action to then created the necessary 3 rows in a spreadsheet:
4bd61d865ae273945af6076eba83dec9.png

So in theory you could use that workflow to populate a Google Sheets spreadsheet with all the upcoming holiday. Then have another Zap that uses that Schedule by Zapier app as the trigger like I mentioned earlier which would then take care of sending out the daily holiday reminders. 

Do you think that would work? If you do give that a try and run into any issues at all just let me know!

@SamB Thank you! I’m afraid, with my level of user rights at BambooHR, I can only see my team’s holidays. 

But what if, instead of BambooHR, I still use Google Calendar as my starting point? Fetch events from a certain calendar, break them into rows in Google Sheets using that script, then have another Zap that uses that Schedule by Zapier app as the trigger. 

What do you think? 

Userlevel 7
Badge +11

That’s a great idea @Monika! 🙂

Ah, the Find Event search action would only be able to fetch a single event not all events. But perhaps you can have a Zap that uses the New Event (Google Calendar) trigger which would run when a new event is added to a specific calendar. Then the Code action could get the individual dates for that event and pass them to the Google Sheets action which should take care of that part of the workflow. 

Want to give that a try and let us know how it goes?

@SamB Thank you very much! I tried and gut stuck with two things - I don’t know what headers should I use for my sheet (didn’t find anything useful in help articles), and what fields and with what data should I fill in to generate the code. 

Userlevel 7
Badge +11

Happy to help with this@Monika!

With the Google Sheets spreadsheet the headers should be whatever is most relevant to the data they’re going to contain. In the screenshot of an example Google Sheets spreadsheet I just stored the name of the employee and the individual holiday dates so I created two columns one called Name and another called Dates. But you could have fields called Employee Name and Holiday Dates for example. It’s totally up to you.

If helpful, you can find out more about how to best set up Google Sheets spreadsheets to work with Zaps here: Work with Google Sheets in Zaps

If you’re using the New Event (Google Calendar) trigger instead of using BambooHR then for the Input Data section you’d want to select the Event Begins and Event Ends fields in the Code step instead. For example: 
67afd81252bba4d6ff4ef1898710998f.png

That said, I don’t know whether BambooHR adds the events as all day events or sets a start and end time. If it adds them as all day events then the event will likely be set as finishing at 12:00am on the next day which isn’t ideal as it would mean you’d get a list of dates that includes the day after the end of the holiday. So for an all day event that starts on November 17th and ends on November 19th, Google Calendar would have 2023-11-20 in the Event Ends field (as pictured in the screenshot above).

In which case you’d want to use the following code in the Code by Zapier action instead:

// Function to get all dates between a start and end date
function getDates(startDate, endDate) {
// Create an empty array to store the dates
var dates = [];

// Convert the start and end dates to Date objects
var currentDate = new Date(startDate);
var stopDate = new Date(endDate);

// Loop through all the dates from the start to the end date excluding the last day
while (currentDate < stopDate) { // Change from <= to <
// Add the current date to the array
dates.push(new Date(currentDate));

// Move to the next day
currentDate.setDate(currentDate.getDate() + 1);
}

// Return the array of dates
return dates;
}

// Get the start and end dates from the input data
var startDate = inputData.startDate;
var endDate = inputData.endDate;

// Call the getDates function with the start and end dates
var dates = getDates(startDate, endDate);

// Create an empty array to store the output
var output = [];

// Loop through all the dates
for (var i = 0; i < dates.length; i++) {
// Add each date to the output array as a line item
output.push({ line_item: dates[i] });
}

// Set the output variable to the array of dates
output = { dates: output };

And that should get it to ignore the last day (November 20th) and only output 2023-11-17T00:00:00.000Z, 2023-11-18T00:00:00.000Z and 2023-11-19T00:00:00.000Z like so:
f12dce3a2d83bb97334b50b8273eb1f5.png

Does that make sense? Let me know if that doesn’t work or if you need any further assistance at all on this. 🙂

@SamB I’m sorry, I’ve been busy with other things, and didn’t have time to check your last proposed solution. I will try it this week, and get back to you!

Userlevel 7
Badge +11

No worries, @Monika! I look forward to hearing how it goes! 🙂

@SamB I finally managed with the spreadsheet, the only problem now is that I’m on a free plan and can’t complete the Zap. I’m checking the option to upgrade, and in the meantime I think we can close this ticket.

Thank you very much for your assistance and your patience!

Monika 

Userlevel 7
Badge +11

Thanks for the update @Monika, glad I could help! 🤗

Sorry you’re not able to get the Zap up and running at the minute. But if you do upgrade and get stuck at all just let us know! 🙂