Create Calendar Events from Google Sheet
This app lets you create the calendar events from google sheet. It's helpful for people who are collecting the the event details through a google form and storing it in google spreadsheet. Any sort of events such as Out Of Offices, Vacations, Daily/Weekly/Monthly Tasks can be directly created into the google calendar with just a button click. If configured, It creates overlapping events and also send emails to the participants and Additionally updates the existing event with matching titles.
Step 1: Open Google Spreadsheet
Open google spreadsheet where events are stored
Step 2: Verify Calendar Settings in your sheet
Verify the Date Format and locale is setup correctly on the spreadsheet.
Go to File > Settings
Step 3: Open the CreateCalendarEvents Add-on
Once you click on Open the Event Settings sidebar will be displayed
Events: Click on the drop-down and select the column where event field is referring to in the spreadsheet, In My sheet above it's Col A. It's a required field
Start Date: The Start date is the date and time when event is starting, In the spreadsheet my date format is MM/DD/YYYY HH:MM:SS, You can select based on your locale setting. Select the column which points to start date in the spreadsheet. It's a required field
End Date: The End date is the date and time when event will end up, In the spreadsheet my date format is MM/DD/YYYY HH:MM:SS, You can select based on your locale setting. Select the column which points to end date in the spreadsheet. It's a required field
Description: If there's an Agenda or description of the meeting that you want to share with the participants. It's an optional field. In my sheet it's Col D
Location: The location of your meeting, It's optional. In my sheet it's Col E
Guest: It's a comma separated email list of all the participants of the meeting. It's optional, In my sheet it's Col F
First row Not header: In your spreadsheet if events start from first row then check this box, otherwise we will assume the first row is header by default.
Update Events:
Update event checkbox lets you update an existing events matching title. Let's understand this with the help of an example:
1) In this first example, the title is matching but the start and end dates of existing and new events are not matching
Existing Event: Out-of-Office-Syndy startdate: 12-AUG-2022 00:00:00 enddate: 20-AUG-2022 00:00:00
New Event in Google Sheet: Out-of-Office-Syndy startdate: 12-AUG-2022 00:00:00 enddate: 18-AUG-2022 00:00:00
In this case it will update the Existing event and set it to this:
Updated Event: Out-of-Office-Syndy startdate: 12-AUG-2022 00:00:00 enddate: 18-AUG-2022 00:00:00
2) Another example, The title is matching and also the start and end dates of Existing and New event is matching
Existing Event: Out-of-Office-Syndy startdate: 12-AUG-2022 00:00:00 enddate: 20-AUG-2022 00:00:00
New Event in Google Sheet: Out-of-Office-Syndy startdate: 12-AUG-2022 00:00:00 enddate: 20-AUG-2022 00:00:00
In this case it will not update the Existing event and leave as is:
Updated Event: Out-of-Office-Syndy startdate: 12-AUG-2022 00:00:00 enddate: 20-AUG-2022 00:00:00
3) Final Example, The title is not matching but the start and end dates of Existing and New event is matching
Existing Event: Out-of-Office-Syndy startdate: 12-AUG-2022 00:00:00 enddate: 20-AUG-2022 00:00:00
New Event in Google Sheet: Out-of-Office startdate: 12-AUG-2022 00:00:00 enddate: 20-AUG-2022 00:00:00
In this case it will leave the existing event and create the new event if overlapping event is checked
Updated Event: Out-of-Office-Syndy startdate: 12-AUG-2022 00:00:00 enddate: 20-AUG-2022 00:00:00
Overlapping events
if overlapping checckbox is checked, then the partially or fully overlapping events will be created otherwise not.
1) In this first example, the new event is partially overlapping with the existing event
Existing Event: Meeting-Bluestar-Assoc startdate: 12-AUG-2022 10:30:00 enddate: 20-AUG-2022 11:30:00
New Event in Google Sheet: Meeting-Allparty-Assoc startdate: 12-AUG-2022 11:15:00 enddate: 18-AUG-2022 12:00:00
2) In this second example, the new event is fully overlapping with the existing event
Existing Event: Meeting-Bluestar-Assoc startdate: 12-AUG-2022 10:30:00 enddate: 20-AUG-2022 11:30:00
New Event in Google Sheet: Meeting-Allparty-Assoc startdate: 12-AUG-2022 10:30:00 enddate: 20-AUG-2022 11:30:00