As of March 2024 this tool is now available as a Google Workspace Marketplace Add-on.
The following Google Apps Script tool is designed to bulk create Google Calendar events with optional video conferencing (Google Meet or Zoom). It is an enhanced version of this blog post for creating events with optional Google Meet. As it now includes Zoom there are a number of extra steps such as additionally setting up a Zoom Marketplace App.
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. |
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.
Caveats of the tool
- You can create up to 100 Zoom meetings within a given 24-hour period. This limit is imposed by Zoom and resets at 00:00 UTC.
- The tool currently uses the default ‘Event notifications’ set up for the calendar you want to create the events on.
- Currently this is a tool for bulk creating events and cannot yet bulk delete them. You can delete the events in the normal way that you would through Google Calendar and there will be a direct link to it within the Google Sheet upon its creation.
- Currently you cannot create ‘All day’ events - you must specify a start/end date/time.
Instructions
Use these instructions to create the Zoom OAuth App. A couple of points about the one I created:
- It is a user-managed app, meaning it is installed and authorised by users individually.
- Turned off 'Event Subscriptions'.
Time zone
If you have any problems here, there are 2 areas to check:
1) The Google Sheet: File > Settings > Time zone
2) The Script Editor: Extensions > Apps Script > Project Settings > General Settings > Time zone
Updates
07/11/2022 - now includes the option to set the Event Colour. Added as an extra column within the Google Sheet.
File Download
Download the Bulk create Google Calendar events with optional Meet or Zoom here. Please use 'File' > 'Make a copy' for your own version.
As of March 2024 this tool is now available as a Google Workspace Marketplace Add-on.
The Code
Hi, thanks a bunch, thanks for sharing. I was wondering if you try to upload like 5000 events will hit an API limit ?
ReplyDeleteHi Antony
DeleteThank you. Yes there are API limits to be aware of: https://developers.google.com/apps-script/guides/services/quotas
Kind regards
Phil
Thanks
ReplyDeleteAbsolutely brilliant!!
ReplyDeleteThank you!
PS: Any way to add/subtract guests from sheets once the events have been created?
Hi
DeleteThank you.
Not with this tool no - it's a one way sync only I'm afraid.
Kind regards
Phil
Great tool! ❤
ReplyDeleteOne question, how can I change the time zone? Currently it's UK time so it doest sync well with my calendar due to time differences
Thank you.
Delete1) Check in the Google Sheet: File > Settings > Time zone
2) Check in the Script Editor: Extensions > Apps Script > Project Settings > General Settings > Time zone
Kind regards
Phil
Works great! Thanks a bunch!
DeleteYou're welcome.
DeletePhil, thank you so much for publishing this tutorial! It is fantastic. 🤩 Glad I finally stumbled upon it. I had been using a tutorial you did several years ago to build my own version, a much leaner app but was struggling with getting the Zoom OAuth to work in mine. Now I'm going to try to merge your version with mine. Mine uses data that I have parsed from my email as I want the appointment data to transfer automatically. It's been a very slow process but I will let you know if I ever figure it out. Your work has been a tremendous help! 🙏🏻🙏🏻🙏🏻 Truly grateful!
ReplyDeleteYou're very welcome, thank you!
DeleteHey Phil,
DeleteThank you for such a quick response! I have removed the validation but now I receive the error message:
“problem with createEvent Function
TypeError: checkDataReturn.endHour.trim is not a function” when running it.
I don’t suppose you have a quick and easy solution for this problem? I wouldn’t want to trouble you any further.
This reminds me of why I put the validation in there.
DeleteAfter some testing I have found the value from the cell is no longer being read as a string, hence the error. You would need to go into the Apps Script code, to the '5) checkData.gs' function and append '.toString()' to lines 32 and 35, so they look like this:
var startMin = spreadsheetData[row][6].toString();
var endMin = spreadsheetData[row][9].toString();
Hey Phil, this is an incredible tool! Thank you so much!!! It saves so much time for me at work. As a person who knows nothing about coding, you provided an easy solution for me to reduce admin.
ReplyDeleteI have set up a sheet with event template, since events at my work are very similar with just a few tweaks.
The only problem I have found is that I can't set the end hour or minute as "start hour +1" or "start minutes + 5" since the validation rules would be showing a warning. I managed to follow this logic with dates, but not minutes/hours.
The reason I need it is because I need to set up a lot of events and they all follow a similar logic: one starting at 10am and lasts an hour, second from 9.45am to 10 am, and third: 10am to 10.15am - so 15 min before and after the first event. Any guidance with this problem would be highly appreciated.
Thank you very much Tsu!
DeleteYou could probably just remove the Data Validation to achieve what you want ... highlight those columns > Data > Data validation > delete for those columns.