Over the years I’ve picked up some tricks when it comes to event planning and managing tasks. A big example that comes to mind is getting ready for opening reception for art exhibitions. I knew there had to be a way of importing calendar events from a spreadsheet.
Let’s say there are 30 things that need to get done on a 6 month timeline for an art exhibition to execute correctly. To give you an idea on some possible specific tasks…
- Selecting quality artists that show well together.
- Making sure the selecting artists and the gallery are on the same page when it comes to mission and aesthetics
- Making sure artists are available for the show dates
- Selecting specific pieces for the show
- Developing a press release
- Developing online marketing materials such as event postings and social media posts
- Designing a marketing materials for the show and getting those to a printer with enough time to get them mailed out
- Getting refreshments for the exhibition
Now these are some larger and more obvious tasks that you would expect. If you are only planning one art exhibition then you could probably just schedule every item individually.
If you are planning 8 exhibitions per year however you need to be importing calendar events from a spreadsheet. In a realistic week maybe you need to finish marketing materials for exhibition 1, select artworks for exhibition 2, find artists for exhibitions 3 and 4.
Now the interesting thing here is that if all the steps for each exhibition are the same for the most part you can create one master checklists that you use for each exhibition. This is great, however it still involves putting each item into a calendar by hand and possibly risking human error.
With the use of google spreadsheet script however you can just make a custom spreadsheet with the master checklist and have it program each exhibition separately into the calendar.
And if you are utilizing shared calendars In Google Calendar then your team has a razor sharp calendar of all of the scheduled tasks.
Below you’ll find the apps script I used years ago. I believe this still works. I unfortunately couldn’t find the origin of this because it was so long ago. Thanks to whoever wrote this.
var EVENT_IMPORTED = "EVENT_IMPORTED";
var ss = SpreadsheetApp.getActiveSpreadsheet();
function onOpen() {
var menuEntries = [{name: "Import Events in Calendar", functionName: "importCalendar"}];
ss.addMenu("Scripts", menuEntries);
}
function importCalendar() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 6; // First row of data to process
var numRows = 30; // Number of rows to process
var dataRange = sheet.getRange(startRow, 1, numRows, 6)
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var title = row[2]; // First column *3rd
var startDate = row[4]; // Second column *5th
var endDate = row[5]; // Third column *6th
var description = row[3]; // Fourth column *4th
var location = row[6]; // Fifth column *7th
var eventImported = row[7];// Sixth column
if (eventImported != EVENT_IMPORTED && title != "") { // Prevents importing duplicates
var cal = CalendarApp.openByName("PSGExhibitions");
var advancedArgs = {description: description, location: location};
cal.createEvent(title, new Date(startDate), new Date(endDate), advancedArgs);
sheet.getRange(startRow + i, 7).setValue(EVENT_IMPORTED);
Browser.msgBox("Events imported");
// Make sure the cell is updated right away in case the script is interrupted
SpreadsheetApp.flush();
}
}
}And here you’ll see the Google Sheet that the script runs off of. This will give you an idea of what rows are used to do what, columns and so forth. You can tweak the script to match your spreadsheet, or the other way around. This is just one screenshot of the spreadsheet, it actually goes for quite a while with different tasks.
You could probably even get crazier with the scripting. But you can see here that the top 3 rows help populate some of the calendar items below. So the Show Title get’s injected into multiple calendar items. Then when you run the script that all get’s imported to you Google Calendar. Fun stuff.
There may be better ways of importing calendar events from a spreadsheet. Also some people go the other direction with Importing events from their calendar into a spreadsheet. Not exactly sure why you’d want to do it.
I hope someone will find this useful. It sure made my even planning run much smoother, especially when multiple events were getting planned at a time.
