Builder's Guide for Google Sheets

Google Sheets Show & Tell
1

This Builder’s Guide is a work in progress. We have published it as a wiki so that the community’s Superheroes :woman_superhero: can add, improve, and expand on it. (Superheroes, activate!)

Why Templates?

Spreadsheet templates are incredible tools that empower people with greater understanding, confidence, and control of their financial lives. Each of us has unique workflows for managing their finances and this community has become a place to share those ideas, workflows and best practices.

Overview

We hope this Builder’s Guide will be a seed that grows into a complete reference on Tiller’s spreadsheet architecture and comprehensive set of best practices for aspiring and advanced spreadsheet builders. We will start with the basics, see what is missing, and enjoy the journey as this document grows and evolves.

Best Practices

The best, most-useful templates are easy to try and benefit from. They are portable, intuitive, and adapt to our data organization. They are built in familiar ways and can be easily updated, forked, and maintained. (We designate such templates with the “Tiller Community” badge.)

The three :magic_wand: essential steps to meeting these objectives are:

  • Honor and leverage the Tiller spreadsheet architecture
  • Use dynamic column references
  • Refer to the best practices in this document

Terminology

  • spreadsheet - an integrated collection of one or more sheets
  • sheet - a single tab or page in a spreadsheet
  • feed - a readily-updated connection between a user’s financial data and their spreadsheet(s)
  • template - one or more sheets that help solve a problem often linking to live data through formulas
  • workflow - a productivity-enhancing process or optimization that helps users efficiently solve common financial tasks
  • report - a static sheet of rendered data

Core Sheets

Tiller spreadsheets are built atop Tiller’s “core sheets," a best-practices data architecture that enables interoperability across the Tiller ecosystem.

Whether starting from the Foundation Template or not, Tiller spreadsheets leverage a predictable architecture based on four core sheets with specific sheet names and single-row headers with expected column names.

  • Transactions
  • Balance History
  • Categories
  • Accounts

Other sheets/templates in the spreadsheet that do the work of displaying budgets, reporting on cashflow, or charting Net Worth depend on the data in these four sheets. Without adherence to the core sheets standards, modular templates will not be shareable, fill via Tiller Feeds, or dependably work as designed.

Two of the core sheets, Transactions and Balance History, are filled by the Tiller Feeds service. The Categories sheet is not filled by Tiller Feeds but contains a standardized schema for category and budget data that is used for data validation in the Transactions sheet and by Foundation Template sheets like the Monthly Budget and Yearly Budget. Finally, the Accounts sheet is a reference for linked accounts and user overrides.

These sheets each contain reserved-word headers allowing templates to locate expected headers to leverage data. Customers can choose and modify which columns they want to include in their Core Sheets, but feeds, reports, tools, and templates will look for specific case-sensitive header names to find data within a sheet.

Transactions

The Transactions sheet is a running log of all transactions from accounts linked to the spreadsheet with each transaction receiving its own row. Column headers can be added, moved and deleted. Whenever a reserved-word column header is present, Tiller Feeds will fill the cell with available data for new transaction records.

  • Transactions records in the Transactions sheet are written by Tiller Feeds only when they are first appended. Tiller Feeds never modifies data in already-added rows (though AutoCat may modify existing rows).
  • The Date column is required for sorting to work properly. If the Date column is missing, new rows will be appended at the bottom of the Transactions sheet.
  • If an account is unlinked from the sheet, the already-existing transactions will remain.

Balance History

The Balance History sheet is a running log of balances for each linked account with each dated balance receiving its own row. This sheet is typically hidden (as the data can be overwhelming when not rendered with charts and formulas). Column headers can be added, moved and deleted. Whenever a reserved-word column header is present, Tiller Feeds will fill the cell with available data from new balance records.

  • Balance records in the Balance History sheet are written by Tiller Feeds only when they are first appended. Tiller Feeds never modifies data in already-added rows.
  • Balances for unlinked accounts can be added manually or via scripted workflows.
  • Tiller uses the hidden Account ID column to uniquely identify accounts. This identifier is prefixed with manual: to identify manually-added accounts.

Categories

The Categories sheet establishes conventions for category and budget data. It is not populated with data from Tiller Feeds.

  • If the Transactions sheet contains a Category column, it links to the Categories sheet for data validation.
  • The only required column for category data validation is the “Category” column.
  • Budget data is stored for each category in the Categories sheet using monthly headers— in the format of “1/1/2023”— usually starting to the right of the Hide from Reports column. There is no limit to the number of budget columns.

Accounts

The Accounts aggregates account data from the balances in the Balance History sheet. This sheet serves two purposes:

  1. In the visible area, it allows optional overrides for accounts in the Balance History sheet.
  2. In the hidden area, it renders the entire Balance History into a sorted unique list of accounts with latest balances. For performance reasons, templates requiring a list of accounts or last-balances should use this hidden data whenever possible.

Notes about the Accounts sheet:

  • The Accounts sheet contains (optional) global account overrides such as Class, Group, and Hide. Group overrides (e.g. “Credit”, “Banking, Loan”) are used to group similar accounts types in reporting & dashboards like the Balances template.
  • The Hide setting hides accounts from dashboards that report at the account level.
  • Though it is required by many common templates such as Balances, Net Worth, and the Debt Planner, Accounts is an optional sheet. Templates that reference it should use the data when the sheet is present and also recover gracefully when it is not present

Conventions

Data Conventions

  • Typical data sizes. Templates should be built to accommodate typical ranges of user data. Tiller’s templates are built for up to 200 categories and up to 100 accounts.
  • Honor overrides. Where transactions, accounts, and categories have documented overrides (e.g. through the Categories or Accounts core sheets), these settings should be leveraged and respected in automated reporting. For example, the Categories core sheet contains a “Hide From Reports” column. Templates and reports that present data organized by category should query the “Hide From Reports” setting and honor it in the report output.
  • Error messages. Avoid error messages such as #N/A, #VALUE, and #REF. Provide a contextual error message or blank cell when possible.

Style Conventions

  • Tiller’s product templates typically use the “Overpass” font with font size 10 for body text.
  • Try to show template users which cells are meant to be user-editable cells by coloring them green. We use #E2FFE2.

Recipes for Common Data Operations

Often the best how-to reference is picking apart a template that solves a similar problem. We have collected information on some of the most commonly-solved problems and best-practices below.

Dynamic Column References

If you want to share templates, this is THE MOST IMPORTANT thing.

Despite the Tiller core sheet conventions, users will rightly take advantage of the inherent flexibility of spreadsheets by adding columns, deleting columns, moving columns. If your template direct references the range Transactions!C2:C for the user’s transaction categories, more likely than not, it will point at the wrong data in the user’s sheet (even though that reference worked in your development sheet).

Fortunately, solving this riddle is not very hard, there is a very common solution, and it is implemented in most every template that is shared in this community.

We create a small table that looks like this in the “hidden area” of each template, listing the sheet and columns names we need.

image
image900×344 37.4 KB

The header-column formula is a little bit fussy but looks like this:
=iferror(REGEXEXTRACT(ADDRESS(1,MATCH(H2, indirect("'"&G2 &"'!$A$1:$1"), 0)), "[A-Z]+"))

This formula looks in the first row of the specified sheet and searches for the first instance header name in the Column column, returning the column letter if found. The Reference column concatenates the Sheet name and column letter into a string reference to the columns data using this formula:
=if(isblank(I2),iferror(1/0),"'"&G2&"'!$"&I2&"$2:$"&I2)

From here, rather than making direct references (e.g. Transactions!C2:C) to the ranges we want to query, we can use an indirect reference (e.g. INDIRECT(J2)) using the string reference to the column wherever it lives in the user’s sheet.

If you’re interested in going deeper on this, have a look at this tutorial and this sample spreadsheet.

Due to the duplicative use of the Account, Group and Hide header names, column lookups into the Accounts sheet are slightly different, starting in column G instead of A. Refer to Balances!X:Y for an example.

The “Hidden Area”

Template developers often need a staging area for intermediate calculations. A “hidden area” can be used to feed charts with data, list data validation dropdown content, and or intermediate calculations. Many Tiller templates have a “hidden area” on the right of the main template body.

VLOOKUP, XLOOKUP & Dynamic Array Ranges

VLOOKUP and the newer XLOOKUP are powerful and indispensable tools for scanning tabular data with a key and extracting a related value. For example, if you have a category name but want to know its group, you could search the Categories sheet for the name, and, once found, extract the group from the same row.

When used in the most literal way, VLOOKUPs are brittle when a user inserts new columns into their search ranges.

Have a look at this video demo of best practices with VLOOKUP() and XLOOKUP() and a tutorial on using dynamic ranges with VLOOKUP().

The tutorial includes information on dynamic array ranges which enable creation of two-dimensional data ranges with non-contiguous ranges which are great for dynamic column lookups and reduce brittleness for many functions.

ARRAYFORMULA Headers

Google Sheets’ powerful ARRAYFORMULA() allows a single cell to run calculations across an array of cells (e.g. down an entire column), allowing formulas to automatically extend into newly-created rows. Tiller recommends using the ARRAYFORMULA() function whenever possible in header rows to avoid data-integrity issues where critical formulas are deleted or not extended to new rows. Note that the ARRAYFORMULA() function is not compatible with all common functions so sometimes this best-practice is not possible.

Here are simple and more-complicated examples of how these formulas can look.

Aggregate Within a Period

There are many ways to aggregate totals within a period like SUMIF(), SUMIFS(), FILTER(), and SUMPRODUCT().

For an example with the QUERY() function, see L12 in the Category Tracker template. You’ll see that the query function brackets a date range (e.g. Col3 >= DATE """ & text($E$2, "yyyy-mm-dd") & """ AND Col3 <= DATE """ & text($G$2, "yyyy-mm-dd") & """) and then the query sums the amounts grouped by the category name (e.g. GROUP BY Col1).

Type→Group→Category Hierarchy

Many of Tiller’s most popular dashboards include organize categories into a type-group-category hierarchy for user-friendly browsing. You might recognize this hierarchy from templates like the Monthly Budget, the Savings Budget, and the Yearly Budget.

image
image744×642 36.3 KB

Check out this demo project and write-up to learn how to implement type-group-category hierarchies in Sheets.

Implementing this is not for the faint of heart… but is doable once you see what all the bits are doing.

QUERY() Function

Google Sheets’ ultra-powerful QUERY() function has a major Achilles heel: unlike typical spreadsheet functions, query fields are captured in plaintext and are therefore not updated when cells are moved or rows are added/deleted. This limitation means sheet functionality is brittle in the face of even well-intentioned and knowledgeable user edits (e.g. column insertion).

The solution to this limitation is building custom arrays as in this example. Check out this spreadsheet for a functional example of this best practice.

Tiller Community Badge

The “Tiller Community” badge elevates shared templates that:

  • Enhances the value of a Tiller subscription
  • Builds upon the core-sheet datasets
  • Integrate seamlessly Tiller spreadsheet by honoring the conventions of Tiller spreadsheets
  • Includes documentation
  • Solution can be easily removed from a Tiller spreadsheet without significant rework
  • Does not store or transmit user data in any way
  • Are built in good faith

Generally, community-built templates are first shared in the community. We encourage builders of popular templates to submit their solutions for inclusion in the Tiller Community Solutions add-on for discoverability and ease of maintenance.