AppSheet: a pleasant no-code surprise
Google acquired AppSheet in early 2020. I remember the announcement, but didn’t think much of it at the time. 18 months later, and it seems to me that low-code/no-code is such a “big thing” that it’s nudging out AI/ML in the tech press hype. In 2017 I co-founded a start-up where we built a low-code chatbot platform (yep… another one) and we were leveraging AI hype to make our early sales. If I was still involved in the company, I suspect that I would find we were now leveraging “low-code” hype. AppSheet had been going for eight years at acquisition, so it was already mature, and Google have been plugging away bringing it closer and closer to the rest of their tech.
LOB apps
Before I started down the more orthodox path of Computer Science at university, and took my first “proper programming job” as a Java developer, I had various part-time jobs building line of business (LOB) applications. By LOB apps, I mean technology point-solutions that fill gaps that official resident enterprise technology platforms (ERP, CRM) are unable to fill (usually because of prioritisation and people capacity problems, rather than because they are not capable). This was the during the first years of the new millennium — pre-cloud — and the easiest way I found to build these apps was as Microsoft Excel spreadsheets or Access databases, with forms and “code behind” in Visual Basic for Applications (VBA) to provide user interfaces and logic. Distributing these apps to users involved putting the .xls file or .mdb file on a Novell Netware share. This sometimes caused some pretty interesting problems in Access where the Microsoft Jet DB and the Windows NT/Novell didn’t play nicely together, but on the whole it worked just fine. As a solo developer, it was possible to be very productive, creating a table or two, whacking a couple of forms on the front, and using the built-in capabilities of the spreadsheet or database to do validation, trigger logic on changes, and so on. It’s definitely not the approach anyone should take to creating a larger system that is going to have many engineers working on it or with a long life expectancy, but for LOB purposes, it hit the spot.
After the surge of n-tier approaches, then Web 1.0, then Web 2.0, the cloud, the JavaScript framework explosion, and so on, I’ve never been able to recapture that sense of pace and productivity that I got with Office and VBA. I’ve tried plenty of approaches, giving any tools that showed promise a go, trying opinionated approaches like Rails and Django, building my own rapid development toolchains and frameworks, but it’s just not the same.
There’s nothing like a use case
Like many people experiencing Covid-driven lockdowns, I’ve been gaining weight. The combination of being home all day with ready access to snacks (and drinks), and an injury that I can’t get physiotherapist attention for and is preventing me running, I am slowly and surely expanding.
There’s no way I want to lunge into a fad diet or fasting regime, but experience tells me that if I measure something I want to improve, that tends to motivate me. It worked ten years ago when I taught myself to run and enjoy it, and it’s worked with mindfulness. It even encourages me to keep at it with less wellness-oriented things: like cataloguing the books I read, the movies I watch, and the cocktails I make 😀.
All I want to do is record what my weight is at the same time of day every few days and see whether it is staying level, increasing or decreasing. I had a quick search on the Play Store (I’m an Android user) but I found myself in a sea of complex target-setting, calorie-counting, freemium offerings. If my team at my day-job want to buy a new information tool, I make them prototype what they are hoping to achieve in a spreadsheet first, and to buy the tool only if the outcomes are successful but the spreadsheet isn’t enough. It’s time for some of my own medicine.
The joy of spreadsheets
Spreadsheets were never really meant to be cheap and cheerful databases, but they sure play the role well. I created a Google Sheet with two columns: date and weight, and put in my first entry, formatted sensibly.
This works perfectly on a laptop, but I’m most likely to be recording adding entires on my mobile, and the experience of editing Google Sheets (or any spreadsheet, really) on a phone is really not very nice. Selecting the cell involves delicate prodding with the edge of a finger, and this brings up an editor, and a confirm button — and any scrolling at all gets really hairy.
What if I could just turn my sheet into an app?
What I really wanted was a way to quickly turn my simple database table into a mobile-first app. I dropped the Tools menu down out of curiosity, thinking that maybe a Google Form would do the job when I saw the “AppSheet” item. It seemed too good to be true, but I clicked the “Create an app” button.
To my astonishment, after clicking through a very simple onboarding flow, I found myself with an email inviting me to download and install my new app. The link in the email took me to the Play Store to install the AppSheet app on my phone. It then let me add my Weight Diary app as a shortcut on the home screen! Here are the two screens in the CRUD flow it automagically created (ignore the “History” menu item down the bottom — I added that later):
There’s a lot going on here:
- A list view with the entry from the spreadsheet neatly formatted as a card (the date column is the title and the weight column is in the detail)
- The card in the list has an overflow “kebab” menu with edit and delete actions
- The edit dialog has a date picker (I’d applied a date format to that column) and the weight has a number field with increment/decrement buttons
On top of that — when I ran the app for the first time it asked me to log in and offered OAuth options like Google and Microsoft. There’s also a ready-to-go “hamburger menu” with working actions for raising an about dialog, adding a home screen shortcut, providing feedback, and logging out.
That was my whole use case solved. I had a working app to solve my problem with absolutely no coding effort. I was curious though to see what could be done to polish it a little further.
Customising the app
In addition to the link to open my app, the intro email also contained a link to customise it. This took me to the cloud-based IDE for AppSheet.
The spreadsheet appears as a table in the data source area and the right hand hand is devoted to a running instance of the app to preview changes.
The default value for weight in a new entry was blank, so I thought I’d have a go at making it the previous entry’s weight value instead. Below is the column editor. It’s reminiscent of the Microsoft Access table editor, with all the metadata you’d expect for table driven user interfaces. You’ll also notice there is a special “virtual column” called “_RowNumber” to make it easy to reference the row.
One of the metadata fields is “initial value” and it supports a spreadsheet like expression language. I did a bit of Googling and found the following expression in (the excellent) support site, that evaluates to the value in the cell immediately above the one being added:
LOOKUP([_THISROW].[_RowNumber]-1, "Weight Diary", "_RowNumber", "Weight")The LOOKUP function takes a column value from row in a table that matches a given criterion. It takes this form: LOOKUP( find-value , in-dataset , in-column , return-column )
In the case above, the find-value is the number of the row being added less one, the dataset is the sheet/table, the column being searched is the row number, and it’s we want the value from the weight column. The special _THISROW qualifier is important, otherwise it searches the row being searched instead.
Helpful suggestions
The other pleasant surprise was the suggestions offered up in the “home” section of the IDE. One of these offered to add a new “view” to the app with a bar chart of the data. The default was to count the values, but a one click change to an average instead created an instantly useful history of weights. The view is added as a menu item in the bottom nav bar.
Summary
I wrote a series of stories about the stack I use for building tiny SaaS apps for public distribution. AppSheet doesn’t fit easily into my goals with these (particularly a requirement for scale-to-zero) because while it is amazingly productive, to get beyond ten users a fixed monthly fee becomes payable. That said, at $5/user/month for the most basic features and unlimited users — it is a pretty compelling option for building tiny SaaS applications.
This is the first time since the early 2000s I’ve recaptured the magic of being able to knock up a polished app so quickly, and I think I’m going to have to try a few more!
