At Devpost, my life is punctuated by hackathons. For 48 hours, it’s chaos. But unlike the hackers, I’m not concerned about meeting the submission deadline or nailing my demo at the expo. I’m worried about what happens in-between.

Intro to madness

I cut my expo teeth at PennApps X. After a sleepless, caffeine-fueled fugue, 1,200 hackers rushed to submit 237 projects before the 9:30am deadline. Afterwards, they hustled across campus to demo at an expo which was supposed to start at 10:30. At mega-hackathons like PennApps, expos can take up an entire gymnasium.

That left me and the organizers about an hour to assign and distribute table numbers. Hackers couldn’t setup with them. Judges couldn’t start their rounds until the hackers were situated. And sponsors needed to know which projects used their tech & APIs. It wasn’t pretty.

PennApps X Expo

Swing and a miss

A few months later at PennApps XI and MHacks V, Richard Murby and I tried to streamline the process with shared Google Sheets. We exported submission data from ChallengePost, assigned table numbers, created filterable tabs for each sponsor, and sent out the links via email.

It was a solid plan, but there was just one teeny tiny problem: Google Sheets can’t handle 1,000+ concurrent connections.

Getting it right

After my mega fail at PennApps, I resolved to find a solution. One that could scale for this summer’s 2,000 person Global Hackathon in Seoul. And guess what? I think I’ve got something.

Google Sheets is still the best way to manipulate and share tabular data with lots of people at once. And I think that the concurrent connection issue only affects sheets with filters and lots of formulas.

So, what if we treated Google Sheets as a simple datastore? Instead of running individual queries, we could load all the data locally and use client-side JavaScript to selectively show & hide data. That might alleviate the concurrent connection issue, right?

I found the solution while digging through GitHub one morning. According to author Jessica Lord, “Sheetsee.js is a client-side library for connecting Google Spreadsheets to a website and visualizing the information in tables, maps and charts.”

After looking the basic table demo, I knew I could adapt it for hackathons.

Here’s how it works:

  1. Create a copy of this Google Sheet and export your hackathon’s submission data from ChallengePost.

  2. Copy the Submission Title (A) and Sponsor Prizes (G) columns from the export into projectName (B) and sponsorPrizes (​C) in the Google Sheet.

  3. If you have less than 500 projects, delete the extra rows. If you have more, copy down the formula in tableNum (A) to assign extra table numbers. And if you need to reorder / reassign table numbers, (e.g. drones must be at tables 70–100), you’re in control.

  4. Select Publish sheet to web from the File menu and copy the sheet URL. It contains a sheet id which looks something like: 1kaNq96j0S76A2P_dk82VekiruiWEQXTu5hT0POPywe4.

  5. Replace the id and name parameters in this URL with the id from step 4 and the URL-encoded name of your hackathon: http://nealrs.github.io/hackathon_tables/?id=1kaNq96j0S76A2P_dk82VekiruiWEQXTu5hT0POPywe4&name=PennApps%20W15

  6. Shorten the URL and share it via email, Twitter, Snapchat, or whatever.

That’s it. You’re done in less than 5 minutes. Here’s what it looks like:

Hackathon Table Demo

The app has a search filter and downloads all of the data locally. Hackers can look for their project by name to find their table and sponsors can identify projects using their tech. Since it’s a static page hosted on GitHub, there’s nothing to deploy either.

It looks good too, thanks to Holly Tiwari. She added the tiger striping, fixed header, and mobile styles.

The app works well, but I haven’t battle tested it yet. I’m hoping that you can help me out with that. The repo is open source and I’d love to get your feedback on it. Even better — a pull request!

Built With

+ 7 more
Share this project:
×

Updates

Neal Shyam posted an update

Made some big (thought not yet merged upstream) updates for MHacks Refactor. I added a mobile only toggle for sponsor prizes & categories, so category judges aren't left out in the cold. Even if you hide one of the columns, the search / url param filtering will still work.

Also, the MHacks crew told me that this year, each hack will have a "purpose" attribute to help judges understand what it's about. I added that in parentheses below the project name. (And yes, you can also use that as search criteria / filter.)

There's another hidden column too that'll help the 50+ judges know which tables to go to!

Check out the live demo @ http://nealrs.github.io/mhacks_refactor_expo

Log in or sign up for Devpost to join the conversation.

Neal Shyam posted an update

During our hackday, i wrote an optional pre-commit hook that converts some of the data to JSON:

{
  crAIg: {
    link: "http://yhack2015.devpost.com/submissions/44212-craig",
    table: "131",
    expo: "1",
    sponsors: ""
  },
  webTLDR: {
    link: "http://yhack2015.devpost.com/submissions/44057-webtldr",
    table: "67",
    expo: "1",
    sponsors: "Microsoft,HP,Facebook,Yale Institute of Cultural Heritage,Bloomberg"
  }
}

it places the the file at /data/json like this.

Log in or sign up for Devpost to join the conversation.

Neal Shyam posted an update

SUCCESS! The revised table number app worked at MHacks 6] without any scaling problems.

After the hard deadline at 10am, I pulled a submission export and created a local CSV file with all the project details. Then I pushed it up to my gh-pages branch and the app was live for hackers to find their expo & table numbers. I did a few additional deploys during the expo for late submissions.

At @Murby's suggestion, I wrote print styles so we could use the app to create personalized judging sheets for each sponsor (Srsly, try printing that). And in yet another awesome MLH + Devpost collaboration, @yaynickq made a pull request mid-judging to further optimize the print styles.

There are still some things to improve. Right now, if you edit data.csv with a text editor, it'll break the app. For some reason you have to use Excel and export to CSV.

Log in or sign up for Devpost to join the conversation.

Neal Shyam posted an update

So yeah. we tried this at PennApps XII with some additional modifications (hardcoded URL) and extra columns for the new route system.

It worked super well for a while. Ricky and Sam from Twilio said it saved their lives during the expo, making it easy to filter the list and identify the exact tables they needed to visit.

But, once all 2000+ hackers & sponsors tried to pull data from the same Google Sheet and the whole thing fell apart. I was banking on the idea that a view-only Google Sheet would't suffer from the same problems that our earlier attempts at filterable sheets did. I was wrong and it was pretty embarrassing.

Today, I ditched sheetsee.js & Google Sheets for Papa Parse, Mustache, list.js, and simple CSV file. For users, it functions exactly the same, runs faster, and eliminates the weak link (Google). Of course, I still haven't stress tested it, but perhaps the good folks at MHacks will give me a second chance.

Log in or sign up for Devpost to join the conversation.

Neal Shyam posted an update

  1. Added template tags to link each project name to it's Devpost page.
<td class="name"><a href="{{projectlink}}">{{projectname}}</a></td>
  1. Also added template tags for additional fields (currently named foo, bar, baz) in case you need more info. But, you'll need to create new classes & write your own CSS styles for them.
<!-- column headers -->
  <th class="foo">Foo</th>
  <th class="bar">Bar</th>
  <th class="baz">Baz</th>

<!-- data -->
  <td class="foo">{{foo}}</td>
  <td class="bar">{{bar}}</td>
  <td class="baz">{{baz}}</td>

The Google sheet template has been updated with new columns accordingly.

  1. There is a filter URL param which you will pre-populate the search box, but for some weird jQuery reason, I can't get it to trigger an update event / resort the list. Maybe somebody could help me with that?

Log in or sign up for Devpost to join the conversation.