My company is in the awkward phase of small-transitioning-to-medium-sized. This means we’re small enough to not need (or want to spend money on) huge enterprise-class systems, but big enough to need to automate some stuff.
I’m working on my own small/med biz IT management system, but that’s not quite done yet. In the meantime, we use Google Doc forms to offset some of those needs. Using Google Doc forms, I can set up the form, enforce required fields (this part is the real key), and have the data submitted to a spreadsheet which I can then do all kinds of things with.
I have a New Hire form, for example. This form simply consists of about 10 fields that my team needs in order to set up a new user. Basic stuff like the new hire’s name, desired corporate email address, where they’re going to sit, who they report to, whether we’re ordering them a new laptop or they get an older one, and any special software that my team needs to install on their machine (Photoshop for designers, Axure for UX, etc).
This form doesn’t contain sensitive personal or corporate information, but moving this to Google Docs from the previous “format” of a freeform email has improved efficiency tenfold, since people are human, and they would forget to include information in a freeform email. Using Google Doc forms, they can’t actually submit the form unless they have all of the information I require.
So, great, HR fills in the form, I get notified via email that the form has been updated — and then what? I’d have to copy+paste the new entry into a helpdesk ticket. Ugh. This is slow, clunky, error-prone, and hardly the best use of my time.
What I wanted was to be able to generate a helpdesk ticket using the newly entered content itself, not just the notification that the form was updated. Thanks to a great post by Amit Agarwal, I had a really good start.
- Create a new form in Google Drive (or use any of your existing forms). Open the Spreadsheet that stores the results for your form submissions.
- Go to Tools – > Script Editor and choose “Blank Project.” Paste the code in the gist below over the starter code they provide, and save the project.
- Replace “you@example.com” in the code with the email address you want to send the new form submission content to. For me, this is usually my helpdesk address so that it will automatically create a ticket using the new form’s contents.
- You can change the subject in the sample code to match whatever you want the subject of your mail to be
- From the Resources menu in the Script Editor, choose “Current Script’s Triggers” and set up a new trigger. Replace “On Open” with “On Form Submit” and save the trigger.
- The script will require you to authorize Google Docs to access your Gmail account (for sending the email). Authorize it, and you should be all set.
Now just go to your live form and test it out. I’ve included some screenshots below to show how I’ve got it set up. Below is the majority of my new hire form:
When HR fills in this form, it adds the record to the spreadsheet, but now also emails a ticket to the helpdesk, so my IT guys know they have a new hire coming, and can make sure we have the hardware and time resources allocated to get a new workstation deployed in time. The ticket looks something like this in our helpdesk:
While I wouldn’t recommend using Google Docs/Forms to store any sensitive information, a little creativity can make them really useful for streamlining some tedious and error-prone operational tasks without spending a ton on some horrible, unwieldy enterprise system.
I’ve set up forms that generate emails and/or tickets for new hires, employee terminations (voluntary or otherwise), software license requests, and so on.
It’s far from perfect, but it’s vastly better than a free-form email process that ends up in a lot of back and forth because the data being sent through isn’t normalized or validated.
