Bootstrapping your company counter

Every company has a purpose. At OpenClassrooms, ours is to help people to find jobs by making Education more accessible. We offer education through a combination of online courses and mentorship.

It means our primary KPI is: “How many people have we helped to find jobs?”. However, how can we be sure that everyone in the company knows how well we’re doing?

Of course, we could be showing dashboards on big TVs in every corner, but we’d quickly get lost in a sea of data. Sometimes, less is more. This is why I wanted to go back to something very simple, very practical:

A physical counter.

Finding the perfect counter

Everything is connected these days. Why not a physical counter?

After some research, I found out about Smiirl, which is well known for its Facebook and Instagram counters for local businesses:

Image for post
Image for post
A Smiirl Instagram physical counter

These counters connect to the internet and are updated in real time.

I started wondering if I could customize one to our needs. I don’t want to show Instagram or Facebook likes; I want to show how many people have gotten jobs!

Turns out… they also have custom counters in stores! Yay!

Image for post
Image for post
A custom counter

Setting up the counter

Setting up the counter is really simple (if you’ve ever written some code). You need to provide a page which sends the number to show in JSON format. Here is an example that would show “1” on the counter:

OK, so I need to provide the number of people who found a job thanks to OpenClassrooms this way. However… I started to wonder: where do we store this data? 🤔

Seriously, where is our data?

Turns out… we don’t have it in a formal way yet. We don’t even have a table set up in our database for this. Of course, some developers are actively working on it, but what can we do in the meantime?

I started bootstrapping the simplest solution I could find (I take my concierge role very seriously!). Each time we would hear about someone who found a job thanks to OpenClassrooms, we would add their name in a Google Sheet just like this:

Image for post
Image for post
We started logging who found a job using a simple Google Sheet

That’s a start. At this point, I wanted to get the number of lines in this file to generate a JSON that would be read by the counter. I knew that Google Sheets would have a comprehensive API, but I wondered how I would generate the JSON. Would I need to set up a server just for this?

The answer is no: you can do everything thanks to Google Apps Scripts! 😃

Playing with Google Apps Scripts

From the Google Sheet, I just had to click on the Tools / Script Editor menu to go to Google Apps Scripts:

Image for post
Image for post

There, after reading some documentation (and copy-pasting random code, I must admit!), I found a way to read the number of lines in the Sheet and to generate the JSON I needed:

There are 4 lines in this function:

  1. Get the active sheet object
  2. Count the number of lines (I had to deduce 1 to avoid counting the first line)
  3. Generate a JSON string just like {“number”:1}
  4. Return this string to the requester

Now, I had to save this version of the code, using the “File / Manage version” menu. I clicked on “Save new version”.

Finally, I clicked on the “Publish / Deploy as web app” menu. I had to configure it to be available to everyone, even anonymous users, so the counter could read the page:

Image for post
Image for post

The URL at the top of this popup is where the page would be hosted (something like https://script.google.com/macros/s/xxxx). I just had to copy/paste it in my browser to get the confirmation that it works:

Image for post
Image for post
Woohoo! The URL successfully returns the number in JSON format!

Last step: connecting to the counter

The last step is simple: I just had to go to my Smiirl account to connect the counter to the URL given by Google Scripts:

Image for post
Image for post

And… voilà! This is all that needed to be done. Now the counter reflects the number of lines in the Google Sheet in real time. When we add a new name, the counter increments.

Image for post
Image for post
The counter reflects the Google Sheet in real time!

When we hear the counter flapping, everyone is happy: it means we just helped a student to find a job. Mission accomplished! 😎

Written by

Entrepreneur, author and co-founder at OpenClassrooms

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store