Case Study: A Google Sheets Project - Montana Webmaster

Case Study: A Google Sheets Project

The Scenario

Boy with empty plateA non-profit organization takes applications from the community for volunteers to be assigned to a demographic in need. Matching of volunteers with folks in need is done twice a year, but members of the community can sign up at any time during the year. At the time of matching, the signups need to go through a series of elimination to help the matching committee make the best matches possible. But, this matching process takes hours of volunteer time.

The Question: Could Software Help Shorten the Process?

The first question is actually, “Should software be used to help shorten the process?” The organization is proud of the quality of their matches and the human touch that produces that quality. So, the next question is, “Are there parts of the process where the human touch is needed and parts where it’s not needed?”

Some of the intake questions for both clients and community volunteers are Yes/No.  One example of a Yes/No question is whether either the client  or the community volunteer smokes. Another filter is pet allergies. Some of the criteria are not so cut and dried. For example, the question about the types of activities preferred by both volunteers and the match.

Software can easily do the questions that are yes – no, such as smoking. Then the more subjective questions would continue to be determined by a person. With this plan in place, the next step is to determine how the plan can be implemented with software.

Thinking through the Data and Needs

Data Available

  1. There is a list of volunteers
  2. There is a list of clients
  3. A volunteer can be matched with 1 or 2 clients.
  4. Some of the form questions have a yes/no answer. Only these are under consideration for automation. For the purpose of this article, two yes/no questions will be used as examples: smoking and pets.

Data Results Needed

  1. A list of volunteers paired with general information about the client(s) they are matched with.
  2. A list of clients with general information about the volunteers they are matched with.
  3. Mailing labels for volunteers.

Choosing a Technology

The non-profit does not have a budget for software and software updates, so  Google forms and other Google tools are a great option. Community volunteer and client signups are collected through a Google Form. The Google Form sends the responses to a Google Sheet at the main level of Google Drive. At the matching deadline, the Google Sheet is manually duplicated into a Matching -> Year subfolder. Then a series of operations are done to prepare the data for the matching process.

What Should Be Automated?

It takes a significant amount of time to plan and set up the existing spreadsheets used for lists. All work is done by volunteers, so it would be helpful if the spreadsheet preparation process could be done with a macro, instead of manually. Whenever there is an action on a computer that is done repeatedly with the same steps, it is possible that a macro, or script, can do those steps.

A macro can be created with coding, but it’s much easier to have the program just record your steps and make the macro for you. Many types of business software has macro capability, including Microsoft Office programs. The process is very similar for all of them.

In this case, automations could provide lists of potential matches for the volunteers to make the final choices, where the Yes/No questions had already been matched by automation.

Step 1: Creating a Macro to Format Each New  Sheet

The goal here is to 1) make a new sheet, 2) add formatting to the first row automatically. It is important to know exactly what steps you want to take before you start recording. For example, today, I made a macro that sets the text to size 11 and then sets it to size 12, because I forgot which size I wanted after the macro was already recording!

Keep in mind, that if you change what you want the spreadsheet to look like later, you will have to re-record the macro. Re-recording the macro will not affect the sheets you have already created.

Step 2: Importing Data from the Original Sheets to the Matching Sheet

The both volunteer and client signups are collected in Google Form. The Google Forms send the sign up information into separate Google Sheets. So, the first step is to copy the data from the main Google Form Sheet into a file that is specifically used for the matching process. The first hurdle in this step is terminology, in that Google doesn’t consider this step a “copy”. Because the information is in a separate file, it considers it an “import”.

Because the macro from the Matching Google Sheet file will be importing data from the file that Google Forms creates, you will have to be sure that the file name matches what the macro is looking for. One way to do this is to create a new folder for each matching period and put both the Matching sheet and the Form sheet into that folder.

Step 3: Assigning Priorities to Yes/No Questions

The members of the organization feel that it is very important that they be able to note exceptions at every step. So, each decision will produce a new, named sheet with the same formatting as the other sheets for ease of use.. For this example, we are only considering two questions. Let’s say that the smoking question is top priority with the pet allergies taking second priority. Then the first lists would identify those who smoke and those who cannot be around smoke.

Step 4: Making Lists

The first macro just followed a set of steps that would be the same for each new sheet. Now, we will have to use some logic to add content subsets to sheets. This macro will also have to setup column names. So, the first substep is to figure out how the data will be laid out.

Questions to Ask the Client

  1. Will these decisions be made by the Matching Committee or by the whole Board?
  2. What lists do you need? Potentially, there are
    1. Students who smoke
    2. Students who don’t smoke
    3. Community volunteers who smoke
    4. Community volunteers who don’t smoke
    5. How should people who don’t smoke, but don’t care either way, be assigned?
  3. Should there be one sheet of lists related to the smoking issue or should each list be on its own sheet?

The End Result

In the end, when the concept was introduced, the development of these spreadsheets felt too much like a “dating service” to some committee members, and the project was cancelled.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.