Automating tasks at work using spreadsheet applications
I started writing this post on Thursday, 13th September at the moment I was able to get my google script to work. It was 1:45 AM (WAT).
FWIW: One of the beauties of spreadsheet applications is that they always have somewhere to script (write code that makes content show up on the actual sheet with the rows and columns).
For Microsoft Excel, the programming language used to script is Visual Basic, while for Google Sheets it is Google Script (a variation of JavaScript).Background
I work as a Product Manager but I sometimes find that my front-end developers are swamped with a lot of work (backlog). So, I can’t keep requesting “nice-to-have” features.
Typically, it has been argued that to be a Product Manager you have to possess some software development knowledge and that’s a reasonable ask. Because, how do you manage what you don’t understand? But, I digress.
Statement of the problem
I needed a way to cross-reference a specific project’s activity with the corresponding user growth data. Perhaps, I might be able to find a correlation and hopefully a causation for why we were incurring such high costs in an area of the project.
In a short time, the devs had been able to produce a dashboard for the executives to see the most critical numbers, like total users, volume, and value of transactions. But I’m not an executive, just an employee who also needs to make sense of data to be presented to the execs.
Approach
So, I requested for the direct APIs (come in the form of URLs) which the front-end engineer used to populate the dashboard.
To test the APIs (aka see the response data), I put it into Postman (it’s like a web browser, used for testing APIs), and voila!
So, I had the response from both APIs in their separate tabs.
So, what I first did was to manually scan the response data in search of the specific key I was looking for. I would then enter it into a spreadsheet per day to form a table.
But after doing this for the first three days, I realised it was not scalable and this project was going to be on for months. So, I went in search of a way to automatically feed data about the specific things I was looking out for in the response into my spreadsheet.
In the next post, I will dive right into the technical bits and give a conclusion.
In the meantime, you can reach me on Twitter. Also, you can become part of the DadaBen clan, a crop of people I share my life and work with.
