Edit Article

Four Parts:Creating a formData collectionData managementData mapping

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. One way to do this is to design and implement a sophisticated data management system by using free software along with existing hardware to gather and manage data without having to break out the check book to pay for an expensive GPS unit or data management software. This article will explore 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.

EditPart 1 of 4: Creating a form

  1. LOLO128695.png
    1
    Start a new Google 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. 
  2. 8899445566.png
    2
    Name your Form. Double click in the "Title" field and enter a name for your Form.
  3. 765THE4567.png
    3
    Add a question to 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.
  4. 4
    Build your Form. 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.
  5. 9654874.png
    5
    Enter your confirmation message. 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.
  6. 6
    Set the Form to allow multiple responses. 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.
  7. LOJKUHE987.png
    7
    Review the live Form for accuracy. 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.
  8. LLHGTY.png
    8
    Share the Form with field personnel. 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.

EditPart 2 of 4: Data collection

  1. Field Map_birdie.jpg
    1
    Access the Form in the field. 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.
  2. LLKYD234.png
    2
    Complete and submit the Form. Field personnel will populate the form and then submit their findings by clicking the blue “Submit” icon at the bottom of the form.
  3. 359874GHT.png
    3
    Ensure that the Form has been successfully submitted. 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.

EditPart 3 of 4: Data management

  1. 987OUYTIl.png
    1
    Manage your data. 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.

EditPart 4 of 4: Data mapping

  1. 25987JHGT34.png
    1
    Download Mapping Sheets add-on. 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.
  2. LLIKE43194.png
    2
    Add Mapping Sheets to the master spreadsheet. 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.
  3. 1QQD43E.png
    3
    Auto-populate the Mapping Sheets with data from the master spreadsheet. 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")
  4. 4
    Repeat this step for both the “Category” column and the “Address” column. 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.
  5. GHTY34281.png
    5
    Map your data points. Once this step is complete, you are ready to test your map. Click the “Build” icon on the Mapping Sheets sidebar to the right.
  6. IKIUH6751.png
    6
    View your map. 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.

We could really use your help!

Can you tell us about
gossip?
Yes
No
gossip
how to gossip
Can you tell us about
gleeking?
Yes
No
gleeking
how to gleek
Can you tell us about
home hacks?
Yes
No
home hacks
how to warm up a room when it's cold
Can you tell us about
relationships?
Yes
No
relationships
how to treat your girlfriend like a princess
Thanks for helping! Please tell us everything you know about
...
Tell us everything you know here. Remember, more detail is better.
Tips
Provide Details.
Please be as detailed as possible in your explanation. Don't worry about formatting! We'll take care of it. For example:
Don't say: Eat more fats.
Do say: Add fats with some nutritional value to the foods you already eat. Try olive oil, butter, avocado, and mayonnaise.
Submit

EditTips

  • Creating a Form: There is a ton of information field personnel can collect using their smart phone. The specific field data needs will need to be sussed out by project managers on a case by case basis but the good news is that the possibilities are seemingly endless using a clever combination of smart phone features such as cameras and calculators and free or low cost downloadable applications. For instance, iPhone users can easily to get coordinates for a form by accessing the “Compass” application. From this application, you can press and hold over the lat./long coordinates until the “Copy” option appears. Select copy and then paste it into the form as required.
  • Regarding Mapping Sheets: You will notice that the category column on Mapping Sheets provides a simplistic sorting option on the map. To get the most bang for your buck, you may want to limit your category options by incorporating a drop down multiple choice question on your Form. This will prevent unnecessary categories from being created every time someone misspells something in a text box.
  • Uses for this system: This method is particularly effective for collecting and managing water quality, volunteer, MS4, outfall, illicit discharge and stream data but it can be modified to fit just about any need.

EditSources and Citations


Article Info

Categories: Software

Thanks to all authors for creating a page that has been read 911 times.

Did this article help you?
Yes No