Google Calendar Question

[trs]

So... I work at a school. I'm mostly a history and theory of knowledge teacher, but I'm also a "teacher leader" (read: mid-level management....) and as such have been trying to set up Google Calendar to accept meeting requests for students.

Last year I slowly taught myself how to create an appointment schedule with Google Calendar, and it was a game changer in many ways. However, given that we're a school, our daily schedule is hopelessly complex and, well, annoying. I do however have two basic times that I would like to set myself available for appointments each day. This is fairly easy to do with the Appointment Schedule feature. HOWEVER, on some days I teach during those times and therefore unable to appoint myself. When I do teach is obviously based on a schedule, but it's not Monday to Friday but rather from Day 1 to Day 8 as a rotation.

All too much information. Basically, I'm wondering if it's possible through a script or otherwise to do this:

When on a given Google Calendar it is a "Day 1" to insert "Class" during certain times of the day. When it is "Day 2" to insert "Class" during other certain times of the day, and et cetera through "Day 8." This would basically populate my class schedule a bit more quickly and accurately than me typing each class period in. I learned that "Appointment Schedule" automatically marks as busy any appointment that would be scheduled when I am otherwise occupied.

I'm not sure any of this makes sense. Basically I'd like a series of recurring events that don't recur weekly or monthly but recur based on events from another calendar. Possible?

Probably not and I'll just stop being lazy and type in my classes...

 

[gtmtnbiker]

When I do teach is obviously based on a schedule, but it's not Monday to Friday but rather from Day 1 to Day 8 as a rotation.

All too much information. Basically, I'm wondering if it's possible through a script or otherwise to do this:

I’m not familiar with Google Calendar scripting but it appears that it has a REST API ( https://developers.google.com/calendar/api/v3/reference) so yes, you could create some sort of automation to populate your calendar. I guess it would be useful to you since it’s something you could run every school year.

You would have to handle holidays, half days, professional days.

in the past, I’ve used bash/curl or Perl/python to access REST APIs.

 

[cgori]

There appears to be some IFTTT stuff to sync one calendar to another, but you'll have to see if one of their automations will do exactly what you want.

If not, using the REST API (or possible Google's apps script) would certainly allow you to do this, just a matter of how much work it is to create the automation.

 

[luckiestman]

if you paste your question in Bing or GPT, it will draft a script for you. May not be easier than what you are doing already. I will paste below but the formatting will look better if you do it:

Hi, I understand your situation and I think it is possible to create a script that can automatically populate your class schedule based on a rotating day cycle. Here is one possible solution:

- You can use Google Apps Script to create a custom function that can determine the current day of the cycle based on a start date and the number of days in the cycle. For example, if your cycle starts on August 1st and has 8 days, you can use something like this:

```javascript
function getDayCycle(date) {
var startDate = new Date(2023, 7, 1); // August 1st, 2023
var cycleLength = 8; // number of days in the cycle
var diff = Math.floor((date - startDate) / (1000 * 60 * 60 * 24)); // difference in days
var dayCycle = (diff % cycleLength) + 1; // current day of the cycle
return dayCycle;
}
```

- You can then use this function in a Google Sheet to create a table of your class schedule for each day of the cycle. For example, you can enter `=getDayCycle(TODAY())` in cell A1 to get the current day of the cycle, and then enter your class times and names in the corresponding rows and columns. You can also use conditional formatting to highlight the current day of the cycle.

- You can then use the Calendar Service in Google Apps Script to create a function that can read your Google Sheet and create events in your Google Calendar based on your class schedule. You can use the `createEvent()` method to create a single event, or the `createEventSeries()` method to create a recurring event. You can also use the `setColor()` method to set the color of the event. For example, you can use something like this:

```javascript
function createClassEvents() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName("Class Schedule"); // name of your sheet
var calendarId = sheet.getRange("C4").getValue(); // cell with your calendar ID
var calendar = CalendarApp.getCalendarById(calendarId); // get your calendar
var data = sheet.getDataRange().getValues(); // get all data from your sheet
var today = new Date(); // get today's date
var dayCycle = getDayCycle(today); // get today's day of the cycle
for (var i = 1; i < data.length; i++) { // loop through each row of data
var row = data;
if (row[0] == dayCycle) { // check if the row matches the current day of the cycle
var startTime = new Date(today.getFullYear(), today.getMonth(), today.getDate(), row[1], row[2]); // get start time from row
var endTime = new Date(today.getFullYear(), today.getMonth(), today.getDate(), row[3], row[4]); // get end time from row
var title = row[5]; // get title from row
var color = row[6]; // get color from row
var event = calendar.createEvent(title, startTime, endTime); // create event
event.setColor(color); // set color of event
}
}
}
```

- You can then run this function manually or set up a trigger to run it automatically every day. This way, your Google Calendar will be updated with your class schedule based on your rotating day cycle.

I hope this helps you with your problem. If you need more information or guidance, you can check out these resources:

- [Google Apps Script quickstart](^7^): A tutorial on how to use Google Apps Script with Google Calendar.
- [Calendar Service](^5^): A reference for the methods and classes available in Google Apps Script for accessing and modifying Google Calendar.
- [How to automatically add a schedule from Google sheets to Calendar using Apps Script](^6^): A blog post that explains how to create a similar script for scheduling shifts.
- [Using Google Calendars to Create a Shift Calendar](^4^): A video that shows how to create a recurring calendar event using Google Calendars.

Please let me know if you have any questions or feedback. Thank you for using Bing!

Source: Conversation with Bing, 8/30/2023
(1) Google Apps Script quickstart | Google Calendar | Google for Developers. https://developers.google.com/calendar/api/quickstart/apps-script.
(2) Calendar Service | Apps Script | Google for Developers. https://developers.google.com/apps-script/reference/calendar/.
(3) How to automatically add a schedule from Google sheets to Calendar .... View: https://medium.com/@varsha.das22/how-to-automatically-add-a-schedule-from-google-sheets-to-calendar-using-apps-script-2d37990a1ddd
.
(4) Using Google Calendars to Create a Shift Calendar - YouTube. View: https://www.youtube.com/watch?v=9n5efJHdU-M
.
(5) How to automatically add a schedule from Google Sheets into Calendar. View: https://www.youtube.com/watch?v=MOggwSls7xQ
.
(6) Setting Up Classes in Google Calendar with a rotating class schedule.. View: https://www.youtube.com/watch?v=WSPkDibgZJ4
.
(7) How to Create a Weekly Schedule Using Google Calendar. View: https://www.youtube.com/watch?v=HRgdRuEcVbw
.
(8) undefined. https://tlp-lpa.ca/).
(9) undefined. https://tlp-lpa.ca/study-skills/weekly-schedule.
(10) undefined. http://www.medicmadness.com.

 

[trs]

Wow, thanks for all this, everyone, it's very much appreciated. The Google Sheets idea is interesting -- the only issue that perhaps could happen with just counting 1-8 is the various holidays or times when the 1-8 rotation pauses. Since one shared calendar already has the "Day number" for each calendar day (and skips holidays and weekends), that's why I was thinking maybe one could pull that information with something like (in a completely made up boolean langauge) if Cal1 contains "Day 1" than create event Class (8:45 - 10:05), Class (13:05 - 14:25). Then go to if Cal1 contains "Day 2" than...

But I'm going to click through those links from all of you, for as you said, something like this might already exist. At that point it will have taken more time to do the research than to type all these in manually, but it'd be a hell of a boon to many teachers who have Google Calendar...

Thanks again all of you!

 

[trs]

And wow, the ChatGPT idea was fantastic. After some questions and trials, it actually created a script that does just what I had asked!

That is a bit scary...