How to Collect and Manage Field Data by Combining Google Products with Smartphones
Smart phones are fairly ubiquitous in modern society and it is important for local governments and volunteer groups to realize the potential of these devices.
This article explores one example of how to piece together an inexpensive, yet effective data collection and management system. This example couples the smart phones of data-collecting field personnel with a Google Forms/Google Maps integration to provide all team members access to quality shared real-time data.
Note: In order to complete this activity, the Project Manager will be required to have a Google email (or “Gmail”) account, along with a desktop/laptop and a reliable internet connection. Field personnel responsible for data collection will be required to have smart phone with a data package.
Part 1Creating a form
Every Gmail account is allotted 15GB of cloud storage known as Google Drive. Within Google Drive there are also series of programs, many of which are similar to the Microsoft Office Suite. In order to create the master form, you will be using a program called Google Forms. Access your Drive account and click “New”. Then select “Google Forms” from the “More” sub-folder. Clicking this icon will take you to a blank form. This form is editable at anytime and will automatically update so that anyone accessing the Form from the field will always see the Form in its most current version.
Double click in the "Title" field and enter a name for your Form.
Click the “Add Item” icon and insert the question or data point you wish field personnel to collect in the “Question Title” field. Once this is complete, choose how you wish for field personnel to answer this field in the “Question Type” drop down. If you choose “Multiple Choice”, “Check Boxes” or “Choose From List”, you will be prompted with pre-determined responses from which field personnel can select. Once you have your question looking the way you want it, click the “Done” icon.
Repeat Step #3 until until you are satisfied with your form. Each question can be edited, duplicated or removed at any point by simply choosing the appropriate icon to the right of the question.
Go to the confirmation section at the bottom of the page and double click the text box at the top of the section. Type in the confirmation message you would like field personnel to see once they have successfully submitted a Form.
While in the confirmation section, ensure the “Show link to submit another response” check box is checked. This will allow field personnel to easily complete multiple Forms without having to reload the webpage manually.
Click the “View Live Page” icon at the top of the document. Clicking this icon takes you to the live, operational version of this form. Review the Form for any errors or revisions.
Copy the web address from the address bar of this 'live page" and email it to the field personnel who will be collecting data using their smart phone.
Part 2Data collection
The Google Form that you so diligently crafted is now a powerful data collection tool in the hands of your smart-phone wielding field personnel. First, field personnel will need to access you form by clicking on the hyperlink in your email or selecting it from their browser bookmarks on their smart phone.
Field personnel will populate the form and then submit their findings by clicking the blue “Submit” icon at the bottom of the form.
Field personnel will receive a confirmation message when their information has been successfully submitted. The confirmation message will offer up a link to complete and submit another form.
Part 3Data management
A sister spreadsheet is created along with every new Google Form. Both documents live in the creators Google Drive. These two documents are linked so that every time a Form is submitted by field personnel, the data from the Form is automatically added to the spreadsheet. Since the spreadsheet (or Google Sheet) is cloud based, it syncs in real time with its sister Form. The data can be reviewed, manipulated and graphed in much the same way as you would on an Excel spreadsheet.
Part 4Data mapping
If location is something in your Form that you would like to see automatically mapped from data in your spreadsheet, we will need to complete a few more steps. One useful feature in Google Sheets is the ability to use add-on features created by outside developers. For the purpose of mapping our data points, an add-on named Mapping Sheets is a perfect fit. You can download Mapping Sheets for free by clicking “Get Add-Ons” in the “Add-Ons” sub-folder.
After downloading Mapping Sheets, add its functionality to your spreadsheet by hovering over the “Mapping Sheets” icon in the “Add-ons” tab and then clicking on the “Start Mapping” option. Choosing this option will add a page at the bottom of your Google Sheet called “Data”. The Data page which was added is the page used by the Mapping Sheets add-on to plot your spreadsheet data onto a Google Map. Unfortunately, Mapping Sheets will only allow the user to show limited data on the map. For the most part, it consists of the location the data point, name the location and add a category for this data point. Hopefully they will flesh this add-on out a bit in the future but even in its current incarnation, it is still a very useful add-on.
Now you must tell the Mapping Sheet spreadsheet to automatically populate cells with information from your Google Sheet which is receiving information from your Google Form. To accomplish this, start by selecting the first “Name” cell A2 and insert the following function in the function box: =IMPORTRANGE("[The entire copy/pasted URL for your original Google spreadsheet]", "[The exact name of your Google spreadsheet]!C2:C1000")
The function will remain largely the same for each of these columns but you will need to make sure the range for each column is changed accordingly.
Once this step is complete, you are ready to test your map. Click the “Build” icon on the Mapping Sheets sidebar to the right.
After a moment of processing, the “View” icon should become available. Click the “View” icon to view your mapped data points. Pretty exciting, eh? Note at this point that although the Mapping Sheet cells will automatically populate every time your field personnel submit a Form, you will have to repeat the “Build” process every time you wish to update your map with new data.
Part 5Adding Photos To Your Spreadsheet
In order to create a more robust spreadsheet, you may want consider hyperlinking photos or documents to it for easy reference. This process will likely be performed by the project manager after field activities have occurred during post-processing. For the purpose of this exercise, we will be hyperlinking photos to the master spreadsheet. In order to hyperlink photos to the master spreadsheet, you will first need to make sure that all the photos you wish to use have been uploaded to Google Drive. It is a good idea to group all photos into a folder where they will remain permanently because moving the photos will deactivate the hyperlink. It is also a good idea to use standardized nomenclature for your photos so that they are easily identified.
To insert a new column on your master spreadsheet, right click on the column adjacent to where you want your “photo” column to live and then choose either “Add 1 Left” or “Add 1 Right”. If you would like to create more than one column for additional photos or supporting documentation, repeat this step as many times as required.
Double click on the first row of your newly added column and then type in the name of the column. If you have added multiple columns, repeat this step for each newly added column.
Now that you have added, the appropriate amount of columns to your spreadsheet, go back your photos folder on Google Drive and right click on the photo you wish to use. Select the “Get Link” option. This will generate a new window with a link to the picture. Highlight this link and then copy the link by right clicking and selecting the “copy” option.
Select the cell where you want to place the hyperlink for the photo, right click and then select the paste option. If you wish you can rename the hyperlink to fit your specific format needs. To do this, right click on the hyperlink and select the “edit link” option. Enter the name of the photo as wish it to appear on the spreadsheet in the “Text” text box then hit the “Apply” button.