How to set up recurring Google Calendar events from Sheets for weekdays only?

Other Questions Google Sheets
1

I’ve got a script that adds events to Google Calendar from a spreadsheet. It works great, but now I need to make sure the recurring events only happen on weekdays (Monday to Friday).

Here’s the problem: if I set up a meeting that starts on September 2, 2014, and make it repeat monthly, some future meetings will end up on weekends. I want to avoid that.

Is there a way to make these recurring events stick to weekdays? Like, if a meeting would fall on a Saturday, can we move it to Friday instead? And if it lands on a Sunday, can we push it to Monday?

I tried using CalendarApp.Weekday.MONDAY and similar functions, but they clash with the .addMonthlyRule() part of my code.

Any ideas on how to fix this? I’m pretty stuck!

2

I’ve dealt with this exact problem in my work. Here’s what I found to be the most effective solution:

Instead of relying on Google Calendar’s built-in recurrence rules, I created a custom function in Google Apps Script to generate the event dates. This function checks each potential date and adjusts it if it falls on a weekend.

The basic logic goes like this:

  1. Generate the initial set of dates (e.g., every month on the 2nd)
  2. For each date, check if it’s a Saturday or Sunday
  3. If it’s a Saturday, move it to Friday; if it’s a Sunday, move it to Monday

Then, use these adjusted dates to create individual events. It’s more work upfront, but it gives you complete control over the scheduling.

This approach has been rock-solid for me. Let me know if you want more details on implementation.

3

hey aroberts, i had this issue too. i solved it by checkin dates before addin to calendar. if getDay() shows a weekend, adjust it to fri or mon. try it out, it’s a bit easier than workin with addMonthlyRule()!

4

I faced a similar challenge with recurring events and weekdays. Here’s what worked for me:

Instead of using addMonthlyRule(), I created a custom function to generate dates for the recurring events. This function checks if a date falls on a weekend and adjusts it to the nearest weekday.

Then, I used a loop to create individual events for each date generated by my custom function. This approach gives you more control over which days the events occur.

It’s a bit more code, but it solves the weekend problem effectively. You’ll need to modify your script to incorporate this method, but it’s worth the effort for accurate scheduling.

If you need help implementing this solution, I can share some sample code snippets.