Help Needed for HS Project: Community-Service Matching Algorithm
easte...@gmail.com
Hi Code for DC,
My name is Rob Barnett and I am a math teacher at Eastern Senior High School, in NE DC. I'm currently working with a team of students to develop an algorithm that matches students with community service opportunities based on availability, student interests, and convenience. We are in the finals of the national Samsung Solve for Tomorrow competition, and we can use your help!
Here's where we are: we currently have two Google Forms out, one for community service organizations and one for student preferences. Each feeds into a separate Google Sheet. Assuming that we can get the two sheets to talk to one another, we have Google Sheets formulas that will match a student with the appropriate service opportunity, based on his/her preferences. We also have a short JavaScript program that will send both the student and the service provider an e-mail confirming the match.
What we would like to do is automate the matching process. Rather than having to go into the spreadsheet manually to make the matches (by extending formulas) and send the e-mails (by running the JavaScript), we want the computer to do that, as soon as the Google Form is submitted. Think of an online personality test that e-mails your results, and you'll have a pretty good idea of what we want to create.
If you want to check out our code, there's a link to a model spreadsheet here. (For ease, I've manually combined both databases into one sheet.) Green columns refer to data generated by our Google Forms, black to steps done by the computer, and red to the outputs which go into the e-mail which we want to send. If you make a copy of this spreadsheet in your Google Drive and go to "Tools > Script editor" you will see three scripts:
1. The Google Sheets formulas which should run whenever a new service opportunity is entered on Google Forms.
2. The Google Sheets formulas which should run whenever a new student submits a Google Form.
3. The e-mail sending script. If you run this, you will see an error on Line 39... but it will still send e-mails to the addresses which are listed in the spreadsheet. For some reason it just won't run completely through (which may be preventing our installable trigger from working.
What we need to figure out is:
1. How to make two separate Google Sheets databases speak to each other (which is essential as we want both to be constantly updating).
2. How to automate the processes we can currently do by extending formulas and running the JavaScript file.
If you have any thoughts on this, please let us know. (Or, if you just want to do this for us, that would be amazing too!) I'm happy to give you more information/access if need be, or to come in and meet with you some time – with or without my students, who are awesome. Thank you in advance!
Sincerely,
Rob Barnett