Code in RHA

Being a software engineer, I like to solve problems. Especially the problems which take up a lot of time and effort. Any problem which is also repetitive. At work, we figure out what issues the customers are facing, how can we solve them and make their lives a bit easier? How does this apply to the real world, where you don’t have customers? How do you figure out their problems? Even if you do write code and make life easy for someone, how will you get it verified? How do you account for feedback?

At Robin Hood Army, we have a lot of work in reporting and dashboards and leaderboards. Surprising, right? At the beginning, I was too. But then it started making sense when I saw how they were being used. All of RHA’s communication with its media and food partners require some sort of reporting and statistics. Especially social media statistics, where every city is ranked across multiple metrics and the final colour code lets them know of their performance.

The final SM leaderboard

SM reps(SM representatives for each city) often share this information with their city teams, and SM reps are encouraged to keep their city in the green zone.

All the data comes from a few Google spreadsheets. For example, cities come from a master sheet, and are imported into the SM Data input sheet:

SM data input sheet

Each zone is assigned to one SM coordinator for that zone and he/she is responsible for filling the data for the cities in their zones. Once the data for 150+ cities is filled, now starts my work:

1. Double-check all cities which are marked in red.
2. Prepare the leaderboard as per the first image.
3. Send it across the SM groups for all the SM reps to share among their teams.

Problems:

1. Double checking all cities takes time -> 2 hours at least, and is not foolproof.
2. I am quite bad at formatting and presentation.
3. The expectation is that irrespective of who builds this leaderboard, the outcome should be consistent: same formatting, same structure, same ordering.
4. Once this has been published, there come a deluge of messages from SM reps saying their cities are marked wrongly, and sometimes they are right. In this case, we need to issue a correction. Issuing a correction means re-doing the leaderboard, which means more than 30 minutes will be required.
5. The number of cities is constantly increasing, which means we cannot use the same file over and over again, as the number of cities per column needs to be tweaked for easy viewing.
6. People like me, working on macs, are not able to quickly do all these operations because the Numbers app isn’t capable of formulae and queries.

Solution: Use Google sheets itself to build the leaderboard using Google Script.

Approach:

1. Create a new sheet which queried the “Data input” sheet as per the SM leaderboard criteria.
2. Order the cities as per their colour code and names in the ascending order. Green first, Yellow next and Red last.
3. Take number of cities per column as a user input in one of the cells of the intermediate sheet:

4. Keep 2 buttons so that user can reset and create the leaderboard:

Note that “reset” and “recreate leaderboard” are supposed to be buttons!

5. Write code that will:

1. According to the number of cities per column, and total number of cities, copy the headers in adjacent cells.
2. Copy the cities and their evaluation results onto each column.
3. Merge the blank set of cells between two columns(for easy viewing)
4. Merge the cells in the last column, which could be partially filled.
5. Format every city cell by setting background colour as green, yellow or red as per their contents.
6. Set background colours, borders, font sizes, weights, text overflows, to the entire table as the process team head wanted it.

Once all these were done, the final result was a completely automated leaderboard, which takes less than 2 minutes to build. In case we need to issue a correction, we take 2 instead of 30 minutes. If I am unavailable, somebody else can click the “Recreate leaderboard” button, and the board will be generated in the same formatting and styling.

I tested this by myself, playing around with the configurations and found it working. How do I get this verified or user acceptance tested? This is now the tricky part. Now there are no users as such here. Either I am creating the leaderboard, or some other Robin is. Not all Robins are tech savvy, and are familiar with coding. They need clear steps on what to do, and the code part is completely abstracted.

Writing and testing this much code may not be possible by all Robins

User manual inside the sheet itself

After discussing with other Robins, some who are not even part of this team, I got great positive feedback. The benefits of changing values in one place and seeing them reflected everywhere else and the time saved in recreating the leaderboard 3–4 times in 2 hours, is worth spending 1.5 days over Diwali.

I learnt the Google Script engine, wrote all the code locally(which took me half a day to figure out as Google’s script editor is ridiculously slow and lacks IntelliSense), and also spread the knowledge to another Robin, who had asked me something similar a few weeks ago. This is just the beginning, and the plan is to automate as many such repetitive tasks, saving as much time as possible!