PASSNYC: How we helped build the definitive guide to scholastic events in NYC

At PASSNYC, we are volunteers lending our specialized skills to various partners in the New York City education space. One of our partners, Let’s Talk Schools, came to us with a dream of having full insight into every meaningful education event in New York. Here’s a quick peek into how we used Google Apps Script to make this dream come true. We had 3 requirements for a perfect calendar:

1. Covers every public event from the Department of Education in a useful, sortable and searchable fashion
2. Allows for easy uploading from clients and partners (and non-public entities)
3. As a parent, you can search for only the events that matter to YOU

Here’s a glimpse at what we did, meant for educational purposes:

Step 1: Scrape publically available data using google calendar

Hooray for public data and hooray for Google’s tools! NYC’s Department of Education hosts several Google Cals on their website with all public events:

This website is helpful, but limited in that a) it is divided by school type and b) it’s crowded. There are thousands of schools in NY, so a filter could go a long way.

We start by building a service that captures all updates to each calendar. Here comes some Apps Script!

doeCalendars = {
  preschool:    ‘strongschools.nyc_s3s77q5jg2heve7baj2sihqks0@group.calendar.google.com’,elementary: '...’,middle:'...',...
}
function pullAll() {
  var keys = Object.keys(doeCalendars);
  for (Key in keys) {
    var type = keys[Key];
    var id = doeCalendars[type];
    var events = getEvents(id);
}
function getEvents(id) {
//pulls next 10 months of events from DOE
  id = id || doeCalendars[‘elementary’];
  var cal = CalendarApp.getCalendarById(id);
  var startTime = new Date(); var endTime = new Date();
  endTime = new Date(endTime.setMonth(endTime.getMonth() + 10));
  var events = cal.getEvents(startTime, endTime);
  return events;
}

Just one piece of the puzzle, but this should highlight how a few magic words can pull in all the data we need. If you want to learn more about apps script, follow my blog and also check out this great primer by Ben Collins.

Step 2: If you can’t scrape it, make it

We build a capture form (in Google Forms) to allow for partners to contribute to the same database, plus we assign a unique ID for each other school. Custom apps script comes in secondarily to make sure the format matches the DOE format.

Step 3: Serve it up

The final step for our apps script is to ‘tag’ all data in meaningful ways. For example, see this particular event:

These tags go into more details when applicable, such as costs, school type, age, and custom IDs. This allows us to filter the results seen by the end user (paid users of Let’s Talk Schools).

Have a child looking at middle schools near 10128 that are public? Our dataset now helps you find the perfect events for you.

You can learn more about our work at passnyc.org, or about Let’s Talk Schools’ work at https://www.letstalkschools.com/contact-us/.