As you may have begun to learn in my previous post on Keeping a Church Prayer List, I am a firm believer that the tools developed by Google can be a great benefit to churches, particularly in the unglamorous but vitally important arena of church administration. One particularly useful Google tool is the Google Doc Spreadsheet, which is useful primarily because its spreadsheets:
- Work like Microsoft Excel
- Are free
- Are accessible from any computer connected to the internet
- Easily shared and developed with collaboration
While each of those features are helpful on their own, Google has recently added a whole new layer of function to spreadsheets with the advent of Forms. It is now possible, with any Google Spreadsheet, to easily create a form that imports responses into your spreadsheet. This can save you and the members of your congregation a great deal of time in administration.
For the sake of this tutorial, we will develop a spreadsheet and form for Vacation Bible School (VBS) registration, but simply by altering the details of your spreadsheet you could just as easily use this for potluck dinners, Sunday School volunteers, van drivers, a prayer list, or anything else that you might need.
Tutorial: Building a Form for your Spreadsheet (Using VBS Registration as an Example)
- Log- in to http://docs.google.com (if you do not have a Google account yet, you will need to sign up for one, but it is free and deserves to replace whatever e-mail client you are currently using)
- On the left-hand side of the page click Create New -> Spreadsheet
- At the top of the new spreadsheet, click the title, Unsaved Spreadsheet and then rename the file. In this example, we will name it VBS Registration 2011
- We will now begin to fill in the title for the different columns by typing in the following at the Spreadsheet adresses:
- Child Last Name at 1A
- Child First Name at 1B
- Child’s Age at 1C
- Parent’s Last Name at 1D
- Parent’s First Name at 1E
- Phone Number at 1F
- E-Mail Address at 1G
- Allergies at 1H
- Emergency Contact Info at 1I
- Now to do a little formatting…
- Click the 1 to the left of the first row, this should highlight the entire first row
- Change the Text Background Color to something other than white so that it will stand out (Text Background Color is found at the button that looks like a T in a box)
- Right-click the empty square just above the number 1 and to the left of the letter A
- Choose Sort, then check Data Has Header Row, then press the Sort button
- Now select the View menu from the top of the page, then click Freeze Rows -> Freeze 1 Row (doing this means that no matter how far down you scroll on the spreadsheet, you will always see the column titles at the top)
- Ok, you are ready to make your form…
- Select Tools at the top, then Form -> Create a Form
- A new page will open showing your form and allowing you to edit it
- Hover your mouse over any of the form fields and you will see a little button with a picture of a pencil appear, click that Pencil to edit that form field
- On each form field you can choose whether or not to make that field required (a good idea for emergency contact info, not necessary for E-mail address), provide a help text to explain what you are asking (regarding emergency info, for instance, the help text might prompt parents by suggesting, “Names and Phone Numbers of Emergency Contacts if the Parent Cannot be reached”), and even change the type of question it is (e.g. “Text” is appropriate for the last name, “Paragraph Text” is appropriate for emergency info, “Multiple Choice” is great if people should only select one option, and “Checkboxes” are great when you need them to be able to select multiple options).
- Once you are done formatting your form, you should save it if it doesn’t already say Saved at the top
- Now you just need to share your form with the people who will be signing up. You have the option of sending the form inside an e-mail using the E-Mail This Form Button, embedding it in your pre-existing website by copying the code from the Embed button and pasting it in your site, or by sending the form as a stand-alone web page by returning to your Spreadsheet, selecting Form at the top, then Go to Live Form and then sending the address to others
- When people fill out the form, their responses will automatically be entered in your spreadsheet for you to see simply by signing back into http://docs.google.com
- Finally, if you have multiple people who need access to the spreadsheet to see the responses, you can share it by clicking the Share button above the spreadsheet and entering the e-mail addresses of those who should have access (please keep in mind that many spreadsheets, such as those used for VBS registration should always be kept on the Private setting as you do not anyone but trusted church members having access to children’s information)
If you would like to save yourself a little time making a VBS form, please feel free to use the template I have developed as an example.
All questions, comments, and suggestions are welcome below.
Is there an easy way to do multiple kids from the same family. I tried it using your basic format, but adding additional fields for child #2, #3, etc. The problem was that the spreadsheet then got really wide, If you only enter 1 child per form, then you have to repeat a bunch of info; or does it remember?