Case Study: A Google Sheets Project
The Business 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 sorts and filters to help the matching committee make the best matches possible.
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.
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”.