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 (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. |
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.
Updates
07/11/2022 - now includes the option to set the Event Colour. Added as an extra column within the Google Sheet.
File DownloadDownload the Bulk create Google Calendar events with optional Google Meet sheet 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
Here is a link to the Bulk create Google Calendar events with optional Google Meet code blog post.
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
ReplyDeleteThanks
Anto
IT Director
Overseas School of Colombo
Hi Anto
DeleteThank 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
Hi Phil,
ReplyDeleteThank 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.
Hi
DeleteThank 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
Hello Phil ..
ReplyDeleteReally 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?
Hi
DeleteThank 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
Thank you Phil for different link, there were very useful info but not much info about getting google meet recording info.
DeleteI 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.
Hi
DeleteAs mentioned it isn't an option from Google I'm afraid.
Kind regards
Phil
Very cool script!
DeleteI 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?
Hi
DeleteThank you!
No it should use the TimeZone from wherever it is being run from - what TimeZone are you in?
Kind regards
Phil
Phil,
ReplyDeleteHow can I use this tool to create All day events?
Hi
DeleteI'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
Hey! I used this function:
ReplyDeleteif(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?
Hi Gitika
DeleteThe 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
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.
DeleteIn 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?
Hi Gitika
DeleteThat'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
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.
DeleteThis 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?
Hi Gitika
DeleteThank 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
Hi Phil! First of all, thanks for all your help. I am really grateful for it.
ReplyDeleteI 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?
Hi Gitika
DeleteYou'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
Hey Phil
ReplyDeleteSo, 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.
Hi Gitika
DeleteAs 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
Hi Phil, this is a pretty cool tool! Question-
ReplyDeleteHow 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 =)
Hi Heidi
DeleteI'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
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
ReplyDeleteHi
DeleteWhen 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
Yes, #2 was the solution for me. I wonder if copying from your template version causes the copied sheet to default to UTC +00:00.
DeleteMore importantly, THANK YOU for this fantastic tool. It saved me so much work, and it was so satisfying to sit back and watch it populate my calendar for me. Really appreciate your sharing this.
Hi Michael
DeleteThat's a really good point - the Apps Script code gets the TimeZone from the Google Sheet. I'll update the blog post with this step to prevent other people from getting caught out by it, thank you.
And thank you for your nice comments! I'm glad it's been able to help you out.
Kind regards
Phil
Yes! I came here for this exact same issue. Solution was perfect.
DeleteThanks so much for this. I've been trying to teach myself Google Apps Script to create something that does EXACTLY this. You have made my life so much easier.
I'm still going to try and learn how to do it, but this is amazing.
Excellent Michael, glad you were able to get sorted.
DeleteThank you for the nice comments.
Kind regards
Phil
Hi is it free to use?
ReplyDeleteHi
DeleteYes it is.
Kind regards
Phil
Thank you for your work on this - brilliant, it is going to save so much time.
ReplyDeleteThank you very much!
DeleteKind regards
Phil
I'm putting in bulk dates and im coming back with this error
ReplyDeleteProblem with 'getSpreadsheetData' Function:
Function 'eventsSheetLastRow' returned: -2
The log says:
_The number of rows in the range must be at least 1_
Hi Brad
DeleteAre you formatting the dates correctly as detailed in the Column headings? With only 1 date per cell?
Kind regards
Phil
Hi Phil, thank you for this, it will make our job easier.
ReplyDeleteHowever I am encountering error when running the script:
Problem with 'createEvent' Function API call:
GoogleJsonResponseException: API call to calendar.events.insert failed with error: Invalid Value
I have set the timezone to mine (+8) and the dates are correctly formatted.
From the Google Workspace event log, I see that it is creating a event called "create event test" and then deleting it. My event name is not that, so I assume your script is testing before it creates the actual event?
Please advise what can I check?
thank you
Vijay
Hi VJ
DeleteThank you.
Annoyingly (or I'm yet to figure out a way to find it) the Calendar API does not give much back re exactly which bit of data it does not like!
So you have set the relevant timezone within the Google Sheet properties?
It does create a very quick test event (and then deletes it) to ensure you have entered a valid Calendar ID on the 'Welcome' sheet basically. Have you tried creating a super simple event with just a Title, Description and the timings? (So no Guests, etc)?
If you wanted to use the Contact Form on my blog to share a version of you file to see if I can spot anything then I could try to take a look for you. I do have somebody successfully using this in Hawaii currently!
Kind regards
Phil
Thank you Phil, yes I tried a very simple event and on two different accounts. I have sent you the file through the contact form.
Deletethank you
Hi VJ, I have nothing through my contact form ...
DeleteHi Phil, I am having trouble submitting the contact form. The message states "sending..." but nothing happens. Tried on multiple browsers.
DeleteHi VJ
DeleteThank you for sharing your Sheet. I see the error now - and it's a typo on my part (sorry about that). I recently added the ability to set the Event Colour and as it's not a 'required' item I believed it would accept a blank value. Unfortunately it does not, so I have been able to default it to '0' which will use your default Calendar colour (hence people do not have to keep changing it if they do not wish to).
If you take a fresh copy of the Google Sheet linked in this blog post as I have updated the file for everyone.
Ta
Phil
Thank you so much, Phil. It is working now. Appreciate all you have done to make our jobs easy and efficient.
DeleteThanks for this. You have literally save a humoungous work for us. I was wondering if this can be made recurring? Please suggest work around with script if possible?
ReplyDeleteYou're very welcome!
DeleteI have not directly created recurring events via the API, but I can see the options exist (there are a lot of them based on frequency, intervals, end dates, etc). I do freelance work if it's something you specifically require (https://www.pbainbridge.co.uk/p/freelance.html) as it looks like it would need to be tailored.
Hello, any way to show the Meet links on the spreadsheet? Need to share these links and need them all in one place. Thanks for creating this!
ReplyDeleteOk sorry for the repeated comments but I wanted to let you know that I figured out a solution to getting the Meet link instead of the Event link. In the createEvent.gs section I modified your code on lines 256-261 and set it to this:
ReplyDelete// capture Meet meeting code
var eventMEET = request.conferenceData.conferenceId;
// run Function and paste Meet meeting code into spreadsheet as clickable link
var eventMEETLink = '=HYPERLINK("meet.google.com/"&"' + eventMEET + '")';
pasteIntoSheet((row + 3), eventIDCol, eventMEETLink, '#000000');
Hope this helps!
Excellent! Thank you for sharing.
Delete