DroneCamp is IGIS's flagship event of the year. Now in it's 4th year, the program started out as a 2 1/2 day mash-up of several half-day workshops created by Sean Hogan on using drones for research data collection. The curricula bundles essential bits of knowledge on safety, regulations, data processing, and remote sensing, with a large number of practical lessons on equipment, flight operations, and mission planning. Over the years, it's become bigger and more collaborative, with presenters from several UC and CSU campuses. We've also partnered with an industry symposium, so it now resembles something more akin to a academic conference with a core instructional track. Prior to COVID-19, we were planning to return to CSU Monterey Bay for the event in late June, with a target audience of around 100.

The Decision to Go Online. When COVID-19 struck, the DroneCamp Planning Committee had to quickly decide how to respond when it became apparent an person-event would not be possible. By the time we officially canceled the in-person event in early April, registration had already started. We had a couple weeks to decide - do we cancel? Postpone? Hold it virtually?

Given the amount of planning that had already been invested (we started meeting in October 2019), and the fact that only one component (flight practice) would not translate at all to a virtual format, the Committee decided to move forward with an online event. We were admittedly intrigued by the possibilities of an online format, including a potentially larger audience. I was equally excited about the potential for an expanded pool of instructors, as we had been trying for years to get colleagues from distant campuses to participate. We were torn whether to maintain the same 3-day time frame, or spread it out over several weeks as some virtual conferences are doing. We ultimately decided to maintain the same one-week time frame, in part because instructors had already committed to those days. To mitigate Zoom fatigue, we aimed to limit programming to 2-3 hours each morning and 2 hours afternoon. Sessions would be scheduled on the early side to accommodate potential participants on the east coast.

A Need for New Coordination Technology. After committing ourselves to an online event, the details and requirements for what we needed started to hit me. To manage such an ambitious program, we needed a way to coordinate workshop info, software instructions, and exercise materials involving nearly two dozen instructors, and share everything in a clear and organized manner with a couple hundred participants (which in the end grew to over 300). We also needed a way to manage registration for multiple Zoom sessions, including Webinars for the plenaries and concurrent Meetings for the hands-on software workshops.

Having been thru this rodeo before, I knew two lessons for a successful conference. First, the key to a successful collaborative event is not just catering to the needs of participants, but equally if not more importantly your presenters. Second, almost everyone - instructors and participants alike - waits until the 11th hour to pay attention. Hence whatever tool, or set of tools, we used, they had to be user-friendly and as automated as possible so that last minute changes could be accommodated with a minimum of manual steps.

Specifically, I needed to create three information tools:

• An Online Program, consisting of a mix of content from the Committee as well as instructors, that can be be updated quickly, and capable of sharing including a variety of links for slides, data, Zoom, etc.
  
  
• A way for people to sign-up for concurrent workshops, and get individualized Zoom links after cross-checking their registration status.
  
  
• A customized summary of participants generated from registration data from the ANR survey platform

Ideally, we would have had a Learning Management System to use, or at least an event planning platform to build upon. That was not our case, so I had to turn to my go-to tool stack of DIY solutions: Google App Scripts, R, markdown, and Github.

 

The Online Program

An online event needs an online program. DroneCamp in particular needed a program that could be quickly updated before and during the event, was user-friendly for instructors to update their session info, and combined a range of both static and dynamic content including session titles and descriptions, thumbnail images, scheduling info, and links for slides, exercises, datasets, and Zoom.

 

I began with a Google Form through which instructors could enter and update information for their session. For additional functionality, I wrote several Google Apps scripts (a programming language based on JavaScript) to do things like populate multiple choice options from a Google Sheet (e.g., session times), add a column of “Edit Links” in the Google Sheet so I could go back and update the session info, send a custom email notification to the instructor(s) that a session had been updated, send me (as Administrator) an independent notification, and update a Google Sheet cell with the current time that would later help my R script determine whether the website data needed a refresh.

To make life as easy-as-possible for busy instructors, I pre-populated as much of each session info as I could, including draft session titles and descriptions. Knowing that session description would need links to software installation instructions and other details, I created the equivalent of auto-text fields that when rendered by my R script were replaced with values stored on a different tab in the Google Sheet.

Bookdown. To build the HTML Program from the Google Sheets, I turned to R. I could have created the website using Apps Scripts, however session info was just one part of the Program that I had to build, and Rmarkdown is a far more flexible tool for mixing and matching content. For the website “template”, I used the Bookdown package. I was attracted to Bookdown because it creates lightweight output, renders nicely on small screens, and supports offline output formats including ePub (a common e-book format) and PDF. The later two features are particularly useful for in-person conferences, where everyone is checking their phone to see where the next session is. I've been wanting to try Bookdown for a while to see how it would work as a poor-man's conference app, and was not disappointed.

googlesheets4. Reading the Google Sheet content into R was fairly straightforward using the googlesheets4 package, which provides several wrapper functions and handles authentication. Rendering data-driven markdown required some programming techniques with the htmltools package, but I eventually got it to work. For production purposes, I wrote a number of ‘control panel' functions that accepted parameters I could turn on or off for things like making the Zoom links live, share the links to slides and exercises, etc. There were a couple sessions, such as the participants speed talks, where the session info was actually rendered separately, and had to be integrated into the final Program using a custom flag. Other pages, such as the Instructor Profiles (also fed by a Google Sheet), were generated separately but using similar techniques. During the development process, I got a “quota exceeded” error message a few times, meaning I was querying the Google Sheet too rapidly. But I managed to reduce that to almost never by putting the timestamp of the most recent Session update in a Google Sheet that I could access quickly and without authentication.

GitHub. To host the Program, I needed a web server. ANR's Site Builder platform isn't capable of hosting externally generated HTML files, so I turned to GitHub. I like GitHub's ability to keep track of different versions, and GitHub Pages works fine for hosting small websites. RStudio has Github integration built in, which works well with code projects. But I elected to use the standalone GitHub Desktop which makes it easier to see what changes have been made before making a local commit then pushing it to GitHub.

Knowing that GitHub occasionally goes down, or could theoretically throttle our site if there was too much traffic, I created a backup site on an Ubuntu web server that IGIS manages. Fortunately we never needed to use it, but it was good to have a Plan B.

Google Analytics. GitHub provides basic analytics on content access, but since I was mostly using GitHub to serve HTML pages, I could also make use of the more powerful Google Analytics. After saving the required JavaScript file in the right place and importing it into Bookdown, Google started tracking the traffic. The results showed that 1300 users visited the site 5000 times in June, the majority during DroneCamp week.

Page views are just tip of the iceberg from Google Analytics. I don't have a huge amount of experience with Google Analytics, but hope to dig into this some more for next time to see what else I can glean about our users. Another area for future development is implementing click tracking, which Google Analytics supports, to learn more about how people are using the site.

 

Creating a Zoom Registration Dashboard

Probably our biggest headache was managing the multiple Zoom sessions. Our requirements were more complex than your average webinar as we need to:

1. Manage a combination of Zoom Webinars (for plenary talks), and Zoom Meetings (for the hands-on software workshops). To complicate things further, we only had access to 1 webinar license (which we couldn't use the entire time because of other commitments), so had to ask colleagues to schedule webinars for us. We also needed up to 5 concurrent Zoom meetings for demos, meaning 5 different hosts.
   
   
2. Make use of Zoom's registration features, to ensure only registered participants could join and prevent Zoombombers
   
   
3. Provide a mechanism for participants to sign-up for concurrent workshops, and receive individualized Zoom links for the appropriate session after cross-checking their registration status
   
   
4. Minimize additional registration steps participants had to complete, including registering for multiple Zoom meetings.

Biggest Challenge: Managing Zoom Meeting Registration. In the 3 months leading up to DroneCamp, we learned a lot about Zoom's dizzying array of dials and levers for meetings, webinars, user accounts, and the Zoom client. My biggest challenge was how to register 300+ people for roughly 8 concurrent Zoom meetings, preferably without asking them to click on individual Zoom registration links. This was a fairly big hurdle because unlike Zoom webinars, the Zoom website does not provide a way to upload a CSV file of registrants for a Meeting. Additionally, I needed a solution that would cross-check our DroneCamp registration list, and send individualized connection links. All of this had to be done as efficiently as possible to cater for the dozens I knew would not read any of our emails until after the opening session, and accommodate people who would inevitably change their mind after the course started.

 

Strike 1: AutoHotKey. I first turn to a tried-and-true automation utility called AutoHotkey. AutoHotkey is the equivalent of writing batch scripts for Windows, and in theory can automate any repetitive process you would normally complete with key strokes and mouse clicks. I use AHK a lot, for everything from simple auto text in weird places, to inserting machine readable tags in my calendar program for Project Board tracking. I spent an entire Saturday developing a script that would loop through a CSV file, fill in a Zoom meeting registration web form, and 'click' the submit button. It was working brilliantly and I was very pleased with myself, until that darn Captcha widget finally figured out it was a program and not a person filling in the form. Once that happened, it would ask me to solve a puzzle before submitting the entry. This was still a lot faster and more accurate than typing in the web form by hand, but I knew it wouldn't work in a production setting.

 

Strike 2: Third Party API Integration Services. Next, I turned to the Zoom API, beginning with the dozens of third party integration services (e.g., automate.io, integromat, appypie.com, hull.io, tray.io). These platforms serve as the connective tissue for all manner of online services and web apps, because they help you integrate your various web APIs (like Google Sheets and Zoom). These platforms are not free, but they're easy to set up and use so if I found one that would get the job done it would be a small price to pay. Reading through the details however, I discovered they mostly supported Zoom webinars, not meetings, and few if any of them supported registration management. Another dead end.

 

Success at last: A Custom Zoom App. My final play, which I was hoping to avoid because I knew it was going to be a lot of work, was to create a custom Zoom App, and write the code to drive the Zoom API myself. Zoom's API is fairly well-documented, so I wasn't worried that I could figure out how register participants from Google Sheets or R if I could just get into the API.

But the authentication step is daunting. The simplest way to authenticate a custom Zoom App is creating a Java Web Token (JWT), which is the equivalent of sending a password. But that option was not available to me because our organizational ANR Zoom account is locked down for security reasons and I'm not an administrator. Thus I had to go with a user authentication approach using an OAuth protocol, which requires programming a complex ‘dance' between login page, redirects, IP white lists, and secret client keys and tokens under the hood. One would think there would be ample examples of authenticating Zoom's enormously popular API from Google's enormously popular Google Apps platform, but such is not the case. After a couple of late nights of stringing together various chunks of sample code, and playing whack-a-mole with cryptic error codes and configuration settings, I finally got it to work. I knew I could now create custom menus in Google Sheets to schedule new Zoom meetings, define their properties, and register people for them.

The next step was to integrate this new capability into a Google Sheets app for DroneCamp. The Google Sheet serves as the 'hub' for several Google Forms, our registration data, and Zoom. It uses conditional formatting, a whole bunch of VLOOKUP and IMPORTRANGE functions, and a Custom menu with a sidebar GUI to manage the Zoom functions. I called the result the Zoom Dashboard.

 

Each column of the Zoom Dashboard represents a Zoom meeting, and each row represents a participant. When a DroneCamp participant submits the Concurrent Session Sign-up form, a script runs putting a ‘1' in the corresponding column of the Zoom dashboard. The Zoom dashboard also had a copy of the ANR Registration Survey, and a combination of VLOOKUP functions and conditional formatting rules to flag people who did not have a matching registration. As Administrator, I could quickly check the registration status of people who requested a particular workshop, change the ‘1' to another code if needed, and then when everything was good run a menu command to actually register people for the meeting.

The key to creating an interactive Google Sheets App is using sidebars to present the user with a GUI. I had never programmed sidebars before but have seen them used a lot including many Google Sheets Addons. After another night of reading documentation and programming, I eventually figured it out. Behind the scenes, a sidebar is simply an HTML template with form elements that are configured to execute Google Apps functions. This gives you enormous flexibility both in terms of layout as well as functionality, pretty cool.

The system worked fairly well, and the vast majority of participants got their Zoom links quickly and in ample time. Last minute changes in workshop preferences were dealt with relative ease. It took less than a minute to review new workshop requests, validate them, and run the registration script. A small number of participants had recurring hiccups due primarily to problems on their end including, i) registering with multiple email address despite being told repeatedly not to do so, ii) making typos in their email addresses (shockingly common), or iii) failing to take the recommended steps to prevent Zoom confirmation emails getting flagged as spam. Our phenomenal PSU staff were the frontline dealing with questions about Zoom; but I intercepted many of these issues by first flagging problematic entries with conditional formatting rules on the Google Sheet, and then investigating them on a case-by-case basis.

Other features for the Zoom Dashboard that I ran out of time to implement included writing a script that would extract a participant's individual Zoom links for multiple meetings and send them in a single email. That would be extremely useful for an event like ours with so many sessions. I also had plans to write an auto-reminder email for Zoom meetings, another feature that Zoom only provides for Webinar licenses but can be done via the API.

 

Summary of Participants

We used the in-house ANR Survey System for registration and payment. Similar to platforms like Eventbrite, the ANR Survey Tool provides a highly customizable registration form linked to multiple payment options (including inter-campus transfers). Unlike Eventbrite however, this older tool doesn't have a built-in report generator, nor an API providing access to the underlying data. Instead, you manage registrations through a web interface, which works well, which includes a download option to get the results as an Excel file.

 

We had no idea how many people would sign-up, so I wanted to create a HTML summary of registered participants for our instructors and planning team to stay abreast of registrations as they trickled in. I knew we'd get people from all over, so I wanted the summary to have an interactive map showing participants' locations, as well as various summaries about their organizations and titles. I also knew that I would have to update this summary on almost a daily basis, so I needed the summary to be generated as automated as possible. There was no getting around having to manually download an Excel file, but my goal for everything after that was to do it "at the click of a button".

The Engine: R. R was a natural choice for the engine generating the participants summary. R can easily import Excel files (using the readxl package), and has a powerful set of functions for munging and summarizing tabular data (with dplyr primarily). Rmarkdown does the heavily lifting of generating HTML files, and supports a good number of Javascript libraries like Leaflet and DT that provide interactivity and visualization. But there were still a number of details I needed to figure out.

Geocoding. We didn't ask people on the registration form to enter their longitude–latitude coordinates, which meant I had to invoke a geocoding engine. However, I didn't want to geocode a participant more than once, both because it costs ‘credits' and takes a couple of seconds. I also needed a way to ‘help' the geocoding engine when the participants location info was incomplete (e.g., the name of a university, but not the city or zip code).

To solve these challenges, I deployed a three part strategy. I first checked if the participant entered a valid zip code, in which case I simply looked up coordinates using the venerable zipcode package. If that failed, perhaps because user didn't enter a valid zip code or lived outside the USA, I turned to Google's Geocoding API. Google has one of the best Geocoding engines out there. The other strong option was ESRI, but I went with Google's engine because Google allows you to create an API key that doesn't expire (ESRI API keys expire after two weeks), and Google's geocoding service is also fairly easy to use in R via the ggmap package (ESRI requires custom code). All of Google's API services require a billing account, however the monthly free quota is fairly generous so I wasn't worried about incurring costs as long as I saved the geocode results so I wouldn't have to make the same call repeatedly. If a successful geocode was found, either from the zip code package or a call to Google, I cached the results in a csv file linked by the participant's email address.

A small but significant number of cases could not be handled by either of the above approaches. Either the registrant omitted multiple locations fields, or my code didn't parse an international address correctly. These cases were flagged and saved in yet another CSV file. After each render, my script reported the number of unsuccessful location matches, telling me I needed to go look at the CSV file and if possible enter an ad-hoc geocoding string based on other information in the record. This worked in nearly all cases.

Other data requirements for generating the participant summary included identifying and omitting people who had dropped out (because these are not deleted from the Excel file), and not double-counting people who registered more than once (more common than you might think). These were handled using more external CSV files and functions from the dplyr package.

Creating the Map. Once the data were cleaned, I had a couple of options to generate the map in the summary page - static or interactive. The number of points to display was modest, so I went with leaflet. There are at least three well developed R packages that have functions to render map data in leaflet, but I went with the standard leaflet package which exposes the most dials and levers.

Generating the tabular summaries of registrants by domain type and organization was relatively straightforward using Regex expressions to parse those fields. The ‘title' field was very eclectic, as you'd expect, nevertheless some interesting patterns started to emerge that I wanted to capture. Manually putting titles into different categories is normally what I'd do, but registration was happening too fast so I needed a more automated solution. I decided to use a word cloud. I had never generated word clouds in R before, but after a few hours of reading forum posts and playing with packages, I settled on the wordcloud2 package with text cleaning functions from the tm (text mining) package. A nice thing about wordcloud2 is that the output is interactive (hover over a word to the effect), and every time you refresh the page it redraws the word cloud with a different color palette. Thank you to the amazing community of open source programmers who over many years have developed word cloud, leaflet, and all the other amazing open source plugins for the web!

 

Reflections and Lessons Learned

My homemade event technology solutions for DroneCamp using open-source software performed extremely well, with surprisingly few problems. In the process I learned a tremendous amount about integrating Google Apps, R, and Zoom that I will be able to draw upon for future work. Cracking the OAuth authentication dance and Google Apps sidebars were perhaps the biggest breakthroughs, which now gives me a way to automate tasks involving any of the dozens of web API services out there, including Google Apps, Zoom, DropBox, Slack, and many, many others.

I'm very cognizant of the fact that I'm the beneficiary of an enormously well-developed ecosystem of open-source tools. The ability to import and munge data with just a few lines of code, and present it in compelling HTML based widgets like leaflet and word clouds, is simply remarkable. I and everyone who benefits from my work stand on the shoulders of the legions of open source developers who have toiled for years and years to build and share these remarkable tools. Next time you encounter a popup window inviting you to send a small donation to support an open-source platform or buy a coffee for a developer, please consider this!

There are also a few things I would do differently next time, or advice I would give those with similar requirements. Some of the main ones include:

The value of a LMS. Many of the tools I was forced to build from scratch are features you would find in a Learning Management System (LMS). Perhaps the most valuable feature that would have been really nice to have is a one-stop-shop dashboard for participants. Most LMS's require users to create accounts, which by itself would have resolved a host of challenges regarding multiple emails, cross-checking registration status, distributing Zoom links to only those who need them, etc. LMS's also facilitate sharing course materials like slides and exercises, and give you more options to manage access.

Zoom User Accounts. If we were going to use Zoom again, I would definitely turn on user authentication. This simply means participants need individual Zoom accounts (free is fine), and use the Zoom app to join meetings and webinars. User authentication simplifies managing Zoom links, because upcoming Webinars and Meetings appear in the app. It also simplifies sharing recordings, and gives you better analytics. We initially discussed user authentication but shied away from it so as to not put further demands on participants. That was well-intentioned, but in hindsight there would have been more advantages than disadvantages for both participants and organizers.

Alternative Registration Systems. Linking Zoom meeting to a payment system doesn't just incur credit card fees, it also comes with workflow costs. Either you develop ad-hoc tools and protocols (as we did) to cross-check Zoom and event registration, or you adopt a 3rd party registration system like Evenbrite that integrates both payment and Zoom registration. All of these options come with tradeoffs that need to be incorporated into planning and support efforts.

Simpler, smaller, and/or free trainings can be conducted quite satisfactorily using just Zoom Meetings, but it helps to have a good understanding of Zoom's myriad of settings. Zoom's built-in registration system is actually quite configurable, something we didn't take advantage of. You can add your own questions and branding elements to the registration form. For trainings that only involve one or two meetings, that might suffice.