Google Forms and Sheets and a VMS Component

Google Forms and Sheets and a VMS Component: A Guideline 
 
 
  Danny Johnson | Julie Lee | Gabriel Tamayo | Hannah Jayne | Kush Shah   
2 
The purpose of this manual is to explain how to 
use the Google Forms and Sheets that are being 
utilized as back-end data storage for an inhouse 
VMS solution. This will provide a breakdown of 
the Form associated with the Google Sheets, 
the individual Sheets themselves as well as the 
associated tables and aggregated data.  
The Google Form and Sheets shown in this 
manual are used to gather and store data that is 
displayed in a Retool application. It is incredibly 
important that the user of the Google Form and 
Sheets fully understand them and how to 
properly update both so that any calculations or 
displayed data in the volunteer management 
dashboard are accurate. 
The Google Form and Sheets were made to fit 
the specifications from ENLACE, a governmental 
nonprofit operating in the Caño Martín Area in 
Puerto Rico. Any additional edits made that are 
not mentioned in this manual will require the 
editor to have a full understanding of Google 
Sheets in order to aggregate the data. The 
added data will not appear in the volunteer 
management dashboard until it is also edited. 
External links to assist in learning more about 
Sheets will be located in the External resources 
chapter. 
 
 
 
 
 
 
 
 
 
 
 
Contents 
Google Form Components ................................. 3 
Correo Electrónico/Email address .................... 3 
Fecha en la que proveyó el servicio 
voluntario?/Date you provided the voluntary 
service ................................................................ 3 
Nombre/Full name ............................................ 4 
Organización en la que trabajaste?/ Entity 
worked with ....................................................... 4 
Proyecto/Project................................................ 5 
Tareas/Tasks ...................................................... 5 
Horas/Hours ....................................................... 6 
Comentarios opcionales .................................... 6 
Sheets Components ........................................... 7 
Respuestas de Formulario/Form Responses ..... 7 
Validated Responses .......................................... 7 
Unvalidated Responses ...................................... 7 
Certificate Information....................................... 8 
Valor por Hora/ Value per Hour ......................... 8 
Summaries .......................................................... 9 
Quantification of Volunteer Work ..................... 9 
Sum of Hours per Project and Task .................... 9 
Total Volunteers per Project ............................ 10 
Summary of Projects ........................................ 10 
How to properly update Google Forms and 
Sheets ............................................................... 11 
Editing Google Form ......................................... 11 
Step One: Edit Access on Google Forms .......... 11 
Step Two: Adding/Editing Choices in Existing 
Questions ......................................................... 12 
Question One: Entity Worked With/ 
Department Worked With ............................... 12 
Question Two: Project Worked On .................. 13 
Question Three: Task ....................................... 14 
Step Three: Adding Questions ......................... 15 

Google Forms and Sheets and a VMS Component: A Guideline

Danny Johnson | Julie Lee | Gabriel Tamayo | Hannah Jayne | Kush Shah

External Resources .......................................... 17

Google Form Components

Google Forms is how the volunteers log their time donated. This is a pivotal step of the process because

without filling out the Google Form their data will not be recorded. The format of the google form is also

very important. Questions that utilize a drop-down menu do so because the answers need to be

formatted in a particular way.

Correo Electrónico/Email address

This is a required question that asks for the volunteer’s email and is entered as short text.

Fecha en la que proveyó el servicio voluntario?/Date you provided the voluntary

service

This is a required question that asks for the date that the voluntary work was completed and is entered

through a date field. This question is important because volunteer work may be logged after the work

was completed.

Google Forms and Sheets and a VMS Component: A Guideline

Danny Johnson | Julie Lee | Gabriel Tamayo | Hannah Jayne | Kush Shah

Nombre/Full name

This is a required question that asks for the Volunteer’s full name and is entered as short text.

Organización en la que trabajaste?/ Entity worked with

This is a required question that asks the volunteer which organization they worked with and is answered

through multiple choice so that the value entered is consistent throughout all responses.

Google Forms and Sheets and a VMS Component: A Guideline

Danny Johnson | Julie Lee | Gabriel Tamayo | Hannah Jayne | Kush Shah

Proyecto/Project

This is a required question that asks the volunteer which project they worked on and is answered

through multiple choice so that the value entered is consistent throughout all responses.

Tareas/Tasks

This is a required question that asks the volunteer which task they completed and is answered through a

drop down so that the value entered is consistent throughout all responses.

Google Forms and Sheets and a VMS Component: A Guideline

Danny Johnson | Julie Lee | Gabriel Tamayo | Hannah Jayne | Kush Shah

Horas/Hours

This is a required question that asks the volunteer how many hours they donated through their

volunteer work and is entered as a number in the short text field.

Comentarios opcionales

This question asks the volunteer if they have any additional comments and is entered as a short text.

Google Forms and Sheets and a VMS Component: A Guideline

Danny Johnson | Julie Lee | Gabriel Tamayo | Hannah Jayne | Kush Shah

Sheets Components

Respuestas de Formulario/Form Responses

This Sheet records the responses from the Google Form. These responses are input as unvalidated and

have the option to be validated.

Validated Responses

The validated responses sheet holds the validated responses from Respuestas de Formulario.

Unvalidated Responses

The unvalidated responses sheet holds the unvalidated responses from Respuestas de Formulario.

Google Forms and Sheets and a VMS Component: A Guideline

Danny Johnson | Julie Lee | Gabriel Tamayo | Hannah Jayne | Kush Shah

Certificate Information

This sheet contains a Pivot Table that breaks down the number of hours donated by each individual

volunteer.

Valor por Hora/ Value per Hour

This sheet provides the various tasks that can be completed and their associated hourly rate.

Google Forms and Sheets and a VMS Component: A Guideline

Danny Johnson | Julie Lee | Gabriel Tamayo | Hannah Jayne | Kush Shah

Summaries

The summaries sheet is home to most of the data aggregation.

Quantification of Volunteer Work

This table takes the task values from the Valor por Hora sheet and multiplies them by the

number of hours for each task from validated responses sheet.

Sum of Hours per Project and Task

This Pivot Table breaks down the projects by the tasks that they contain, and the number of

hours dedicated to each task.

Google Forms and Sheets and a VMS Component: A Guideline

Danny Johnson | Julie Lee | Gabriel Tamayo | Hannah Jayne | Kush Shah

Total Volunteers per Project

This Pivot Table provides a break down of the number of volunteers per project as well as a

grand total of all volunteers.

Summary of Projects

This sheet contains the various projects and relevant project information.

Google Forms and Sheets and a VMS Component: A Guideline

Danny Johnson | Julie Lee | Gabriel Tamayo | Hannah Jayne | Kush Shah

How to properly update Google Forms and Sheets

As time goes on and information needs to be added to the form, it is important that the information is

added so that all components of the form and sheets still work properly.

Editing Google Form

When editing the Google form the questions being asked, and their order in the form should remain

consistent, but the available choices for the entity worked with, project, and tasks can be updated. In

addition to this, questions may be added. Please note that any questions that are added to the form will

not be linked to other sheets in the associated Google Sheets.

Step One: Edit Access on Google Forms

To edit the Google Form, first go to the associated Google Sheets. First click on Tools,

then Manage Form, and lastly Edit form.

Google Forms and Sheets and a VMS Component: A Guideline

Danny Johnson | Julie Lee | Gabriel Tamayo | Hannah Jayne | Kush Shah

Step Two: Adding/Editing Choices in Existing Questions

Most important step

Once in the Form editor, simply click on the question that you would like to edit.

For this step, it is critically important to understand how to update the Google Sheets so that responses

to the Google Form are consistent with the data in the spreadsheet. The three questions that require

the most attention to detail are “Entity Worked With”, “Project Worked On”, and “Task”.

Question One: Entity Worked With/ Department Worked With

The question “Entity Worked With” is a multiple-choice question. To edit

multiple-choice questions, click Add option to add another choice for the question.

Clicking add ”Other” creates a text box for “Other” responses.

Google Forms and Sheets and a VMS Component: A Guideline

Danny Johnson | Julie Lee | Gabriel Tamayo | Hannah Jayne | Kush Shah

Question Two: Project Worked On

The question “Project Worked On” is a multiple-choice question. To edit multiple-choice questions,

either click on existing choices to edit them or click Add option to add another choice for the question.

Clicking add ”Other” creates a text box for “Other” responses.

After editing an existing project choice or adding a new project, edit the existing project name or add a

new project, the person in charge, their email, and the project description in the Project Summaries

Sheet. Total hours is a calculated field and does not need to be edited.

Google Forms and Sheets and a VMS Component: A Guideline

Danny Johnson | Julie Lee | Gabriel Tamayo | Hannah Jayne | Kush Shah

Question Three: Task

To edit task, which is a dropdown question, either click on an existing task to edit it or click Add option

on the bottom to add another option.

After editing an existing task or adding a new task, edit the existing task name or add a new task, and its

corresponding value per hour. Make sure that the spelling of the task in the Google Form and the Value

per Hour sheet are identical because if they are different other fields that reference them in the Sheets.

Google Forms and Sheets and a VMS Component: A Guideline

Danny Johnson | Julie Lee | Gabriel Tamayo | Hannah Jayne | Kush Shah

Step Three: Adding Questions

We highly advise against adding questions due to the possibility of it breaking the formulas.

To add questions to the form, click on the bottom most question. A pane should appear next to the

question with the top button there to add a question.

The first option for editing these new questions is by changing the title in the top right.

The next being to change the type of question in the top right. If multiple choice, dropdown, or

checkboxes are selected, follow step two to further customize the question. The date, time, short

answer, and paragraph options need no further editing.

Google Forms and Sheets and a VMS Component: A Guideline

Danny Johnson | Julie Lee | Gabriel Tamayo | Hannah Jayne | Kush Shah

If multiple choice, dropdown, or checkboxes are selected, follow step two to further customize the

question. The date, time, short answer, and paragraph options need no further editing. Linear scale,

multiple choice grid, and checkbox grid are not incredibly useful for this system.

The last option is to make the question required or not in the bottom right.

Google Forms and Sheets and a VMS Component: A Guideline

Danny Johnson | Julie Lee | Gabriel Tamayo | Hannah Jayne | Kush Shah

External Resources

Google Forms and Sheets can be used as a powerful tool to sort gather and sort data. The full

capabilities of both Forms and Sheets are not explained in this manual.

Below are external links that will provide a better understanding of what these tools can do and how to

do it.

How to use Google Forms

How to use Google Sheets