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

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!

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?

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:


 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:

Which allowed the Google Sheets action to then created the necessary 3 rows in a spreadsheet:


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!

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?

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

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! 🙂