In 2019, I was asked to build a daily progress tracker at work. The idea was to get all my fellow colleagues to fill in their daily progress so the manager could keep a check or have an idea about the work done. Not wondering about the project’s utility, or the lack thereof, I started building it because it sounded like a fun project.
I used google spreadsheets. Our team had 8 people in it. I created 8 individuals spreadsheets using a pre-built template. I edited it for my use and eight spreadsheets were ready soon enough. I, then, built a 9th, the master spreadsheet, that would collect all the data from these individual ones.
It was fairly simple. I created a couple of dashboards and it was good to go. Upon usage, I received feedback that people were forgetting to fill in the tracker. So, I built an automated emailer that went out at 7 pm to those who’d missed filling their logs.
All this was done using Google Apps Scripts. They are similar to VBA in Excel and related MS products, but much more easy to use and versatile in their application. Even Stackoverflow has a good collection of commonly used codes.
You can do all kinds of fun. For example – extract emails with certain text or sender and perform way more actions than the filter menu allows. Or, you can integrate drive and spreadsheets. I, for one, automatically log all my Uber rides in a spreadsheet. It happens behind through IFTTT and Google Apps scripts. I also built a small tool where all the attachments from a particular sender got automatically stored to my drive folder.
Anyway, getting back to topic.
The automated emailer removed the biggest hurdle. There was one small issue. Not everyone had work email configured in their phones. Some people often forgotten to fill by 7pm and some only filled at 7pm after the reminder. Often times, they would be out of the office by this time and on the move.
The manager did not care to optimize this and I was reluctant to improve on a less than useful project. It was called a success and was promptly closed as such. I, however, in my personal capacity tried to find a solution.
So, I pretty much shifted the entire thing to shinyapps.io which is the online free hosting service provided for Rshiny apps!
Here’s the link – pulkitsaraf332.shinyapps.io
Check it out and then come back here.
The biggest problem was building a backend. Luckily RShiny has a google spreadsheets packages that can read and write data to spreadsheets. I built my backend on this. The next problem was figuring out how to create user profiles. The shinyapps website allows for user verification but at a cost. I was definitely not ready to pay for a hobby project.
So, I worked out a hack around their user limit. I built a custom login password page myself within the app!
I did this by picking custom username and passwords from, you guessed it, a google spreadsheet. Here’s the best part – with the integrations available in Google Apps Script, you can create a brand new login password using just the link below!!
Here’s how that works:
- The form sends its entries to my gmail
- Gmail auto forwards the email to another ID that hosts the app and app scripts
- The other Google account has a script running every minute that extracts the email body for a specified subject
- The extracted content is pasted to the very spreadsheet that serves as backend to the shinyaps.io code
- Through basic text cleanup, I add the new username and password to the list
- The shinyapps app refreshes its backend every 5 minutes.
All of this without manual intervention and it takes less than 10 minutes total to update! Create a custom login below and try it out right this minute!
Build your custom Login
Now head over to http://pulkitsaraf332.shinyapps.io/test/, refresh the page and try logging in through the newly created username and password!
PS: You might have to wait for 10 mins at max. The tracker itself is incomplete but this is a fun integration exercise 🙂
UPDATE (May 2025): The tracker no longer works on the free hosting. To buy the tracker, email me at pulkit.saraf332@gmail.com or book a discussion slot through my website’s homepage.
Pulkit Saraf 