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. :) 

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. 

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 = v];

// 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: datesei] });
}

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

@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? 

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. 

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: 


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 = d];

  // 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 = o];

// 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: datesti] });
}

// 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:


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!

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 

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!