For years I've wondered what code I could write to help improve attendance at my shul's Thursday morning minyan. For those not familiar with a minyan, the idea is that we have a weekly prayer service that runs regardless of how many people attend. If 10 Jewish adults are present then we can say a number of additional prayers. You can apply the logic of this post to any gathering that strives to have a quorum.
There are a number of competing requirements that minyan related software should meet:
- It should encourage people to attend without turning into a nag.
- It should be respectful of people's time and only call for a minyan when one is necessary.
- It should be a lightweight, frictionless system. Users shouldn't need to register an account or have to log in to interact with it.
- It should work equally well over mobile and desktop devices.
- It should be something I can build and maintain with a minimum of effort.
- It should both push information out to the community, as well as serve as a repository interested individuals can interact with.
Over the years, I've had glimpses of solutions to the above ("What if I made an SMS based ...") but I've never had a concrete plan.
And then a couple of weeks ago, while on a run, I had a flash of inspiration. I then sat down and coded an implementation in 45 minutes. This blog post will probably take longer to compose and edit! Here's what I did.
First, I created a new Google Sheet. I setup one place on the sheet to request a minyan and another place on the sheet to say you're attending. I made the sheet editable by all (so no login required) and protected all the cells except for the area I expected input. To simplify access, I created a custom redirect on the shul's website so that a URL like: http://myshul.com/minyan would take users to the spreadsheet.
Here's a screenshot of the sheet in action:
With just this sheet and URL created I solved a number of the above challenges. The system didn't require any registration or login and had a friendly URL. That's pretty frictionless, no? The system serves as a repository of information. On Wednesday night, folks can peek in and see how close we are to a minyan for the following day. Google Sheets works well on both Mobile and Desktop devices, so I was covered there too. And so far, the system had been effortless to build.
There were a couple of key pieces missing. First, the system doesn't push information out. Second, there's no obvious way to reset the sheet for the next week. On my run I imagined I'd have to write custom code to address these challenges. However, Google was one step ahead of me and provides easy fixes. The magic: Installable Triggers. Specifically, Time-drive Installable Triggers. Using these you can invoke a function on your Google Sheet at a recurring time. Essentially, it's cron for Google Apps. I setup two triggers: one happens two days before minyan and calls the notify function, and one happens an hour after minyan and calls reset. Here's the definition of the notify trigger:
All that was left to do was to write the notify and reset functions. reset I figured would be straightforward as it's little more than code to delete the contents of cells. But notify I intended to send e-mail. Would Google Sheets allow such an action? Again, things broke my way and the answer was a resounding Yes! Here's my implementation of notify and reset:
function reset() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheet = doc.getSheetByName("Minyan Tracker");
var ranges = [ sheet.getRange("C5:C17"),
sheet.getRange("E5:E17") ];
ranges.forEach(function(r) {
r.clearContent();
});
}
function notify() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheet = doc.getSheetByName("Minyan Tracker");
var needs = sheet.getRange("E5:E17");
var requests = needs.getValues().map(function(row) {
return row[0];
}).filter(function(cell) { return cell != ''; });
if(requests.length > 0) {
var body = "A Minyan was requested for Thursday morning. Can you attend?\n\n" +
"If so, please visit: http://myshul.com/minyan or reply 'Yes' to this e-mail to let the community know.\n\n" +
"Tired of getting these e-mails? Mail Ben Simon (benjisimon@gmail.com) and ask " +
"to be removed the TMMR mailing list.";
MailApp.sendEmail("minyan-notification@mylist.com", "[TMMR] Minyan Requested", body);
}
}
I setup the minyan-notification e-mail list to include folks interested in attending minyans. I configured the e-mail list so the replies go back to the sender; Me. This means that recipients can signify their attendance by either clicking on the link within the e-mail, or hitting reply to the e-mail. What could be simpler than that?
notify only sends e-mail if someone has signed up requesting a minyan. The request list on the sheet, like the list of attendees, is cleared out weekly so it stays fresh. The result is a system that pushes out requests but is careful to do so only if a congregant has an explicit need.
It truly is amazing what you can accomplish using a Google Sheet, an e-mail list, a bit of code and some creativity.
