For a number of years at the Nonprofit Technology Conference (NTC), we have created collaborative notes for each of the sessions. This is a Google Doc that anyone can access and contribute to before, during, or after the session. We have found it adds great value for those who attended and also for folks who, for whatever reason, couldn’t attend that particular session.
Now, if we only had a few sessions, creating and managing these notes wouldn’t be an issue and certainly not worth your time reading a blog post about it. However, as you can see, we had 120+ sessions this past year, so the process took a little wrangling.
We have always been lucky to have an extremely helpful and engaged community with folks who would volunteer to create a script to handle the process of creating all these docs. This did, however, come with challenges that included the final docs not actually living within our own Google Apps accounts, putting the onus on them to also gather and share the links for all those docs, etc. So this year, I was determined to bring it in-house.
Like many of you, I don’t write scripts; I don’t read them; if I get lucky, I seem to be able to successfully run one every now and then without breaking the Internet. I do understand tools, though, and take a lot of joy in figuring out how to make the free and nearly free ones work for me and make my work more efficient.
The following is the solution I came to for creating, managing, and sharing the collaboration notes for 15NTC. I think there are a few tricks and insights to be shared that can apply to many of things folks in other nonprofits are working on these days.
- Google Spreadsheet
- Zapier/If This Then That
From Titles to Docs
I started with all the individual session titles tracked in a Google Spreadsheet.
Next I used Streak to run a mail merge through my Google Apps email account to send myself an email for each session. The thing about mail merge is that it’s smart enough to automatically remove dupes; in order to send myself all 120+ emails, I used a little trick I found in this very helpful blog post “A Guide to Optimizing Gmail: 30 of the Best Tips, Tricks, Hacks and Add-Ons.”
I created email addresses that looked like this “firstname.lastname@example.org, email@example.com” and so on with the numbers increasing sequentially. It looks like a unique email going out, but Gmail only sees everything before the + when receiving, so they all came to my inbox. I used the drag-and-copy trick in a spreadsheet to create all 120+ unique sequential emails with a couple of joined fields.
Next I used Zapier to run a task based on a tag I applied to each incoming email to create a new Google Doc pulling the relevant info from the email for the title and body content. You can do it as well with If This Then That; there are just different options for formatting when creating the new doc.
From Docs to Shortened URLS
I did have to manually add the links to each Google Doc into a spreadsheet, but then I was able to use an API call within the spreadsheet to automatically create shortened URLs for each. I didn’t even know you could put an API call in a spreadsheet until working on this project.
It took me about 15 mins to create 120+ shortened urls and upload them to our online agenda platform for the conference. I used Po.st for this, as I liked their reporting, it didn’t have an API call limit of 50, and I couldn’t get Bit.ly to work in the new Google Spreadsheets (although I am guessing it could work in Excel).
Big Time Saver
Overall, I would say that, after figuring out what I needed to do, I was able to complete the task from start to finish in about an hour and half, with most of that time spent on the one manual step in the process. The Google Docs now live within our system, we have shortened URLs to gather metrics, and we can use the superpowers of our Community members for other tasks we can’t quite figure out workarounds for yet.