Google Appsheet to build a quick journal app

Only recently I learned about Google appsheet. Very quick, no-code way to build responsive web apps with automatic Google authentication. Perfect for building apps for intranet users. I took about 3 hours to build a personal app that helps me maintain a journal. First 2 hours were spent in reading docs and building the app and one hour to beautify it.

Data is backed into a Google sheet. This is a screenshot tour of the app. There are plenty of tutorials, examples and videos on Appsheet site - so not repeating all those here. Only the specific things I ended up doing and things I still need to figure out are going to be here.

App Requirements

1. Should be very quick to enter an entry. I needed something that is like a personal twitter log. Just type in stuff with @ and # prefixed as tags. Autocomplete would have been nice, but not needed. Google Keyboard with voice entry support is super useful. Quick way to record mood (reg, yellow, green) and optional GPS location.
2. Timestamp is auto captured and should be editable on new entry.
3. Should be able to select an entry and edit everything except original timestamp.
4. Should be able to select an entry and duplicate it with timestamp alone defaulting to new time. Others are copied and editable.
5. Should be able to select one or more entries and delete.
6. Tags entered should be parsed out and available for filter.
7. Visualization by latest 30 days as a list, calendar, mood and location.
8. Should be able to edit Google Sheet standalone and data refreshed in the app.
9. Offline data entry and sync should be nice

All of these except one are available with no code in Appsheet. For parsing tags alone, I had to add a Google Sheets formula.

Landing Page

“+” Add Entry

Edit is similar. It has an additional action to duplicate selected entry.

Calendar

Mood is a simple doughnut chart.

Map

Search

All views from the bottom bar filters automatically to the search given. Here is the filtered map view.

Tag list

Clicking on a tag will take you to filtered tag list.

I’d have preferred that it fills the search form at the top and then filters. That is still to be figured out.

App Settings

Appsheet is not free to use. I had to go to app settings and mark category as Personal Solution and choose Personal Use Only checkbox. Because of that, I can’t distribute this. However, for Google Workspace users, monthly billing rates are reasonable to distribute to your own users especially considering super fast way to build apps with responsiveness and offline capability.

Since you can refresh backing data independent of appsheet, your data warehouse jobs can augment the data behind the scenes to provide hybrid functionality apps that support real time, offline and large data summarized.

Data Settings

Filtering

Appsheet supports data sources and data slices within those sources. My data source was a Google Sheet and I created a slice with expression [Date] > (TODAY() - 30) where [Date] is my column name to make a slice of latest 30 days of data.

Formula

For parsing out tags, I had a column named [Tags] for which I added following formula in Auto Compute settings.

trim(regexreplace(regexreplace(lower(RC[-4]),"([#@][a-z0-9_]+)|(.)","$1 "),"\s+"," "))

This formula is copied to backing Google Sheet whenever a record is created. The formula is a regular expression that removes everything other than words starting with @ or #. Inner one replaces other words by spaces and outer one squeezes spaces to just one space. So you get a space separated tag list in the [Tag] column.

Tag list

For building the Tag List, a new tab called Tags in the backing Sheet was created. A formula was added to A2 to get sorted unique list of tags (column F) from the sheet that stores journal entries.

=SORT(UNIQUE(FLATTEN(ARRAYFORMULA(IFERROR(SPLIT('JournalEntries'!F2:F, " ", TRUE, TRUE),"")))))

In this tab, column B has the count - that is a simpler formula which takes value from tag column and does a wildcard count on column F (Tag) on entries sheet to check if tag is contained there. Since I don’t expect to have more than 100 tags ever, just copied this cell formula to 100 rows.

=IF(A2<>"", COUNTIF('JournalEntries'!F:F, "*"&A2&"*"),"")

What Next

1. Adding a mobile desktop icon. Currently I just navigate to this page and leave it open in a tab on the mobile browser. Mobile desktop icon automatically links only to overall appsheet site editor and not to particular app. Also, it somehow seems to loose my Google login, forcing me to login every time.
2. The Map entry when you are recording an entry later is a bit difficult to use. Two finger scrolling doesn’t work on the map widget. So one has to zoom out, move the pin around, zoom in, place the pin etc. It seems to be designed specifically for “get current location” only. Workaround is to copy lat/long from Google Maps and paste it.
3. I am using timestamp as primary key - not good taste! Appsheet provides UNIQUEID() function which I should’ve used - too lazy to fix existing data with that.
4. To distribute with an ability for people to create their own backing Google Sheet. This is critical for personal apps.
5. It supports events and actions. Technically, that should be enough for me to schedule reminders too as a future journal - should be fun to implement.

Overall, this is a fantastic platform.