Connect to Calendar - Create Events From Spreadsheet

Creating Calendar events from data in a Sheet.
Important notes:
* You must have write access to the calendar being accessed in order to use .createEvent()
* A calendar can be retrieved as the default calendar, by ID, or by name. Calling by ID or Name returns an array, so be sure to get the first element of that array [0]
* The /options/ parameter of the .createEvent() method can accept description, location, guests, and sendInvites
* .setHours() is more readable than .setTime() because the latter accepts "a numeric value representing the number of elapsed milliseconds since midnight" and adjusts the entire Date object while the former only adjusts the hrs/min/sec/ms of the existing Date
Spreadsheet (get your own copy using "Make a copy" from the File meu)
docs.google.co...
Script:
function createCalendarEvents() {
const sh = SpreadsheetApp.getActive();
const cal = CalendarApp.getDefaultCalendar(); // or any other method to access a calendar
const ss = sh.getActiveSheet();
let data = ss.getRange(2,1,ss.getLastRow(),4).getValues();
let client,loc,d1,d2,t;
for (let i in data){
client = data[i][0];
loc = data [i][1];
d1 = new Date(data[i][2]);
t = new Date(data[i][3]);
d1.setTime().setHours(t.getHours(),t.getMinutes());
d2 = new Date(d1.getTime() + 30 * 60000);
cal.createEvent("Meeting with " + client,d1,d2,{location: loc});
}
}
Connect with me:
• [e-mail]
• spencerfarris.me
• www.linkedin.com/in/spencer-farris/
• Twitter @FarrisSpencer
• Google Product Expert support.google.com/docs/profile/12305
Donate on Paypal: [e-mail]

[@AlexS-ld8ys]

Great videoes, Spencer. Thank you for putting in the effort. Keep up the good work! Love the email videos.

[@SpencerFarris]

Thank you!

[@Camdizzzzle]

This works great for pushing data to a calendar, but if I edit the data, how do I update the calendar without creating duplicate events?

[@brcleaning3092]

same question,,, have you been able to figure this one out?

[@sujikim5579]

@@brcleaning3092 me too T,T have you been able to figure this one out?

[@TrenerZvezdnaia]

Super!!!

[@yomammaonastick]

This is awesome man thank you I’ve been working tirelessly for days looking for a demo that does that step in a project I’m working (with zero and I really mean zero computer science experience). Do you have any advice on what I would do to prevent duplicated events when the scrip is ran again and then finally how to update existing events when the sheet changes? I imagine they both tie together with sort of identifier like an event ID. There are probably fancier ways to do it but I would just end up using the time stamp google sheets uses when a form is submitted as the ID. I just don’t have a clue how to continue from here.

[@SpencerFarris]

There is a unique ID for each calendar event, so you could write that ID (CalendarEvent.getId()) back to the sheet, only create events that don't have an event ID, and update the event with that ID.

[@saadlahkim]

Thank you, Spencer for the Video great work, I would like to know if it is possible to have more sheets my issue is we have holidays in different countries and each sheet has the holidays for the calendar and we want to add these holidays to a calendar that can be shared by everyone in the company

[@JonathonNeville]

Can this work with recurring events, so Google Calendar recognizes it as a recurring event / repeating event (not just a list of individual events that happen to repeat)?

[@SpencerFarris]

Just use .createEventSeries() developers.google.com/apps-script/reference/calendar/calendar-app#createEventSeries(String,Date,Date,EventRecurrence)

[@DavidMarcadet]

Thanks

[@venkateshsharma2407]

What if I change the date, will it update the event automatically to the changed date?

[@SpencerFarris]

No

[@sergebrosseau991]

Just a log of my process to get this working on Oct 5th 2022 for the next ppl who come along and potentially have these issues. Getting this error with your current code and sheet listed in the comments of the video : TypeError: d1.setTime(...).setHours is not a function createCalendarEvents @ Code.gs:14 My personal calendar works but then if I try to change it to a named calendar or by Id, it doesn't seem to "find" the calendar I want to use ? (fixed) EDIT1: I'm in an organisation and the calendar I was trying to use was a calendar that had been assigned to me by the org administrator, so I created my own calendar and it works now. ****** EDIT2: Time the event is created is not correct. No matter what I do it will not schedule the event at the correct time specified in my sheet. The time is set to create the event for 8:00 AM, but it makes it for 10:00 AM. ****** Edit3: When using dates/time with a Spreadsheet, the script will use the Sheet's timezone settings. Having made a copy of your sheet it kept your timezone which was set to -7 Mountain Time. Changing the timezone setting in Appscript did nothing (was first thing I tried).

[@SpencerFarris]

Wow, lots of testing! I'm impressed. Are you currently having it working, or not working?

[@MonsciousEnglish]

Oct24 2022, and I am also having this problem (TypeError: d1.setTime(...).setHours is not a function). I can't seem to get logger to work for reading any of my sheet data dates either. Sorry I haven't got the error code for that here. I eventually downloaded the spread sheet link, copied and had the error mentioned above. I did notice that in the video you said the time was correct (in 24 hour format) But it actually wasn't. You had 3pm in your spreadsheet, and the data logger had the time as 17:00 (5pm), (1899) which is out by 2 hours (and how ever many hundred years). I was expecting to see 15:00 (3pm) as the correct time. I also found that when looking for calendars, it would only find calendars that were on the same account that I was signed into sheets with.

[@SpencerFarris]

​@@MonsciousEnglish Yes, you can only quickly see the calendars of the account. You can get other calendars by ID It's probable that your values are not formatted in a way that Sheets or the script recognize. The dates were correct for me. The offset comes off weird because of the difference between the sheet TZ, account TZ, and script TZ.

[@Kairosart]

@@MonsciousEnglishSame here with sethours error.

[@jenniferpyle8797]

As far as the setTime error, I was able to make it work by removing .setTime() if you see the code in the video it isn’t there. Hope that helps