Google Sheets to Google Calendar How-To

Every once in a while you get a great idea. The steps seem to be going great, and things are working just the way you would like. Suddenly, you find yourself at a stopping point because the project fell through, and just don’t have interested parties. Yet the things you have learned you would still like for others to know. That is what this is all about. A great script for you to use, to get your projects from a Google Sheet and posted to Google Calendar. This script will place a button for you to press on Google Sheets, and have the information (if labeled properly) create an event for you on Google Calendar.

So let’s begin!

STEP 1: Create your Google Sheet

Make sure your lines are titled properly, you will need this later on. For the example I have created, I listed three columns: 1) Date 2) Event, and 3) Location. Fill out the information for however many events you would like.

STEP 2: Create the script

Part of this project was to create a button on Google Sheets that allowed you to import the events to your calendar. This would save members time so they didn’t have to create individual events each time things are inputted. To create a script, go to “Tools” -> “Script Editor.”

Input the following code:

function onOpen(e){
SpreadsheetApp.getUi()
.createMenu(‘Calendar Options’)
.addItem(‘Import’, ‘addToCal’)
.addToUi();
}
function addToCal() {

var ss = SpreadsheetApp.getActiveSpreadsheet(),
sheet = ss.getActiveSheet(),
data = sheet.getDataRange().getValues();
for (var i = 1, ok = 1, len = data.length; i < len; i++)
{
var date = data[i][0],
event = data[i][1],
location = data[i][2],
dateEvents = CalendarApp.getEventsForDay(date);

for (var j = 0, jlen = dateEvents.length; j < jlen; j++)
{
if (dateEvents[j].getTitle() == event)
{
ok = 0;
break;
}
}
if (ok)
CalendarApp.createAllDayEvent(event, date, {description: event, location: location})
.removeAllReminders();
else null;
ok = 1;
}
}

 

Notes on lines 1 – 6: This portion of the script creates the button that is visible on your Google Sheet that you would press. On line 3, this will create the individual menu (in this case Calendar Options). It will appear as the last menu item, on the same bar as “File,” “Edit,” “Tools,” etc. You may change the part that says ‘Calendar Options’ to what you would like to call it. After clicking on ‘Calendar Options’ in the sheet, you will then see the button ‘Import.’ This can be changed in line 4, where the code says .addItem(‘Import’, ‘addToCal’).

If you go down the code more, you will see this creates an all day event.

To import to your calendar, just click on “Calendar Options,” and then “Import.”

You have now created a button, and eligible scripts to import events from your Google Sheet to Google Calendars, scaling time you have spent significantly. If you would like to dig deeper into the Script Editor and create more sophisticated imports and codes, please visit Google Scripts Guide.

Until we meet again,

TechGuides Unleashed.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s