Just Enough Google Sheets To build a spreadsheet app

Saying that you can’t cook is crazy.

So is saying that you can’t design an incredibly useful and sexy spreadsheet app.

In the kitchen you can start by making a beautiful, delicious grilled cheese.

In a Google Sheet, you can similarly start with the basics and work your way up to mastery.

Cooking = Spreadsheets

Once you know a few basic functions, you’re on your way to a fantastic grilled cheese.

Layer in the power of API connectors like Blockspring or Zapier to pull in data from your favorites tools (Google Analytics, Twitter, etc), and you’re off to the races.

But just like good cooking starts with careful prep, spreadsheet automation is all about design.

If you follow a few simple layout and formula usage guidelines, your Sheets app won’t end up like a sloppy mess – it’ll be a productive, efficient machine to power your work.

Guidelines for solid spreadsheet design

One tab, one purpose

Each tab should serve a specific purpose (one list, one Block, one Zap, or one piece of an analysis). You’ll thank yourself later, when it’s clear and easy for you to dig through an old spreadsheet you built.

If you’re storing data in a tab, do it in table format (as if it were a database table of an app), with one column across the top and data flowing down. Never stack two tables on top of each other – it’ll make it impossible to cleanly link to that spreadsheet from elsewhere.

Query, query, query

There are a zillion ways to pass data around a spreadsheet – vlookup, filter, even index-match – but using the query function makes it stupid simple for your colleagues and collaborators to understand how data flows through your app.

Queries are easily readable and reusable, once you know how to use them – the Google Sheets query is a bit different than traditional SQL queries, but it’s composed of two basic elements:

google sheets query function
Source: Google
  • A range of sheet values to query
  • A query, which selects columns to display based on conditions: “select A, B where B = 1 and A <> ”” will query all values of A and B where B is 1 and A is not blank. Google provides a deeper explanation, or feel free to drop follow-up questions in the comments.

Understanding google sheets query functions also will have you on your way to understanding a building block of any app: query languages, which read data from tables. The most commonly used are various versions of SQL (mySQL, postgreSQL), which underpin most apps on the web.

Update: if you’re curious to learn the Query function, grab the Sheets formula cheat sheet.

Create validation dropdowns for key fields

To make your sheet fail-safe (so that you can share it with other people on your team), you can create dropdown validations for any critical fields. We recommend creating a ‘Validations’ tab, where all of your dropdown settings live (see the Blockspring + Google Analytics template for an example).

google sheets validation dropdown tutorial

Setting up a validation is easy by selecting Data -> Validations, and configuring:

  • A cell for the validation to apply to
  • A range of possible values
  • Appearance = ‘Display in-button cell to show list’

Hide tabs that you won’t actively be using

If you’re sharing the sheet with your team, don’t make them thumb through tabs that they won’t need to touch. Tabs like validations, settings, or form responses are prime candidates for being hidden – let users focus on the analysis and task at hand, rather than what’s going on behind the scenes.

Make key tabs shareable

Your spreadsheet app truly has use when someone can pick it up, having never seen it, and immediately get the picture.

If you’re displaying a dashboard or summary of your sheet, structure it in a way that can easily be exported to a PDF / CSV or other static file format. Remember – spreadsheets aren’t built for just you, they’re built to benefit your entire team.

acquisition by search engine google analytics blockspring

This is why the league adds an action dashboard to any template we build.

A few good google sheets functions

If you’re just diving in with spreadsheets and looking for a few key functions, 99% of spreadsheet apps can be built with just these 6 types of functions (grab the cheat sheet below to learn them):

1) IF and its cousins (COUNTIF and COUNTIFS, SUMIF and SUMIFS)
The most basic conditional statement, which underpins any app. Do this if that is true, and do this if that is false – if statements can be combined infinitely with other functions to get a programming job done.

2) Arithmetic functions (AVERAGE, SUM)
‘Nuff said.

To keep your spreadsheets clean, wrap functions that may fail in iferror() to make sure they fail in peace, and indicate clearly to other users that they failed.

When you need to pull one piece of data from another sheet, based on a cell’s value. There are more efficient ways to lookup data (index-match, arrayformula, or query) but vlookup() is a great place to start if you’re getting your feet wet.

Allows you to pull multiple values from a table of data, based on conditional values in that table. For example, if you have a table of orders and want to pull only orders with a value over $20 (and you aren’t ready to use the query function), filter() is your friendliest option.

Help cut out the riff raff of duplicate values when performing an analysis. For example, in the spreadsheet app that uses the Blockspring + Mandrill to distribute spreadsheet templates, we use unique() to filter out any duplicate template requests.

Now get out there and build spreadsheet apps!

If you’re looking to level up, grab a copy below of the sheet we use to teach new CIFL members Google Sheets formulas.