Tuesday, 16 February 2021

Bulk create Google Calendar events with optional Google Meet

The following Google Apps Script tool is designed to bulk create Google Calendar events with optional video conferencing (ie Google Meet). The tool is run entirely from a Google Sheet and the details of each event is added per row, from which the tool plugs in to Google Calendar and bulk creates the events for you. The outcome is the standard Calendar event item that can then be edited just like any other.

Google Sheet columns allow for event details to be added.
Google Sheet columns allow for event details to be added.

Features of the tool

  • Performs an initial check that you have access to the provided Calendar to create events on.
  • Allows for events to be created on another Calendar that you have suitable access to (not just your own).
  • Uses toast popups to inform you of the progress as each creates each event per row.
  • Fast and efficient for creating a large number of events in one go.
  • Will not duplicate events if re-run, so you can continue to append further if you wish.
  • Provides a direct link to the created event from within the Google Sheet for easy access.
  • Performs a check of any missing 'required' information and informs the user via a popup so they can resolve this.
  • Includes 'Log' sheet to help output any error messages.
  • Has a 'Reset' option in the menu bar to remove all entered data and start from scratch.
  • Replicates 90%+ of the settings you can adjust when directly creating an event in Google Calendar.

 

How to use the tool

I've created a video (with captions) that helps to demonstrate using the tool:


File Download

Download the Bulk create Google Calendar events with optional Google Meet sheet here. Please use 'File' > 'Make a copy' for your own version.


The Code

Here is a link to the Bulk create Google Calendar events with optional Google Meet code blog post.

26 comments:

  1. Hi, so cool, i am going to try this tool to add the teachers subject to a Google cal, will this cause some issues if i am trying to add like 2000 events

    Thanks
    Anto
    IT Director
    Overseas School of Colombo

    ReplyDelete
    Replies
    1. Hi Anto

      Thank you.

      You need to be aware of Google's Quota limitations yes: https://developers.google.com/apps-script/guides/services/quotas

      You may hit the 6 minute total runtime limit (though our Education licence allows for 30 minutes so you may be ok here depending on your setup). Otherwise it appears you can create a minimum of 5,000 events per day.

      Kind regards
      Phil

      Delete
  2. Hi Phil,

    Thank you so much for creating this content and sharing it with us.

    I was wondering if there was a way to add to this process to turn off 'Quick Access' option off when the sheet creates the google calendar events? As 'Quick Access' under the google meet settings in the google calendar event is always turned on when created.

    ReplyDelete
    Replies
    1. Hi

      Thank you for your comment.

      That's a really good question and one I've been searching about online as a result. It seems that at this current time Google is enforcing this option as 'on' by default. They've also not provided any feature/setting in their Calendar API to be able to access it and turn it 'off'. So I'm afraid my hands are tied until Google become more flexible about the setting.

      Kind regards
      Phil

      Delete
  3. Hello Phil ..
    Really great work on "Bulk create Google Calendar events with optional Google Meet" .. I need help on script I am working ..The apps script collect last 2 months events... using calendar.events .. is there a way to get meet recording url from event in apps script?

    ReplyDelete
    Replies
    1. Hi

      Thank you for the nice feedback.

      Hhhmmm that's not something I've done and nothing is jumping out at me from the Apps Script support pages either. I have done a little something on getting Event details in this post: https://www.pbainbridge.co.uk/2019/07/search-google-calendar-for-event-details.html

      But looking at the Developer website I'm not seeing anything for getting the recording URL. I've looked here at what is returned from an event: https://developers.google.com/calendar/v3/reference/events#resource and here for the Calendar App: https://developers.google.com/apps-script/reference/calendar/calendar-event

      Kind regards
      Phil

      Delete
    2. Thank you Phil for different link, there were very useful info but not much info about getting google meet recording info.

      I am able to create script which collect data about event but missing recording info. If you get chance and get more understanding about this, appreciate if you can check and prepare some sample script which can get even and google meet recording info for past google events.

      Delete
    3. Hi

      As mentioned it isn't an option from Google I'm afraid.

      Kind regards
      Phil

      Delete
    4. Very cool script!

      I was able to integrate it no problem, however when it inputs the time I selected instead of 8am it put the meeting at midnight, is the script based on a certain time zone?

      Delete
    5. Hi

      Thank you!

      No it should use the TimeZone from wherever it is being run from - what TimeZone are you in?

      Kind regards
      Phil

      Delete
  4. Phil,

    How can I use this tool to create All day events?

    ReplyDelete
    Replies
    1. Hi

      I'm sorry but this tool is not set up for that.

      In terms of Apps Script you would need to look at the 'createAllDayEvent' method: https://developers.google.com/apps-script/reference/calendar/calendar#createAllDayEvent(String,Date,Date,Object)

      Kind regards
      Phil

      Delete
  5. Hey! I used this function:
    if(Status !="Done")
    {
    var events = eventCalendar.createEvent(title, new Date(startdate), new Date(enddate), {description, sendInvites: true, guests: guestString}).getId();
    setGuestsCanModify(true);
    Status = spreadsheet.getRange(index,9).setValue("Done");
    }
    With the following authorizations:
    "oauthScopes": [
    "https://www.googleapis.com/auth/calendar",
    "https://www.googleapis.com/auth/calendar.readonly",
    "https://www.google.com/calendar/feeds",
    "https://www.googleapis.com/auth/spreadsheets.currentonly",
    "https://www.googleapis.com/auth/spreadsheets"
    ]
    However, I am getting the following error:
    ReferenceError: setGuestsCanModify is not defined

    Can you guide on this?

    ReplyDelete
    Replies
    1. Hi Gitika

      The tool I've created here uses the Calendar API (https://developers.google.com/calendar/v3/reference/events/insert) where there are many more options available to define.

      From your code it looks like you are using the 'Calendar App' (https://developers.google.com/apps-script/reference/calendar/calendar-app#createeventtitle,-starttime,-endtime,-options) which does not allow for details such as Guest modification.

      Kind regards
      Phil

      Delete
    2. Hi Phil! I used your code and sheet to trigger the calendar events and everything works great. I just needed your help understanding one thing.
      In all the events that is being triggered, I am showing up as the organizer. Is there a way to remove myself from these events as organizer?

      Delete
    3. Hi Gitika

      That's great!!!!

      Re Event Organiser - yes that is a feature we needed at our institution so I coded it in specifically, which means we should be able to remove it for you.

      If you go into the Script Editor via the Google Sheet, then on the left go to the File called '7) sortAttendees.gs'.
      If you comment-out line 34 where it is adding the Calendar creating the event as a Guest, I believe that is what you need. Save your changes, exit and try running it again.

      Kind regards
      Phil

      Delete
    4. I followed all your steps and tested a few scenarios. If my email is part of the list of attendees that I am specifying in the Events tab, it still shows me as the organizer.(Eg: If the calendar ID say xyz@gmail.com on Welcome tab matches the email ID in the Events tab column L value xyz@gmail.com, it shows the calendar ID as organizer. But if I remove the value xyz@gmail.com from column L and trigger with the calendar ID as xyz@gmail.com, it doesn't show any organizer.
      This is just for your information. As of now, your solution really helped me out in my scenario.
      I just observed one more issue for me. While entering information in the Events tab, column D, i.e., event description, I want to send out a link in the text which points to another excel sheet. It shows up on the excel sheet, but when the event is set, it is all plain text. Is there a way to do it?

      Delete
    5. Hi Gitika

      Thank you for providing that further information as part of your testing.

      I tried to fix the hyperlink issue but it become quite comprehensive - and yes seems to use plain text by default. However by inserting the full hyperlink itself eg https://www.bbc.co.uk that does come across as a clickable link.

      Kind regards
      Phil

      Delete
  6. Hi Phil! First of all, thanks for all your help. I am really grateful for it.
    I tried inserting the full hyperlink to make it a clickable link, but that isn't working.
    Is there a way I add the file to the meeting invite as an attachment?

    ReplyDelete
    Replies
    1. Hi Gitika

      You're very welcome.

      I'm not sure what is happening here. Are you inserting it into the 'Event Description'? I've just tried it with different files and multiple events and it creates a clickable link (albeit a long Url one) in the Calendar event description.

      I'm afraid the tool is not set up for attachments specifically, at this point.

      Kind regards
      Phil

      Delete
  7. Hey Phil

    So, my use case is as follows:
    I have to setup multiple meetings for 150 people as a group of 3 which we are calling as Water Cooler Chats(value under Event title). In the Event description, this is what I am entering -
    "If you can't make it, please feel free to reschedule with your group or let them know you won't be able to make it.
    Please note: This meeting is 100% OPTIONAL! If you do not wish to participate, please go ahead and check the box next to your name in the {Water Cooler Chats Opt Out Log}."
    On the text {Water Cooler Chats Opt Out Log}, I inserted link to an excel doc with link in this format https://docs.google.com/spreadsheets/...

    But when I setup the meeting, it comes as plain text.

    ReplyDelete
    Replies
    1. Hi Gitika

      As I mentioned you cannot create the link 'on' the text, it must be inserted entirely separately, so:

      "If you can't make it, please feel free to reschedule with your group or let them know you won't be able to make it.
      Please note: This meeting is 100% OPTIONAL! If you do not wish to participate, please go ahead and check the box next to your name in the Water Cooler Chats Opt Out Log: https://docs.google.com/spreadsheets/..."

      Kind regards
      Phil

      Delete
  8. Hi Phil, this is a pretty cool tool! Question-
    How can I update details for an event and have it sync? I'd like to use this to bulk update existing events with new details. Thanks =)

    ReplyDelete
    Replies
    1. Hi Heidi

      I'm afraid it's only been designed to create brand new events and there is no option to update them via the Google Sheet afterwards.

      Kind regards
      Phil

      Delete
  9. Hi Phil - I have same issue. Made a copy of the sheet and the time published the events using UTC +00:00. I am in New York/ UTC -05:00. Can just work around with a mapping, but curious if you know why it would do so

    ReplyDelete
    Replies
    1. Hi

      When you say "same issue" ... as?

      The script uses your local TimeZone so it may be that the copy of the file is stuck somewhere.

      1) Maybe check the Manifest file first for its TimeZone: https://developers.google.com/apps-script/concepts/manifests

      2) Check the 'Spreadsheet settings' from the 'File' menu in the Google Sheet itself (there is a TimeZone option in there too).

      Kind regards
      Phil

      Delete