This is Part 3 of the “Improve knowledge conversion with book tracking” series. In this series, I will explain why we should build a tracking system to assist in converting what we read into actionable knowledge.
In Part 1 and Part 2, I’ve explained the why and how of the book tracking system. Now let’s dive into implementing the metrics to track our investment.
How many books am I reading now?
In our daily lives, many of us have the experience of spinning up too many untied threads, but yet to finish most of them. This can be a blog post we are drafting, a side project we dive in with great passion, or a book with an attractive cover! Without proper tracking, many of these start-as-promising things recede into the background and never see the light again. This is especially true for library ebooks that are free and easy to download in a batch. Let’s give our books the exposure they deserve by implementing some views in AppSheet that show what we are reading now. This not only gives you visibility in all your open books, but can have a nice side effect of warning you against hoarding :-)
A piechart by status
To start, let’s create a simple pie chart grouping books by status.
Let’s go to UX/Views and click + New View. We’ll rename the view Progress and use Books as the data source for this view. Then choose Chart as the View type and Aggregated Piechart as the Chart type. In Chart column, add Status. Accept all other default values. In the phone emulator, you will see a nicely formatted piechart showing the number of books grouped by status. Such a view gives you a quick glance on whether you are reading more than completing, or whether you’ve been aborting more books than finishing them.
Similarly, we can create charts showing us the ratio of books we read in different languages, genre and published years. We can also chart ratings and readagain fields to see if we are finding our investment valuable.
A list of books in reading
To keep tab on the books we read, we will further utilize a slice, which is a filtered view of a table. To do so, go to the Data/Slice tab. When we click Learn More under Add slices of Books, AppSheet already guesses what we want and suggest we create 3 slices based on the Status field. Spot on! Let’s click Use Suggestion to see what we’ll get.
We are now ready to create a list of the books we are reading. When we go to UX/New View and choose a Table view type, we can now choose any of the 3 slices we just made as the data source. For our case, let’s choose Status is reading (slice) as our source and pick the Table view type. Name it Reading, and choose the position as left (where it shows up in the app tab). Immediately we can see the new view showing up as another tab in the phone emulator, alongside the progress chart and our original Books view.
How much time do I usually spend
AppSheet also allows us to have a quick view of the time we spend on our books by way of a histogram.
To do so, in UX, create a new view with our Status is Reading slice as data source. Then choose the Chart view type and Histogram as the chart type. Add daysRead as the chart column. You will then get a histogram showing the distribution of books grouped by the days you’ve spent reading them. To illustrate this feature, here’s a screenshot from the real app I’ve been using for 7 months. As you can glance from the chart, quite a lot of buckets on the right (longer daysRead values) have a high count, alerting me that many books remain unfinished. Perhaps I should trim the queue to be more focused?
In the same way, you can create slices that filter those books you mark as reference after completion (using the readAgain field), or those whom you rate the highest. You can then implement custom views to show them in the app to have a shortcut for books you find useful.
Reminder after book completion
As much as we are impressed by the ideas we acquired from a certain book, we are bound to let them fade into the background after some time. To remedy this, I implement a reminder in AppSheet that will send me a message 90 days after I completed a book. The objective of this is to let me ask myself if any of the takeaways (noted in the nextstep field) from this particular book has changed my life, and if I’ve internalized anything at all.
- Go to Automation/Events and add a new event
- In the new event configuration, enter the followings. Make sure to change to your time zone and choose your preferred time.
The Automation features are very helpful assistants as you can also automate other tasks such as sending you a weekly report, text you when some trigger happens, etc.
Give your app a personal look
You can customize various fit and finish of your app, including a splash screen, color theme, logo, which view to open initially, etc. in the UX/Branding and Options tab. You can even add localization strings there, and configure special formatting rules for your data.
Share the app with family
One of the benefits of AppSheet is the built-in authentication mechanism so that you don’t need to write your own code to authenticate and authorize users. All you need to do is to set who to let in and what they should see.
We already cover how to share the app by setting security levels in Part 2. Now we will look at setting the scope of data each user can access.
- Go back to our Google Sheet and add a new column
useremail - In AppSheet, go to Data/Columns and click Regenerate Structure. This will pick up any change in our sheet columns and reflect here for us to do further configuration.
- Notice that the useremail column is automatically pick up. Let’s edit it by changing the type to Email and in Auto Compute/Initial Value, add
=USEREMAIL(). Now whenever you add a new record, this field will be assigned the value of the email you use to login to the app. - Go to Security/Security Filters. Click Books, and in Security Filter, enter
USEREMAIL()=[useremail]. This means that when the app is loaded, only those rows whose useremail column values match the currently login user will be shown.
What I get from my app
As I stated in part 1 , one of the issues I have before this project was the lack of metrics and visualization of my time investment. This new setup has so far provides me with the followings,
- enables much easier tracking of
- how much time I spent on each book
- what kind of books (per genre and language) I read most
- a list of books in reading (to deter me from starting yet another book!)
- how many “gems” (books that I will keep going back to, or act as a reference) I have discovered, versus how many I have aborted
- how many “knowledge blocks” I have extracted from my invested time, and whether I have acted upon them
- reminds me at a specific time after completion, prompting me to reflect on whether I’ve taken my learnings into everyday action
- allows me to jot down notes anytime, anywhere
- gives family and friends access to the app to encourage reading habit and share learnings, a very nice quality activity.
This is the first app I built with AppSheet and of course I didn’t stop from there. I find it super easy to create an inventory tracking system for my garments, garage hardware and things that are small, stored in different places and hard to centralize for visibility purpose. I actually feel that this model is a great companion if you practice the KonMari or Danshari methodologies for self-management and clarity. Don’t just just limit it to your own space. Why not use it with friends and family to keep track of tasks and resources for a party, trip or project that require collaboration?
Next Steps
AppSheet is a powerful platform that provides additional features that I’ve yet to explore. As I accumulate more data, I would like to try the Predictive Model to see what kind of books it can guess I’ll like based on existing data. I’m sure that will be fun!
Right now my Google Sheets is quite slow as it automatically refreshes formula calculation every time something changes, a feature that I can’t turn off. I expect this problem to get worse as more rows are added. My short-term workaround is to automate the calculating in Apps Script to control when to turn it off.
In addition, AppSheet doesn’t allow creating pivot tables as a view, which is one blocker for my metrics as I’d like to check ratings/readagain per genre and language. A quick workaround is to do that in Sheets itself and export the pivot table out as a new data source in AppSheet. A better workaround is to use Google Data Studio which provides much more powerful dashboarding features to track the metrics. In a separate project, I have implemented a health dashboard using Google Data Studio and this is something that I’ll blog in a future post.
In the long run, I’ll move the whole setup to a real database and build a web frontend myself. This will allow me to remove dependencies on relying on AppSheet, as well as improve the backend performance.