Google Forms is the office workflow interface you didn’t know you needed.
Google Forms isn’t just another simple survey tool. In its most simple form, it’s a free data collection tool. With a little know-how, you can unleash the combined power within Google Forms, Google Sheets, and Add-ons to create some powerful workflows to help you get more done in the office.
Things You Can Do With Google Forms
Out of the box, Google Forms allows you to collect data and store each response as a row in a spreadsheet. Here we will show you how you can use the same form to work with that collected data to add information to an existing row, or trigger a process.
This is useful in any situation where you need to take action on the data you collect, as opposed to just reporting on it. An example of this is when you use a form to collect a request for a service that requires a response, approval, or some other action. Instead of manually manipulating each new request in the spreadsheet, you can reopen the request in your form to perform the next step in your process.
Design Your Workflow
To demonstrate the concept, consider a scenario where you need to create a structured process for receiving, documenting, responding, and reporting on help requests submitted to your team.
Yes, you could use a sophisticated incident management tool for these types of requests. However, this may not be feasible in a small company, or it may just be a way to deal with the ad-hoc type of situations that don’t really require that level of sophistication (or red tape).
To manage this task in a simple way, you can create a solution using Google Forms that achieves the following:
- Creates a new request and captures the data into a Google Sheet.
- Sends an email to the requester, acknowledging that the request was received.
- Sends an email to the support team, notifying them of a new request that requires action.
- Allows the support team to edit the record with the resolution information.
- Sends an email to the requester with the resolution information.
- Records all information in the Google Sheet to allow for reporting.
Build Your Form
- To create a new Google Form log into Google Drive: https://drive.google.com
- Select New > More > Google Forms
Make sure that your Form captures all the data that you will require to support your process. Since you will be emailing notifications, it is important that you capture that contact information for each user in addition to the help request data.
Add Branching Logic
Page branching is the key component in this Form workflow process. It allows you to skip to a specific section of the Form, based on an answer that you provide. Since this Form will be the interface for both new requests and updates, this question will be asked in the beginning, so that you can direct the user to the appropriate section. A requester will only be submitting New Requests and support staff will use the Update Request (Admin Only) option.
To add page branching do the following:
- Click the menu icon at the bottom right of the section
- Select Go to section based on answer
- Click the drop-down menu next to the question and select the proper page destination
Add Input Validation
Obviously, you will want to prevent non-admins from accessing the Admin Only section of the form. This is where input validation comes in handy. Selecting the Update Requests (Admin Only) option will direct you to a challenge question – in this example it’s called passcode. The input validation on this question is set to only accept an answer that contains a specific string.
Keep in mind this isn’t a super secure method but it gets the job done for simple use cases. Just make sure you aren’t collecting sensitive data. Also, make sure not to use your actual password as the passcode; you don’t want to get hacked!
Power Up Your Google Sheet with Add-ons
Now that you built your Form, set up the notification workflow using a Google Sheet add-on called formMule. formMule allows you to send customized and structured emails, based on triggers such as on form submit. Be sure to check out other add-ons that might better suit your specific process needs.
To add formMule to your Google Sheet click Add-ons > Get Add-ons and find it in the list.
Create Your Email Templates
To set up formMule, click Add-ons > formMule – Email Merge Utility > Setup and follow the prompts. For this application, it is important that you enable the send on form submit trigger as well as select the option to log the edit URL for each form submission.
You will be using 3 email templates for this example. The templates should be named logically, to make it easier to edit them in the next step.
Use the drop-down box to switch between the different templates and create the custom email that you want to send, for each of the workflow steps, that were outlined at the start:
The magic here comes in the Merge Tags, that formMule provides. All of the data fields that the form captured are available in tags, that allow you to create the custom templates. In addition to the form data, formMule also adds a tag called Form Edit URL. This URL is a link that re-opens that specific response within your original form. This is the key to tying it all together. When you create the Notify email template, make sure to include this tag, so that the support administrators can update the request using the same form that you created!
Don’t forget to give them the passcode that you created earlier, so they can utilize the update request section of the Form.
Google Script and Event Triggers
When you are thinking about other ways to use Google Forms to solve your process needs, keep in mind that you can also create your own actions that are triggered on form submit by using Google Script.
Test Your Work
You’ve done a lot of work in Forms, Sheets, and Add-ons. Before you go live with your awesome new process, make sure you do a full walk through of your workflow. Validate that each step is working as designed.
Share Your Form
You are all set to impress your boss and coworkers! To share your process, open your Form, and click the Send button. You have a couple options for sharing the Form, pick the one that works best for your situation, and send it to your target audience.
View Your Responses
After your Form link has been sent, you can start monitoring your spreadsheet for responses and analyzing the results. The data will be stored in the spreadsheet that the survey is linked to.
Do More With Google Forms
As we have demonstrated above, Google Forms can do more than help you collect data. It provides a great interface for simple processes and workflows as well. And don’t forget, adding Google Apps Script to the “on form submit” event gives you almost limitless possibilities for automation.
For more advice, check out these advanced Google Forms tips.