The Scenario
A 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. In spreadsheet terminology, these are sorts and filters.
One example of a filter is whether the person with the need smokes and which community volunteers are OK with cigarette smoke. 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.
The idea behind using software to assist in the matching process is that the software can easily do the questions that are yes – no, such as smoking. Then the more subject questions would continue to be determined by a person.
The Technology
The non-profit does not have a budget for software, so they are using Google tools. Community signups are received through a Google Form. The Google Form sends the values to a Google Sheet at the main level of Google Drive. At the matching deadline, the Google Sheet is duplicated into a Matching subfolder and a Year subfolder. Then a series of operations are done to prepare the data for the matching process.
The Need
It takes a significant amount of time to set up the spreadsheets. 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.
Step 1: Importing Data from the Original Sheet to the Matching Sheet
The signup information is collected in a Google Form. The Google Form send the sign up information to a Google Sheet. So, the first step is to copy the data from the main Google Sheet into a file that is specifically used for the matching process. The first hurdle in this step is that Google doesn’t consider this step a “copy”. Because the information is in a separate file, it considers it an “import”.
The End Result
In the end, the development of these spreadsheets felt too much like a “dating service” to some committee members, and the project was cancelled.