Skip to content

Instantly share code, notes, and snippets.

@ojwoodford
Last active March 27, 2024 16:03
Show Gist options
  • Star 4 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ojwoodford/69b72a5bcded0f11ae6c58138a55debb to your computer and use it in GitHub Desktop.
Save ojwoodford/69b72a5bcded0f11ae6c58138a55debb to your computer and use it in GitHub Desktop.
Sync events from a personal Google calendar to a work Google calendar
function SyncMyCal() {
var options = {
'targetEventTitle': "Busy (sync'd event)", // What event title do you want ported events to have
'daysahead': 60, // How many days ahead do you want to sync events over
'ignorealldayevents': true, // Do you want to ignore all day events in your "from" calendars
'ignorethesedays': [0, 6], // Are there days of the week you don't want to sync? 0 = Sunday
'maxhoursbetweenruns': 1, // How many hours between scheduled runs of the calendar sync (you need to set these up)
'onlybusyevents': true, // Only copy events marked 'busy'
'busyness': 1, // Should events be marked 'busy' by default (0: free, 1: busy, 2: match source event)
'visibility': 2, // Should the events be visible by default (0: private, 1: public, 2: match source event)
'verbose': false, // Do you want to log output (useful for debugging)
}
// The first array below contains the ID strings of the "from" calendar (your personal calendars), and
// The second argument is the ID string of the "to" calendar (your work calendar)
CalendarSync(["XXXXXXXXX", "YYYYYYYYYY"], "ZZZZZZZZ", options)
}
// CalendarSync - sync from one or more google calendars to another calendar, with:
// - a generic event name (original name in the description)
// - visibility set to private
// - no reminders set
// Useful if you want to automatically block out personal commitments in your work calendar
// Note, you must have previously shared the "from" calendars with the "to" calendar,
// otherwise this script won't have access
function CalendarSync(fromcals, tocal, options) {
var today=new Date();
var enddate=new Date();
enddate.setDate(today.getDate()+options.daysahead); // how many days in advance to monitor and block off time
var lastupdate=new Date();
lastupdate.setHours(today.getHours()-options.maxhoursbetweenruns); // how long ago was this script last run (at a maximum)
// Calendar to copy events to
var targetEvents=CalendarApp.getCalendarById(tocal).getEvents(today,enddate).filter(e => e.getTag("CalSyncKey") != null); // all target calendar events created by this script
var targetEventIds=targetEvents.map(e => e.getTag("CalSyncKey")) // Original event IDs
// Process source calendars
if (typeof fromcals === 'string' || fromcals instanceof String) {
ProcessSourceCalendar(tocal, fromcals, targetEventIds, today, enddate, lastupdate, options)
} else {
for (const cal of fromcals) {
ProcessSourceCalendar(tocal, cal, targetEventIds, today, enddate, lastupdate, options)
}
}
// If a target event previously created no longer exists in the source calendar, delete it
for (var tev in targetEvents)
{
if (targetEventIds[tev] === "")
continue;
if (options.verbose) { Logger.log('EVENT DELETED: ' + targetEvents[tev].getStartTime() + ' ' + targetEvents[tev].getDescription()); }
targetEvents[tev].deleteEvent();
}
}
function ProcessSourceCalendar(tocal, fromcal, targetEventIds, today, enddate, lastupdate, options) {
// Get all the events in source calendar in the relevant time period
var events;
try {
events = Calendar.Events.list(fromcal, {
timeMin: today.toISOString(),
timeMax: enddate.toISOString(),
singleEvents: true
});
}
catch (err) {
Logger.log('Error calling Calendar.Events.list for calendar ' + fromcal + ': ' + err);
// Failure. Ensure that the calendar ID is correct, that your account has access, and that you've enabled the Calendar API under "Services"
return;
}
if (!events.items || events.items.length === 0) {
return;
}
// Process each event
for (const event of events.items)
{
if (options.ignorealldayevents && event.start.date)
continue; // Do nothing if the event is an all-day event. This script only syncs hour-based events
const startTime = event.start.getDateTime();
const endTime = event.end.getDateTime();
if (!PeriodOverlapsDays(startTime, endTime, options.ignorethesedays)) // Skip events outside of work days
continue;
var available = event.getTransparency();
if (options.onlybusyevents && available) // Skip events marked available
continue;
// Check if the source event has already been blocked in the target calendar
var id = event.getId();
var ind = targetEventIds.indexOf(id);
if (ind != -1) {
// Check if anything changed since the last script run
var lastupdated = new Date(event.getUpdated())
if (lastupdated < lastupdate) {
targetEventIds[ind] = ""
if (options.verbose) { Logger.log('EVENT UNCHANGED: ' + startTime + ' ' + event.getSummary()); }
continue;
}
}
// Create a new event
available = options.busyness == 0 ? true : (options.busyness == 1 ? false : available)
var visible = options.visibility <= 1 ? options.visibility : event.getVisibility();
var newevent = {
"summary": options.targetEventTitle,
"description": event.getSummary(),
"start": {"dateTime": startTime},
"end": {"dateTime": endTime},
"transparency": available ? "transparent" : "opaque",
"visibility": visible ? "public" : "private",
"extendedProperties": {"shared": {"CalSyncKey": id}}
};
// call method to insert/create new event in provided calandar
Calendar.Events.insert(newevent, tocal);
if (options.verbose) { Logger.log('EVENT CREATED: ' + startTime + ' ' + event.getSummary()); }
}
}
function PeriodOverlapsDays(startDate_, endDate_, ignorethesedays) {
if (ignorethesedays.length == 0)
return true;
var startDate = new Date(startDate_)
var endDate = new Date(endDate_)
var date = startDate.getDate()
var day = startDate.getDay()
while (startDate < endDate) {
if (!ignorethesedays.includes(day))
return true;
date++;
startDate.setDate(date);
day = (day + 1) % 7;
}
return false;
}
@djibouti33
Copy link

@ojwoodford for your sake, I'm glad you couldn't recreate my issue :)

I’ve got a lot of different things happening. When I look at my Calendar A, B, and C, here's what I see:

  1. Recurring Event on Calendar A was deleted a long time ago. It's still showing up on Calendar B (that points to an issue with Google's syncing of iCloud calendars maybe?) and C
  2. Event (not sure if it was recurring or not) on Calendar A was deleted a long time ago. It's still showing up on Calendar B (again, maybe a problem with Google’s syncing/caching of my iCloud Calendar?)
  3. On Wednesday, I deleted an individual event that was part of a long-standing recurring event to test what happens with a current deletion. The event is removed from Calendar A,. When I look on the Google Calendar, I see it on Calendar B and C. When I look at my Mac Calendar App, I just see the Calendar C event (the event marked as “blocked”.

Do you know of any way to get Google to drop its cache of my iCloud calendar? I've tried deleting it and re-adding it, but interestingly, it assigns the same calendar ID when I do that.

@ojwoodford
Copy link
Author

@djibouti33 If the deleted events are appearing in Calendar B, then I don't think there's anything my script can do to avoid duplicating them. It's definitely a problem with Google's subscription, or the iCloud calendar. That said, you could run the script in debug mode, and break on line 88 or 89 when it's one of these phantom events, and check the event properties to see if there's anything that would mark it out as being deleted.

Do you know of any way to get Google to drop its cache of my iCloud calendar?

You can fool Google Calendar to think the iCloud calendar is a new one by adding #1, #2 etc. to the end of the URL when subscribing to the calendar.

@kpham123
Copy link

Hi OJ - thanks for the rewrite, this was dope!

I found 2 bugs/improvements:

  1. the busyness operator is inversed according to the comments in the options. I've swapped the true and false booleans in the section below to address this.

  2. the initial code references "summary": options.targetEventTitle (which doesn't exist) and the description moves the title to the description: "description": event.getSummary(). I've corrected the code below to mirror the events exactly.

    // Create a new event
    available = options.busyness == 0 ? true : (options.busyness == 1 ? false : available)
    var visible = options.visibility <= 1 ? options.visibility : event.getVisibility();
    var newevent = {
    "summary": event.getSummary(),
    "description": event.getDescription(),

@ojwoodford
Copy link
Author

@kpham123 Glad you like it and thanks for the corrections! I've updated the script, but just fixed the event title in the options. Maybe I should also add the option to copy the event details exactly, as you've done. I might add that the next time I do some maintenance.

@djibouti33
Copy link

Thanks @ojwoodford ! Using your tip about appending #1 to the end of the URL, I was able to generate a new Calendar ID for my iCloud Calendar. I was then able to re-sync, and after deleting some iCloud events (and waiting for 2 days for Google to sync), your script successfully deleted those events. Really appreciate it!

In regards to your conversation with kpham123, I might suggest making the syncing of event details driven by an option. For me, I just want my co-workers to see that my calendar is blocked for personal reasons, but don't want any details shared.

Thank you!

@mbk-27
Copy link

mbk-27 commented Jan 9, 2024

I am new to this but having trouble and receiving the following error messages - can anyone assist please?

TypeError: Cannot read properties of undefined (reading 'daysahead')
CalendarSync @ Code.gs:28
SyncMyCal @ Code.gs:15

@ojwoodford
Copy link
Author

ojwoodford commented Jan 9, 2024

@mbk-27 Either there is something wrong with the options variable you construct in SyncMyCal() (line 2), e.g. a missing comma between lines or a missing daysahead field, or you are not passing options into the CalendarSync() function as the third argument. At least that's my best guess.

@sarjouns
Copy link

@ojwoodford I love the high-quality code, thank you. Two feature requests:

  1. Remove notification from the new events (e.g. newEvent.removeAllReminders())
  2. Create an Out of Office or Focus Time event instead of a regular event

@cjbeatty
Copy link

@ojwoodford Not sure what's going wrong here, but I'm consistently getting this error:

Error calling Calendar.Events.list for calendar name@company.com: ReferenceError: Calendar is not defined

The original script this one was based off of does not have this issue, so not sure what is different.

@mcculloughcm
Copy link

@cjbeatty make sure you've enabled the Calendar API (under the Services menu). I just had the same problem and that solved it.

@sashatem
Copy link

Game changer. Thanks for sharing, and even more so for making updates in reply to comments! I am wondering how to make multi-day events (e.g., OOO/vacations) appear. I've set the code to be applied only Monday-Friday and set ignorealldayevents to false. If I add an event on my personal calendar that spans say, one Saturday into the next Sunday, how might I show just the Mon-Fri sandwiched between those weekends as busy?

@sashatem
Copy link

Also, any idea how to exclude event invites that were left unanswered? They show neither as busy nor available in the original calendar, yet appear when sync'd over

@sarjouns
Copy link

Also, any idea how to exclude event invites that were left unanswered? They show neither as busy nor available in the original calendar, yet appear when sync'd over

I added this to the ProcessSourceCalendar function:
var myStatus = checkMyStatus(event, fromcal);
if (myStatus == "needsAction" || myStatus == "declined"){
if (options.verbose) { Logger.log('UNCONFIRMED EVENT SKIPPED: ' + event.getSummary())}
continue;
}

and created this function:
function checkMyStatus(event, fromcal) {
var attendees = event.attendees;
if (attendees) {
for (const attendee of attendees) {
// Check if the attendee matches the specified email address
if (attendee.email === fromcal) {
return attendee.responseStatus;
}
}
}
// Return null if the attendee is not found in the event
return null;

}

@swagluke
Copy link

swagluke commented Mar 19, 2024

@ojwoodford
Thanks for updating this script. Recently running into issues where it will create duplicate events. It seems like the trigger will run the script twice and shows one completed and one failed. But one additional duplicated event will be created.
Any idea why it's acting this way?

@ojwoodford
Copy link
Author

@swagluke I don't know why the script would run twice. That seems odd, and I haven't heard of anyone else having that issue. One possibility is that you have two triggers. Another could be that you're calling CalendarSync twice. Perhaps the failure and duplicate events are caused by the scripts running in parallel, or else being run too close together, such that newly created events don't get listed the second time the script is called.

@swagluke
Copy link

@swagluke I don't know why the script would run twice. That seems odd, and I haven't heard of anyone else having that issue. One possibility is that you have two triggers. Another could be that you're calling CalendarSync twice. Perhaps the failure and duplicate events are caused by the scripts running in parallel, or else being run too close together, such that newly created events don't get listed the second time the script is called.

I've double-checked that only one trigger is set up. After looking through the execution, it seems like there will be a back-to-back trigger happening within seconds. And the second one always fails with exception: The calendar event doesn't exist, or it has already been deleted.
Very strange indeed. I'm gonna delete the whole project and restart it to see if it helps.

@swagluke
Copy link

@ojwoodford
Just to make sure I'm setting it up correctly, this is the trigger set up, could you confirm?
Screen Shot 2024-03-22 at 1 02 38 PM

@ojwoodford
Copy link
Author

@swagluke I have never used event-based triggers for the script. It may be that the script triggers itself a second time.

I use a timed trigger, set up as follows:
Screenshot 2024-03-22 at 19 50 57

@jedweintrob
Copy link

Hi there. I also keep getting "Error calling Calendar.Events.list for calendar name@company.com: ReferenceError: Calendar is not defined" even thoguh I have updated the calendar API. The original ttrahan/block_personal_appts script works fine, but i'd like to be able to customize the way you have it here.

@jedweintrob
Copy link

@ojwoodford see question above AND one more question: in the line that follows, what are the XXX, YYY and ZZZ variables supposed to be replaced with? I assume XXXX is for secondary calendar and that maybe YYY is for primary calendar, but then what is ZZZ for? CalendarSync(["XXXXXXXXX", "YYYYYYYYYY"], "ZZZZZZZZ", options)

@swagluke
Copy link

swagluke commented Mar 27, 2024

@ojwoodford see question above AND one more question: in the line that follows, what are the XXX, YYY and ZZZ variables supposed to be replaced with? I assume XXXX is for secondary calendar and that maybe YYY is for primary calendar, but then what is ZZZ for? CalendarSync(["XXXXXXXXX", "YYYYYYYYYY"], "ZZZZZZZZ", options)

@jedweintrob
I think ojwoodford made it very clear that XXX, YYY are your personal calenders and ZZZ is the work calendar.
For example, you have two personal gmails (p1@gmail.com, p2@gmail.com) and one work email (w1@gmail.com). Whenever you create an event on personals, this script will automatically block it off on your work.

So XXX will be p1@gmail.com and YYY will be p2@gmail.com and ZZZ is w1@gmail.com

Here is the comment he made in the script.
// The first array below contains the ID strings of the "from" calendar (your personal calendars), and
// The second argument is the ID string of the "to" calendar (your work calendar)

And your first Error is related to you not replacing the email in the script.

@jedweintrob
Copy link

Thanks for the clarity, @swagluke - I really appreciate it. I have replaced the email in the script with mypersonal@gmail.com and I am still having the problem where i get the error "Error calling Calendar.Events.list for calendar mypersonal@gmail.com: ReferenceError: Calendar is not defined"
I do not get this error with the older ttrahan script using the same calendar. I have enabled google calendar API. do you have any other ideas on how to fix that error?

@ojwoodford see question above AND one more question: in the line that follows, what are the XXX, YYY and ZZZ variables supposed to be replaced with? I assume XXXX is for secondary calendar and that maybe YYY is for primary calendar, but then what is ZZZ for? CalendarSync(["XXXXXXXXX", "YYYYYYYYYY"], "ZZZZZZZZ", options)

@jedweintrob I think ojwoodford made it very clear that XXX, YYY are your personal calenders and ZZZ is the work calendar. For example, you have two personal gmails (p1@gmail.com, p2@gmail.com) and one work email (w1@gmail.com). Whenever you create an event on personals, this script will automatically block it off on your work.

So XXX will be p1@gmail.com and YYY will be p2@gmail.com and ZZZ is w1@gmail.com

Here is the comment he made in the script. // The first array below contains the ID strings of the "from" calendar (your personal calendars), and // The second argument is the ID string of the "to" calendar (your work calendar)

And your first Error is related to you not replacing the email in the script.

@swagluke
Copy link

Thanks for the clarity, @swagluke - I really appreciate it. I have replaced the email in the script with mypersonal@gmail.com and I am still having the problem where i get the error "Error calling Calendar.Events.list for calendar mypersonal@gmail.com: ReferenceError: Calendar is not defined" I do not get this error with the older ttrahan script using the same calendar. I have enabled google calendar API. do you have any other ideas on how to fix that error?

@ojwoodford see question above AND one more question: in the line that follows, what are the XXX, YYY and ZZZ variables supposed to be replaced with? I assume XXXX is for secondary calendar and that maybe YYY is for primary calendar, but then what is ZZZ for? CalendarSync(["XXXXXXXXX", "YYYYYYYYYY"], "ZZZZZZZZ", options)

@jedweintrob I think ojwoodford made it very clear that XXX, YYY are your personal calenders and ZZZ is the work calendar. For example, you have two personal gmails (p1@gmail.com, p2@gmail.com) and one work email (w1@gmail.com). Whenever you create an event on personals, this script will automatically block it off on your work.
So XXX will be p1@gmail.com and YYY will be p2@gmail.com and ZZZ is w1@gmail.com
Here is the comment he made in the script. // The first array below contains the ID strings of the "from" calendar (your personal calendars), and // The second argument is the ID string of the "to" calendar (your work calendar)
And your first Error is related to you not replacing the email in the script.

ttrahan's script is much different from this script.

  1. Did you make sure the work email and personal emails are in the right place in the script?
  2. Is Google Calendar API enabled for the script?
  3. When you run the script on App Script from your Work Email?
  4. Does your Work Email and Personal Email have access to see all event details from each other?
  5. When you run the script from your Work Email on App Script, Google will prompt log in, did you sign in with your personal email to give permission?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment