Improve your reading ROI by building your own knowledge tracking system - Part 2 : Build the app

This is Part 2 of the “Improve knowledge conversion with book tracking” series. In this series, I will explain why we should build a tracking system to help converting what we read into actionable knowledge.

In Part 1 I discussed why we need to take an investment view to scrutinize the output of what we read. In this post, I’m going to show you how I build the book tracking system using Google Sheets, Apps Script and AppSheet. Integrating these 3 platforms, we can quickly create a simple web application that performs CRUD (create, read, update & delete) operations on data.

Create a Google Sheets worksheet to store data

1. Sign in to Google Sheets with your Google Account.

2. Create a new worksheet named MyBooks with the following columns. Add any other columns that you like.

   Column	Name
   |	Timestamp |
   |	ISBN |
   |	Title |
   |	Author|
   |	Year |
   |	Genre |
   |	Language |
   |	Description |
   |	Status |
   |	DaysRead |
   |	Rating |
   |	Notes |
   |	CompletedTime |
   |	ReadAgain |
   |	NextStep |
   |	DaysAfterCompleted |

3. Rename the sheet to books

4. We will capture the book record creation and completion time in the AppSheet UI. With these 2 values, we can use Sheets’ built-in functions to calculate the days we’ve spent on this book by entering the following formula in cell J2 of the DaysRead column

   =ARRAYFORMULA(if(ISBLANK(I2:I),,SWTICH(I2:I,"reading",DAYS(NOW(),A2:A),"completed",DAYS(M2:M,A2:A))))
   

   What the formula does is to check if the status is reading or completed, and calculate the time elapsed (from start till now, or from start to finish) accordingly.

5. Similarly, for the DaysAfterCompleted column, we use the following formula in P2 to check how many days has passed since completion.

   =ARRAYFORMULA(IF(ISBLANK(M2:M),,DAYS(TODAY(), M2:M)))
   

Create a Apps Script to look up book metadata

With our worksheet ready, we need to find a way to search for its information. The ISBN of each book should be a good enough unique identifier (UID), so we will use public APIs that let you lookup books by ISBN.

When you think of books, Amazon and other book review sites immediately come to mind. Unfortunately, Amazon provides no such public API and GoodReads has closed their API to developers as of 2020.

Again, Google can be your good friend (at times!) here as it’s Books API is public, free and well documented. I wrote a script to perform the query, with the following in mind,

1. I make sure each function has single responsibility, for readibility and maintenance.
2. I only query for the last added row in the spreadsheet, as in my user scenario, the existing rows were all added previously from AppSheet and have been looked up properly. So it’s not efficient to parse all existing rows in the spreadsheet whenever a new row is added.
3. If the user modifies an existing row, e.g., edits the author name or description, the script will not be triggered to query again.
4. I retrieve many fields such as title, author, published year, genre, short description, language, etc. whenever I call the lookup API. This is for more granular breakdown of the type of books I spent time on. Note: Google Books API actually allows you to query using metadata other than ISBN (e.g., author, title, etc.), and then retrieve addtional information such as publisher, price, cover image, subtitles, page count, rating, etc. You can examine a sample JSON file to see what you can get and adapt the script as you need.

The script

You can add the following code by invoking the Sheets menu Extensions/Apps Script and pasting it to the code.gs editor. You can also find the script at my Github repo.

const API_URL = "https://www.googleapis.com/books/v1/volumes?country=US";

// Our Google Sheets sheet
const SHEET_NAME = 'Books';
// Our book info columns 
const COLUMN_NUMBER_ISBN = 2;
const COLUMN_NUMBER_TITLE = 3;
const COLUMN_NUMBER_AUTHORS = 4;
const COLUMN_NUMBER_YEAR = 5;
const COLUMN_NUMBER_CATEGORIES = 6;
const COLUMN_NUMBER_LANG = 7;
const COLUMN_NUMBER_DESCRIPTION = 8;

function main() {    
  // get current spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // get Books sheet
  var sheet = ss.getSheetByName(SHEET_NAME);
  var data = sheet.getDataRange().getValues();
  var lastRow = sheet.getLastRow();

  var isbn = data[lastRow-1][COLUMN_NUMBER_ISBN-1];
  var title = data[lastRow-1][COLUMN_NUMBER_TITLE-1];
  
  // We will only do a query if the ISBN is newly entered, i.e., the title field is empty. 
  // We will not query again if the user merely changes the information in the row such as 
  // edit author name, etc.
  if (isbn!='' && title == '' ) {
    // get book info from Google Books API with ISBN from last row  
    var book = getBookByISBN(isbn);

    if (book) {
      // extract book info
      var bookFields=extractBookMetadata(book);
      // update spreadsheet
      sheet=updateSheet(sheet, lastRow, bookFields);
      // apply all pending spreadsheet changes
      SpreadsheetApp.flush();                 
    }
    else {
      // write error into Title cell
      sheet.getRange(lastRow, COLUMN_NUMBER_TITLE).setValue('Cannot locate book by this ISBN');
      return false;
    }
  }
}

// invoke Google Books API to get a JSON file of the book info
function getBookByISBN(isbn) {
  
  var url = API_URL + "&q=isbn:" + isbn;

  // Make a GET request using the query string constructed above
  var response = UrlFetchApp.fetch(url);
  var results = JSON.parse(response);
  
  if (results.totalItems) {
    // for multiple results
    // get only the 1st item that should be closest match
    var book = results.items[0];
    return book;
  } 

  // our search returns no result
  return false;
}

function extractBookMetadata(book) {
  var title = (book["volumeInfo"]["title"]);            
  var authors = (book["volumeInfo"]["authors"]);
  var year = (book["volumeInfo"]["publishedDate"]);
  // extract yyyy from published date
  if (year != null ) {
    var year4 = year.substring(0,4);} 
  var categories = (book["volumeInfo"]["categories"]);
  var lang = (book["volumeInfo"]["language"]);   
  var description = (book["volumeInfo"]["description"]);  
  // searchInfo.textSnippet is shorter than description
  // If this also exists, overwrite the description above
  if (book["searchInfo"] != null){
    if (book["searchInfo"]["textSnippet"] != null) {
      description = (book["searchInfo"]["textSnippet"]);  } }    

  var bookdata = {title:title, authors:authors, year:year, categories:categories, lang:lang, description:description};

  return bookdata;
}

function updateSheet(sheet,lastRow, bookFields) {
  var s=sheet;        
  // write respective info into column cells of last row
  s.getRange(lastRow, COLUMN_NUMBER_TITLE).setValue(bookFields['title']);
  s.getRange(lastRow, COLUMN_NUMBER_AUTHORS).setValue(bookFields['authors']);
  s.getRange(lastRow, COLUMN_NUMBER_YEAR).setValue(bookFields['year4']);
  s.getRange(lastRow, COLUMN_NUMBER_CATEGORIES).setValue(bookFields['categories']);
  s.getRange(lastRow, COLUMN_NUMBER_LANG).setValue(bookFields['lang']);
  s.getRange(lastRow, COLUMN_NUMBER_DESCRIPTION).setValue(bookFields['description']);

  return s;               
}

Setup script execution

After saving the script, we can now test it manually to make sure that it does what it is supposed to do.

1. Enter 9781999449032 in B2 cell of our Books sheet.
2. Go back to the Apps Script window. Click Run on top of the editor to run our script. You should see logging results at the bottom pane indicating status.
3. Switch back to Books sheet and you’ll see that the associated book information has already been filled in the row.

Since we want to automate such lookup whenever we enter a new book, we need to set a trigger for the above script to execute. To do so,

1. Click the clock icon on the left pane.

2. Click the blue + Add Trigger button on the lower right corner of the window.

3. Accept all default values except the following. This is to ensure that whenever we make a change to the spreadsheet, the script is triggered to execute.

   • Select event type: on change

   click Save.

4. Now go back to Books sheet and add another ISBN in B3 (e.g., 9780553804577). Once you tab away from the cell, notice that our script is executed automatically and the related info is filled in the row!

Create an AppSheet app as the frontend

Next we need to build a mobile-friendly frontend for us to perform CRUD operations (create, read, update and delete) on our book records. As I elaborated in Part 1, we will use AppSheet to do this.

You can create a new AppSheet app in two ways

1. Start from our books worksheet by clicking the menu Extension/AppSheet/Create an app. When you do this, AppSheet will automatically assume that you are using this worksheet as its datasource and setup your app project accordingly.
2. You can also go to the AppSheet website to sign in with your Google account to create a new app. Then choose Start with your own data.
   • Name your app MyBooks and choose a category such as Productivity or Personal/Fun. The latter really doesn’t matter. Choosing one way or the other just prompts AppSheet to suggest some sample apps for you.
   • Click the blue Choose your data link and in the Select a File screen, choose the Sheet we created previously from your Google Drive. Click Select. You can also choose any data source (e.g., MS Excel, SQL database, etc., that AppSheet supports)

Since we already have a Sheet open to work on, we will use option 1 to let AppSheet associate our sheet with the new app.

After AppSheet is done initializing, it will show you a phone emulator interface on the right with a default list view of our sheet.

It looks simple enough, isn’t it? Although we would like to make sure the ISBN is formatted right and more book data can be surfaced. So let’s hop in and choose Customize you app to enter the edit screen.

Customizing data source

Each worksheet will be mapped to its own table as a unique data source in AppSheet. If you have several worksheets in a file, you can create multiple data sources in the same app and display the different tables in separate views. (Same for meshing different data sources such as pulling from Sheets, Excel and a SQL database in a single app).

In our example, we have only 1 worksheet books so this is the only table we will work on for the time being.

In the Data tab, you can edit the table transformed by AppSheet and its columns. Click on the Book table, you will see that you can make the table read-only (so the app user can view its content but not edit it). You can also filter out all existing entries so that users can create new entry but not edit existing ones via the Security section of the Table. In addition, if you want to share the app with friends, you might want to set security filter so that only rows created by the currently login user is shown. There is indeed a myriad of options to configure the app.

We are more interested in configuring the columns that the table itself. To do so, click the Columns tab or View Columns button in the Table tab.

Notice that AppSheet already selects ISBN as the key (UID) of each row. And it has adopted the types used by the columns in Sheets.

You can change column properties by clicking on the pencil icon on the left of the column name. Just like the table, there’s a myriad of options for you to configure a column including name, types, visibility, default value, whether it’s required, editable, input validation and any formula applied.

Editing ISBN

ISBN should be write-once, read-only after. Its sole purpose is to be a unique identifier (key) for records in our app, so it doesn’t need to be visible for editing after the initial data entry.

We will first change the ISBN row as follows,

• show: unchecked, then click the inverted glass icon to enter this expression: =ISBLANK([_THIS]) in the Expression Assistant. You will see that AppSheet also provides many prebuilt expressions, and there is a convenient Test feature to test the validity of your expression on the existing table.

  Click Save to save your expression for this field.

• Type Details/Show thousand separator: unchecked

• Update Behavior/Editable: unchecked

Click Done to save our changes.

Editing Status

Status should be an enumeration with predefined values. We will change the type to Enum and then add the valid values in the Type Details section as follows.

Considering that when we add a book, we are usually in the state of starting to read it. So we should update the Auto Compute/Initial Value field to the expression ="reading"

Editing Timestamp

Timestamp (both the add and completion) should be automatically filled in by the system to avoid inaccurate entry. So we’ll make sure this by editing the Timestamp column’s AutoCompute/Initial Value. And we find out that AppSheet automatically applies the NOW() formula to this value. Nice touch! Just make sure Update Behavior/Editable is unchecked so it is write-once.

Editing CompletedTime

The CompletedTime is only relevant when we finish a book, i.e., when the Status flag is completed. Moreover, its sole purpose is to be used to calculate the elapsed time, so we really don’t need to surface it. So we will edit it as

• show: unchecked
• type: ChangeTimeStamp. This data type will record the current time when a specific column has changed to a particular value.
• Columns: Status
• Values: completed

Editing ReadAgain

This value is only relevant when we finish a book. So we will make the following changes

Show: uncheck, then enter AND([status] <> "reading", ISNOTBLANK([status])) as a formula Type: Enum Values: You can add any value you want. I use the followings

Editing DaysRead, Year & DaysAfterCompleted

We also change both DaysRead and DaysAfterCompleted to a Number type. They both require an additional step as AppSheet defaults them to a DataTime type and assigns them initial value of the current time. To change both to a Number type, we need to remove the expression in the Auto Compute section used to calculate the initial value first.

We should uncheck the show thousands separator for Year column.

In addition, we will uncheck Update Behavior/Editable for all 3, since we will rely on our Sheets formula to calculate these columns instead.

DaysAfterCompleted should only be displayed when we finish a book. So we should set its Show field to the expression =[status]="completed"

Finally, change Rating to a number, uncheck the Required field for both Year and Description. We will leave other columns as-is from now.

Click the Save button on the upper-right corner of your screen to save our project. Notice that the phone emulator will update whenever we make a change.

Add a book

By default, AppSheet will display the table in a List View. Clicking + will invoke the default Add View where you can enter a new book.

Now let’s add a new book. Notice that as expected, timestamp captures the current time for us. Enter 9780525512189 in the ISBN box and hit Save.

You will notice the Refresh button on the upper right turns red. Click to refresh and re-sync the app with the updated data source (our Books sheet). Wow, we’ve succesfully added a new book record. You can also go back to the Books sheet to verify that a row is indeed added.

Edit a book

Click a row and AppSheet will use the default Detail view to display the details of a row. Clicking the pen icon will invoke the default Edit View where you can edit the details of the current book.

In the Edit View, let’s try inputing something and verify that it works. Just type anything in the Notes input box and hit Save.

You can verify the change by refreshing the List View or viewing our Books sheet. Our first note is successfully captured!

Moreover, you can delete a book by just clicking on the trash bin icon on the top.

As you can see, each view will have its own embedded view (List->Add, Detail->Edit). Even without much customization, AppSheet has built something that is quite functional for us.

Customizing our views

In the UX pane, you will see the default views we’ve experienced so far. Primary Views such as a List are those that will surface as tabs in our app, whereas Ref Views are those that are invoked by specific actions such as clicking an Add or Edit button. Now let’s customize each of them to suit our need!

Primary Views

Click the Books view under Primary View to edit it. Notice that by default, a Card view is chosen to display our rows. In addition, it is grouped by status and further sorted by the time created. It even nicely shows the number of rows in each status category by count aggregation!

You can flip around the different view types to see which one fits your need better. For example,

• if we fetch the book cover image from Books API, we can use the Gallery view type to display our books like in a visual bookshelf.
• We can also employ the Table view type to display our data in a more traditional spreadsheet like format.
• Or we can use the Deck view type to display each book in a single page, then use the swipe action to view other books.

We can leave the view type as-is, but further fine tune its Layout section.

Accept the default list but change the followings

• Click Title goes here and on Column to show, choose Title
• Click Subtitle goes here and on Column to show, choose Author

Since we haven’t fetched the cover image, we can leave the paper airplace icon choice blank. Dataset with photos that can uniquely distinguish a row (such as inventory management, employee records, etc.) can also use the other 3 layouts that give a much bigger emphasis on the photo, as well as include more information on each row.

Notice that our emulator on the right shows a refreshed view of what we’ve changed. Now it looks more informative!

Ref Views

We can now view our data easily. Now let’s customize the Create and Update operations by modifying the Ref Views.

Click the Book_Form view to edit.

What’s important here is to enable Auto Save. You can also customize the Column Order to rearrange (or omit) which column you want to allow editing. In addition, you can use the Finish view to pick what view the form will return to, when you finish editing it. You can choose between the Books list view (our primary view when we launch the app), or go to the Details view of the book you’ve just edited.

Similarly, you can modify the way data show up in the Books_Detail view.

Secure our app

In order to use the app from our mobile device, we need to setup authentication so that we can properly sign in. To do so, we use the Security tab.

• Require user sign in: on
• Authentication Provider: Choose which provider you want to use an account to sign in
• Allow all sign-in users: turn off unless you want to share your app with everyone using a link
• Manage Users button: Similar to sharing in all Google apps, add yourself or someone you want to share the app with. You can fine-tune the access level to Use App (user can login in and use the features provided by the app), View Definition (user can view the AppSheet project but cannot edit), Edit Definition (user can edit the AppSheet project). By default, your account has been added as the owner.

Now click Copy Sharing Links and copy the Browser Link to open the app in your mobile browser. After logging in, you should see the interface (very similar to the phone emulator) and be able to tab around and edit!

Conclusion

In this post, I’ve walked you through creating a web app to create, view, update and delete data from Google Sheets. Using the same techniques, you can also connect AppSheet with other data sources and quickly prototype a data-oriented mobile solution.

In Part 3, I’m going to show you how to implement metrics in AppSheet so that we can have a visual view of our time investment. Stay tuned!