This content is incomplete and may be inaccurate, feel free to look around & please check back later for updates.

If you’re like me, you can build anything. You know Linux, routing, databases & SQL, a server-side language be it PHP, Python, .NET. You know javascript, AJAX methods with CSRF, maybe a front-end framework like React or Vue. You’ve built nice looking UI’s, supported customers, built CI/CD chains. You’ve designed circuit boards, built firmware for microprocessors, created your own bios with its own filesystem. Maybe you even delved into soldering the occasional surface mount component. You’ve built products that went global, made millions, and auto-translated into the users language with grammar intact.

But when it comes to a spreadsheet you’ve probably at best managed to add a fixed header or discovered that tiny square to clone the formula down. That’s me, and I’m exploring a world that sits just before SaaS – where accountants and business analysts create bespoke tools and dashboards in Excel and PowerBI, tailored to the business, not-scalable, not multi-tenanted. Single-use structure. Essentially app developers, just within spreadsheets. This is where the vibe-coding, no-code, zapier/make automation world sits. I’m looking to understand how whole dashboards are created within a spreadsheet by tying it back to SaaS design, so that I can help translate these small single-domain tools in to scalable multi-tenant global SaaS products.

And if you’re not like me but in the spreadsheet world, hopefully this helps open up how SaaS coding works, and how it’s really just a fancy bunch of spreadsheets!

What is a spreadsheet (as I understand it currently)

This is what I know:

  • A spreadsheet is a table of unlimited* width and height. It’s a grid. It’s a database table.
  • I’m aware of sheets within sheets (a workbook?), so let’s say it’s a MySQL database, where each sheet is a table.
  • I am aware that a cell can reference another cell through formulas (is that the same as a macro?), and a calculated result can be shown. It can even reference cells on different sheets.
  • Cells can be formatted in appearance, and merged vertically and horizontally.
  • I have used VLOOKUP maybe once, but it’s apparently very powerful – however it’s been superceeded by something called PowerQuery? Which can be done mostly via the UI?

This is enough to get by to make a simple invoice or a way to group data, count results, sum totals, etc. which is where most of the knowledge ends.

So how do people make full dashboards out of it, and what exactly is a pivot table??

A brief intro to app development for Accountants

So this can hopefully help the accountants out, one way to understand app development (and the structure I use) is MVC, which stands for Model-View-Controller and can be applied to almost every App or SaaS platform:

  • Model: This is the part of code that retrieves data from a database, groups it, sorts it, does common manipulation to the data. It also stores data you provide to it. One model usually equals one database table, which contains a single category or thing such as Users, Locations, or Products. These tables link together using a common column which is usually a numeric ID – this will be explained later as I explore how to do this in spreadsheets.
  • Controller: I’m putting this second as that’s where it sits logically. A controller is what talks to the model(s), and adjusts the data, does calculations on it, it’s a temporary space – it fetches and manipulates/organises the data ready for the next step. In the web world, this is typically what is referenced by the end bit at the url, for a simple example in http://lukebeales.com/hello/world , the controller might be called “hello”, and a function within the hello controller might be called “world”. The controller is what looks after everything.
  • View: This is fed data from the controller, and it’s sole purpose is to display it. It contains some logic but the idea is for the view to not really think. Most importantly, the view should NEVER directly talk to a model. The view is what creates what the user sees, and is typically a template that has placeholders for data.

That’s backend software development in a nutshell. It can also be applied to app development or front-end development by counting the ‘view’ part as the app itself and sitting on your device, with the other areas sitting on a server somewhere else. And to clarify a couple of other things:

  • SaaS: Software as a Service, basically referring to any subscription based website. Xero is a SaaS platform. You could call Facebook a SaaS probably.
  • Multi-Tenanted: Ability to display different data depending on who logs in, often stored within a common database using common tables. Xero is multi-tenanted.
  • Scalable: When you account for 10 or 100 or 1000 Tenants, who could each have tables that contain 10 or 100000 rows. They could be in different timezones using different currencies. The tenants might have sub-tenants. The data and architecture has to account for these possibilities and beyond.

Also I think this is important as I only discovered this recently and I’ll discuss this later:

  • Database tables are always arranged vertically: Columns being the parameters/attributes/datapoints, and each individual row being a separate entry. This includes chronological data sets. I understand there’s an overarching trend in the accounting world to list parameters/attributes/datapoints as rows, with dates across the columns representing each entry. Rotate that, and you have a database table.

So please join me on this journey to find some common ground between how SaaS and Spreadsheets can be compared.