How to create an automated calendar with Google Apps Script with open source on top

Speaking at conferences can be hard. This tutorial aims to help you better manage the "administrivia" of submitting to calls for proposals (CFPs) and speaking at conferences.

I primarily speak about open source tools or open organization concepts, and I think it's really important to share with others. If you're interested in speaking, check out some of the conferences mentioned in this article, read this awesome resource, and feel free to contact me if you need any tips or help. It also might help to read some of these great tips for organizers, as they may help you better formulate a proposal that can get accepted.

In addition to being a speaker, I'm on the CFP review board for DevOpsDays KC, so I know a little bit about the topic curation side of the CFP process. Pro tip: We found this past year that someone was A/B testing their titles with us, and our ratings were affected by the title. The descriptions were identical.

Managing conference submissions

I'll be submitting to a lot of conferences both in and out of the tech industry. This will be a lot to maintain mentally. I've already spent weeks creating the proposals and making them just right, but how will I manage all my submissions? It would be great if everyone used papercall.io, because I'd just track them there. However, we all know there will never be a single standard. Some conferences have a dashboard to track your submission, others send an email, and others do nothing. The last ones are the hardest to track.

So, I decided to start with a simple Google Form and autogenerated Sheet. I added the types of information I thought I'd need at different stages after the submission. I added entries for event name, location, start and end dates, CFP end date, event and CFP URLs, speaker notification date, and a list of the talks I submitted. I will fill this out as I complete each CFP.

This was going to be the end of it, but I realized it would be pretty hard to track all of this throughout the year without something like a calendar. My first thought was to create something from scratch in JavaScript, but that was just the developer in me. After I subdued him, I decided there was probably an easier way.

The googling commenced. Zapier has really good SEO in its tutorials, so I decided to use it to connect my Google Sheet to my Google Calendar. I made a couple of special calendars—one for tracking conferences where I haven't been accepted and another for the ones that have accepted my proposals. I also created a separate sheet so I could add extra columns, like the acceptance status and which talks were accepted.

The integration with Zapier was pretty easy once I got the hang of it. I configured a complex Zap that created different types of calendar events for each stage of the process, including a separate event for the CFP end date. It was working great, then I realized I was using a trial version and it would all be gone in two weeks. Not good! This isn't my job, so I don't want to pay for something I can build (and write about).

So, the googling began again. I found Google Apps Script (GAS) and some Google Sheets and Calendar API libraries. As I started to work with some of the libraries, I realized that it was going to take a decent amount of effort just to get authentication working properly, and then I'd have to run it somewhere.

Even though GAS isn't open source, I decided to use it and make everything I do on top of it completely open and free. I started by navigating to my Sheet and clicking on Tools and then Script editor This opened an IDE where I could get started.

There was quite a bit of trial and error, but I ultimately ended up with a single small script that does everything Zapier does and more. It's also completely free for my limited needs. It runs every five minutes and is completely idempotent. I also don't have to run the script—Google does that somewhere without me knowing anything else.

Building my calendar automation

Before we get started, there is a caveat to this automation. I'm not great with Google Sheets, so I couldn't figure out how to update the Results sheet when the Responses sheet is updated. This means I have to go in and copy the rule in the current cells to any new cells that need to be filled in.

Also, I recommend this Chrome extension that allows you to use a GitLab repo for your scripts. I learned about it at the end of my development then used it immediately to put my finished code into a versioned repo. You can now fork from my repo to get my script into your GAS editor from a repo you control.

Now I'll walk through the code to give you a better understanding of what's happening and why before showing the finished product.

function createTrigger() {
  ScriptApp.newTrigger('updateEvents')
      .timeBased()
      .everyMinutes(5)
      .create();
}

This first function creates a trigger that calls the updateEvents method. This script is set to run every five minutes, which is fine, as it is idempotent. Be careful not to edit the script in a way that removes the idempotency, which means you'll need to delete hundreds of events. The good news is that you can quickly delete all events on the calendar and revert to the idempotent script to return to a fully functional state in just minutes. You can also reverse the check in the deleteEvent function and the script will delete one entry per event each time the script runs. This helped me slowly walk back a mistake during development.

// Deletes an event if it exists
function deleteEvent(event) {
  if (typeof event != 'undefined') {
    Logger.log("Deleting event %s", event.getTitle())
    event.deleteEvent()
  }
}

Let's skip the next function and head to the third function, deleteEvent. This function takes a CalendarEvent as an argument, checks if it exists, and deletes it if it does. This is just a small helper script that cleaned up my code quite a bit.

// Get Sheet and both Calendars.
  var sheet = SpreadsheetApp.openByUrl("https://xxxxxx").getSheetByName("Results")
  var submittedCalendar = CalendarApp.getCalendarById("xxxxxxx")
  var acceptedCalendar = CalendarApp.getCalendarById("xxxxxx")

Now, let's tackle the big function, updateEvents. This performs all the logic. First, I need to establish authentication into each calendar and the sheet I'm using. This was very quick and easy, and it's handled in the first execution of the script. You can get the sheet URL by navigating to your Sheet and grabbing the URL from the address bar. You can get the calendar ID by following these instructions. I suggest not using the name for either, as you may want to change it later and forget to update references.

  // Get data from Sheet
  var data = sheet.getDataRange().getValues()
  
  var events = []
  
  // This loop parses the data from the spreadsheet and adds it to an array
  for (var i = 1; i < data.length; i++) {
    // Skips blank rows
    if (data[i][0] == "") {
      break
    }
    
    // Gets the speaker notification date if one exists or sets it to the start date of the conference
    var speakerNotificationDate = new Date(data[i][4].getTime() + 2.88e7)
    if (data[i][5] != '') {
      speakerNotificationDate = new Date(data[i][5].getTime() + 2.88e7)
    }
    
    // Uses the first row, headers, as keys with the values being assigned to each. Then the object is pushed onto the array.
    var event = {}
    event[data[0][0]] = data[i][0]
    event[data[0][1]] = data[i][1]
    event[data[0][2]] = data[i][2]
    event[data[0][3]] = data[i][3]
    event[data[0][4]] = new Date(data[i][4].getTime() + 2.88e7) // Update the time to 8 a.m.
    event[data[0][5]] = speakerNotificationDate
    event[data[0][6]] = new Date(data[i][6].getTime() + 2.88e7) // Update the time to 8 a.m.
    event[data[0][7]] = new Date(data[i][7].getTime() + 7.2e7) // Update the time to 8 p.m.
    event[data[0][8]] = data[i][8]
    event[data[0][9]] = data[i][9]
    event[data[0][10]] = data[i][10]
    events.push(event)
  }

Once it's done authenticating, I continue getting all the data from the Sheet. The script checks to ensure there's an event name; if not, it won't continue processing that row and will go to the next entry. Each row is parsed into an object with the key being the column header and the value being the value from the cell in the row being processed. I do a little cleanup on dates and empty data to ensure the rest of the script functions properly.

I experienced some issues with how my events were displaying on the calendar, and I found it best to set the start time to 8 am and the end time to 8 pm. Avoiding the hour of 0000 seemed to be ideal in solving a lot of the problems I was experiencing. The script also ensures the speaker notification date is entered or it sets the date to be the start date of the event..

if (events[i]["Accepted"] == "Yes") {
      if (typeof acceptedCalendar.getEvents(events[i]["Start date"], events[i]["End date"], {search: events[i]["Event Name"] + " - " + events[i]["Talks Accepted"]})[0] == 'undefined') {
        acceptedCalendar.createEvent(events[i]["Event Name"] + " - " + events[i]["Talks Accepted"], events[i]["Start date"], events[i]["End date"], {location: events[i]["Location"], description: "Event URL: " + events[i]["Event URL"] + "\n\nCFP URL: " + events[i]["CFP URL"] + "\n\nTalks Accepted: " + events[i]["Talks Accepted"]}).setColor(CalendarApp.EventColor.GREEN).addEmailReminder(40320).addPopupReminder(40320)
        Logger.log("Created accepted event on accepted calendar")
      }
      
      if (typeof submittedCalendar.getEvents(events[i]["Start date"], events[i]["End date"], {search: events[i]["Event Name"] + " - Accepted"})[0] == 'undefined') {
        submittedCalendar.createEvent(events[i]["Event Name"] + " - Accepted", events[i]["Start date"], events[i]["End date"], {location: events[i]["Location"], description: "Event URL: " + events[i]["Event URL"] + "\n\nCFP URL: " + events[i]["CFP URL"] + "\n\nTalks Accepted: " + events[i]["Talks Accepted"]}).setColor(CalendarApp.EventColor.GREEN)
        Logger.log("Created accepted event on submitted calendar")
      }
      
      deleteEvent(submittedCalendar.getEvents(events[i]["Start date"], events[i]["End date"], {search: events[i]["Event Name"] + " - Submitted"})[0])
      deleteEvent(submittedCalendar.getEvents(events[i]["Start date"], events[i]["End date"], {search: events[i]["Event Name"] + " - Rejected"})[0])
    }

Then the script loops through each event to add it to the appropriate calendar. You can see an example of adding and removing an event above. There's an attribute on the table called Accepted that is filled in with the word Yes if the conference has accepted any of my submissions. If that is true, then two calendar events are created, one on each calendar.

Since I have two separate calendars, I have an easy view of all the conferences where I'm speaking. For the Accepted Calendar, I first do a search for the event on the calendar and return the first item from the array that Google Calendar responds with. I'm assuming that if there is an event, then it is the one I want. I'm using a very specific search that includes the accepted talks, so it should only match the event. If there's no event, then I create a new event with all the pertinent information, ensuring the color is green (since that's the color of events on the Accepted Calendar). I also have notifications set for four weeks in advance of an event. This helps me ensure my presentation is ready and all of my travel is booked.

An event is added to the Submitted Calendar in exactly the same way, however, " - Accepted" is added to the conference title, since it's on a calendar with a lot of other similar events. Then I delete any events with the " - Submitted" and " - Rejected" keywords in the title because they won't be needed anymore.

If the Accepted attribute is set to No, I create a rejected event on the Submitted Calendar. I also need to delete the other three possible calendar entries. Then if there is any other value or no value in the Accepted column, I can assume it has been submitted but not accepted or rejected yet. So, I create a " - Submitted" event and delete any possible rejected or accepted events.

  if (typeof submittedCalendar.getEvents(events[i]["CFP end date"], events[i]["Start date"], {search: events[i]["Event Name"] + " - CFP"})[0] == 'undefined') {
      submittedCalendar.createAllDayEvent(events[i]["Event Name"] + " - CFP", events[i]["CFP end date"], {location: events[i]["Location"], description: "CFP End Date: " + events[i]["CFP end date"] + "\n\nSpeaker Notification Date: " + events[i]["Speaker notification date"] + "\n\nEvent URL: " + events[i]["Event URL"] + "\n\nCFP URL: " + events[i]["CFP URL"] + "\n\nTalks Submitted: " + events[i]["Talks Submitted"]}).setColor(CalendarApp.EventColor.YELLOW).addEmailReminder(10080).addPopupReminder(10080)
      Logger.log("Created CFP event")
    }

Last, I ensure there's an entry for each CFP. This is created on the published end date for the CFP, the color is changed to yellow, and notifications are set for seven days prior to the deadline. Although I've likely already submitted to the conference, sometimes something happens where I want to change a submission or I decide I want to submit another proposal. Also, I can add an event without submitting any talks (yet) so I'll be reminded to submit before the CFP closes. Currently, adding another talk to an event won't update that event unless I delete the event manually in my calendar. This is a pretty rare occurrence, so I'm not too concerned about it.

Getting all of this together shouldn't take long, and I think it's really going to help me manage my schedule better. Here's a look at my April calendar, with one accepted conference so far and several that are waiting for the CFP to close. Also, you can see that there are two colors for Submitted (an artifact of using Zapier first). This shows that the search is working correctly and only matching on the title. I could change this if I want to spend time on it, but it doesn't bother me and shouldn't affect you.

If you want to contribute back any enhancements to this project, please feel free to submit a Merge Request on GitLab.

Writing winning proposals

I've spoken at conferences for the last few years. My first year I just threw out a bunch of random proposals that were custom for each event and tried not to duplicate any. This meant I barely applied to any, and I was basically rejected by all of them. I think I spoke at one conference that year.

I decided that wasn't optimal. None of my proposals were all that good because I couldn't spend much time on any one of them. I also became aware that this is a lot like sales, in that I'm going to mostly be rejected. So, I decided to create three proposals, slightly customize them for each CFP, and customize the actual presentation after the proposal has been accepted. I change my presentations quite a bit throughout the year, based on each conference's focus and time constraints, so I basically never present the same thing twice.

I think I spoke at 10 conferences that year, including All Day DevOps, Jenkins World, All Things Open, and LISA (to name some of the bigger ones). There were also many smaller conferences with great organizers and communities that I'd recommend, like GlueCon, WeRISE Atlanta, BSidesKC, and RevolutionConf. You can see the slides or watch videos through my website.

My strategy of using just three proposals allowed me to submit to many more conferences to find the best fits for myself and the conferences' organizers. It worked pretty well, and I probably submitted to 50 conferences. Keeping track of them all was not happening. I was constantly worried I'd miss an email about getting accepted or accept to two conferences that happen at the same time. Luckily, I don't think either happened.

In 2018, I decided not to travel as much, so I only spoke at a couple of conferences, and they were all bespoke submissions because I wasn't submitting to many. I had closer to a 50% acceptance rate.

Moving into 2019, I plan to present a lot more, so I need a strategy. I've again decided to go with a small set of talks. This year it will be seven proposals, due to a lot of new knowledge I've picked up working at the NAIC and helping the organization through a large technology and culture transformation. I also hope to speak about risk in a way that includes the entire product pipeline and how it can be used to fund transformations, which is convenient as my new role is Chief Architect of RSA Archer.

Read next