This post outlines my experience building a web-based tool that makes learning complicated information quick and effective. You add information to the system in the form of cards, and the app works out which cards you need to review on what days.
The Problem
Trevor Klee is a private tutor who specialises in helping students prepare for standardised tests, like the GMAT and MCAT. He sometimes uses a Google spreadsheet to compile links of relevant topics for his students. They use the spreadsheet to review the information and then assess their understanding after each link. Depending on their performance, they mark the link for review the next day, in 3 days or a week later. Here is what a spreadsheet looked like in practice.
The process is tedious, and Trevor wanted a simple application that took care of all the details.
There are existing apps for this, Anki being the most popular among them. Trevor had two requirements that Anki fell short on. The first was that he wanted all the information to be stored on a Google spreadsheet. The goal behind the interoperability with Google sheets was to let students switch between using the app or studying directly from a spreadsheet without the web app. It was also important to be able to import and export performance data so that tutors and teachers could see the work their students were doing, and where they were struggling.
The second requirement was filtering by subcategory. Students should be able to only study grammar and poetry cards in the Literature category of an English language deck. This kind of tagging and filtering is not something you can do with Anki. It becomes crucial when you have large decks with hundreds and hundreds of cards.
The Proposal
Once I understood the scope of the project, I put my proposal together.
https://app.hellobonsai.com/proposals/62e306cac25715207e36
(shared with permission)
As with any of my paid proposals, I outlined my understanding of the problem, how I planned to tackle it, designs for what my solution could look like, any options I could offer, what they would cost and how long it would all take.
A proposal helps bring any misunderstandings to the surface and ensures everyone is on the same page before work begins.
Case in point, I completely overlooked the ability to create, copy and edit Rich Text.
The !$%*#^@ Rich Text Editor
Rich Text means preserving things like bullet points, heading sizes and italics. Rich Text editors are notoriously tricky to work with, and to complicate matters, Google sheets do not render Rich Text nodes.
Not being able to copy in Rich Text was a deal breaker, so I set out to build a proof of concept first. The idea was to create a mini-feature that let me copy Rich Text into a simplified version of the app.
Rich Text wasn’t part of the original proposal. We came to an arrangement where I would spend up to a week understanding if this could be done. If I couldn’t figure it out, then I would absorb the cost of the extra work. If I figured it out, then the rich text feature would cost an additional thousand dollars.
I managed to get a proof of concept working. There were still edges case errors when copying in Rich Text from different sites on the internet. Since I hadn’t yet begun work on the project, I proposed only charging half the price for the feature. Trevor could use the other half to find someone experienced with Rich Text editors to work on the edge cases in parallel. If he couldn’t find someone by the time the app was complete, then I would finish the job. Trevor was happy with the win-win arrangement, and the project began.
If you’re not a developer, please skip over the italicised text, I explore some of the more technical aspects of the journey in italics.
The solution I arrived at was to serialise the rich text input into a string of markdown text. I could then save the markdown on the google sheet, and deserialise it when I needed to edit it in the app. Google sheets don’t render markdown, but it’s readable, and Trevor was okay with the tradeoff.
The alternative would have been to save each card to a google document. That would have meant an entire folder full of documents for every deck. Retrieving, or god forbid, editing, a card would have meant hoards of network calls back and forth. It would have taken ages, and there would be multiple sources of truth everywhere.
The markdown solution was elegant because it keeps all the information in a single Google sheet. A single sheet meant it was possible to share the decks between friends.
A folder full of documents would not have worked because you cannot store relative paths in a google sheet. All the URLs to individual cards would break when a deck gets shared.
As proud as I was of my solution, images presented the same relative URL problem. At first, I tried to sidestep the issue by storing images as base64 strings. A valiant effort derailed by the character limit on each cell. Fifty thousand characters barely gets you a tiny low-resolution picture. In the end, we decided to provide our own file storage so that working with images was fast, and it meant that we could ensure image links would never break.
Development
I like to begin projects by building out the front-end. A front-end first approach lets a client play with the app (using mock data) before I move onto the backend. Adjustments are simpler to accommodate this way. Moving things around on the frontend is usually fast and cheap. A last-minute change to the back end, specifically the shape of data that flows through the system, can be complicated, error-prone and expensive.
When I applied to work on this project, I explained that I did not have any experience with one of the critical pieces of technology involved, AppScript. I wanted to learn it so I’d be happy to work on the project at a reduced rate. I was surprised that I got a response. Trevor was okay with my lack of experience as long as I took the project seriously.
As much as I wanted to start work on the front end, I had never worked with AppScript before. It was more important to make sure I could get everything working first. If there were critical problems to deal with, Trevor wanted them handled early on.
Straight out of the gate, I ran into some trouble setting up the Google login functionality. Then I learned that AppScript does not let you read formulas or dates from a Google spreadsheet. It was one technical hiccup after the other.
Publishing a script as a web app for anyone to use on a client-side application would not work. Appscript scripts are meant to be personal utilities that you can use with your suite of Google products. They are not public APIs. Everything worked fine in the simplified-proof-of-concept-app because I was publishing the script and then using it from the same account. The moment you try and allow anyone to use the script with their own Google drive, cross-origin resource sharing becomes an issue.
I tried rewriting all the functions as POST methods. I tried looking for client-side wrapper libraries that would help, then I considered abandoning AppScript altogether and just using drive’s APIs. Finally, the solution was to publish the script as an executable API and interact with the script using the AppScript API. I spent two days in configuration hell setting up OAuth and Google Cloud.
Apart from the authentication snafu, another surprising and painful gotcha was realising that you also can’t read dates from cells when using the AppScript API. I ended up adding words to dates for a ’21 of aug of 2019′ workaround. If you scroll down to the bottom on the apps script API website, there is a ‘limitations’ section that covers this issue “Only basic types such as strings, arrays, objects, numbers, and booleans can be passed and returned.” Dates and formulas are not basic types.
Another small problem was that Google sheets don’t automatically add new columns to a sheet. It creates columns A to Z, and that’s it. Each column at the end of the spreadsheet corresponded to a review attempt. I had to predetermine an explicit number of maximum attempts rather than just creating new columns when needed.
Overall I was running into the fundamental limitations of what AppScript lets you do. Limitations like string searches, the ability to regex or the speed of computation. There would have been no way to know any of this other than to do what I did. I’m grateful for the opportunity to work on a non-trivial project like this and learn all of this stuff.
After roughly 100 hours of backend work, I found my way out of the forest with some beautiful, well-tested code.
The biggest lesson I learned from my experience working with AppScript is that it is slow. Its purpose is to enable interoperability. When I need data from a sheet, I fire off a network call to AppsScript, which then relays the message to the sheet, once the script gets the data it can start processing it and then it makes its way back to me. If you want speed, use a traditional database and don’t run your application off a Google sheet.
That said, it can be done, and we did it.
Looking back on the project, knowing what I know now, there are some things I would do to offset the speed issue. I would move the loading order of the pages around so that you don’t have to wait for data to load a page. The two biggest bottlenecks that would benefit from this optimistic update approach are a) creating a deck and b) reviewing a card. At the moment, there is a noticeable pause when you create a new deck. A smarter solution would be to wait for the response while I let people add information to the deck. I could let you fill out the edit form but block the submit button while everything is processing.
The same approach could apply to review cards. At the moment, you have to wait between each review as AppScript updates the sheet. That could happen while you are reviewing the next card instead.
I don’t regret not doing this the first time around. It sounds easy now, but I’m sure it has its tradeoffs. As a general principle, I believe it is better to get the basics right than to try something smart, as opposed to starting smart and getting it wrong.
Given my lack of experience with the tech, it would have been irresponsible to try and do smart stuff when I had zero experience with the basics. Now that the basics are in place, I can do the smart thing as an upgrade while the app is in production. The upside is that the app gets out the door faster, so people get to use it, and then they get to appreciate the improvement when it gets implemented.
Design
At the time of delivery, this is what the final product looked like.
A simple login page.
The first time you log in the system creates an example deck for you.
Clicking on a deck takes you to a page that lets you add cards or filter a deck.
Studying a deck takes you through each of the questions.
Once you are done you get to a session summary screen.
If you decide to add or edit cards in a deck then you get taken to a card editor.
In Conclusion
The project took 292 hours to build, spread over three months and 21 days. This time included putting a proposal together, all the time spent on emails and communication, designing the system, building it, testing it, deploying it and then writing this case study.
A proportional breakdown of the time I spent on each phase (RTE stands for the work I did on the Rich Text Editor):
In the end, after some changes and additional features, I billed $7000 for 292 hours of work. This comes to $24 per hour. I worked on this project at a reduced rate because I was learning new technology
The project was a paid learning opportunity for me, and I was clear about that from the start. I would have learned AppScript on my own time regardless of this project. A paid project just ensured that I had to learn things thoroughly and get them right.
Some constructive feedback from Trevor, that I was most grateful to receive, highlighted how I could improve integrating new developers into a project. I spent a fair amount of time working with other developers on this project. Directing other developers was not within the scope of my responsibility. However, there is no reason it couldn’t be.
I don’t think I undercharged in any way for this project. I got paid well to learn and apply new skills. If I were to do a similar project with the same scope, I would position my service as a technical consultant, not as a software developer. I would charge between 20 and 25 thousand dollars for the entire project and shift my role to that of a technical lead. This means taking responsibility for hiring and directing any other developers that I need. As a consultant I design, plan, build, test, deploy and manage the entire project.
Trevor was an absolute pleasure to work with. He was understanding and he always showed appreciation for the work I put in. I was given the space and time that I needed. Most importantly, I felt like he trusted and valued my judgement. If you are a professional and you have the opportunity to work with Trevor, I recommend working with him.
I believe the feeling was mutual.
The testimonial below is an excerpt from my offboarding questionnaire:
Overall, it was fantastic working with you. The two big differences between working with you and working with most developers are:
a) You care about your work. Having worked with remote developers a couple times, I’m so used to having to bug-hunt for them, make sure all the features discussed are there, etc. I really appreciated not having to do that with you.
b) You’re self-sufficient. Related to the first, I felt confident that, if I left you on your own, the work would get done well. You solve the problems that come up, without my help.
Overall, it was great working with you. If it’s alright with you, I’d certainly like to continue working with you in the future.
Thank you for taking the time to read through my case study. I thoroughly enjoyed working on this project and I am grateful to Trevor for the opportunity to work with him.
If you know of anyone that is looking to build a web application, or if they just need to consult someone about the technical viability of an idea, please refer me.
If you’d like to use the app, you can start a 7-day free trial over at app.get21stnight.com.
If you are preparing for an exam check out trevorkleetutor.com. Alternatively, if you’re looking for a tutor to help with a tutoring business checkout justaddtutor.com .