Create an app: A tutorial

Last updated: May 13, 2022

A tutorial on creating a new app.

Create an app: A Tutorial

If you're familiar with our guides on writing SQL queries and using variables, you're mostly ready to go! For the rest of this tutorial, we'll assume you're somewhat familiar with SQL, and we'll link to various docs along the way when needed.

This is a full tutorial on creating an app. We also have a quickstart on creating an app if you'd like to get a quick overview instead.

Check back in a few days for a video version of the below.

What Do We Mean By App?

In this document, we mean a very specific type of app: a parametrised SQL query, one that can take values from users, and is runnable on demand to produce data.

For a more general discussion, see What Is An App?

Is There No Shorter Way? This is a Long Doc

As mentioned above, if you just want a quick overview, you should check out the quickstart on creating an app.

And indeed this doc doesn't need to be read straight through. See Creating an app from a template below for a shortcut.

For Now, We Need Some Data

For a longer version of this section, see our doc on using datasets in apps.

For the type of apps we'll mostly be making for now, the starting point is data. A good place to find suitable data is our dataset catalog. Let's assume we like trees, so we put that in the search bar:

Datasets catalog with the term 'trees' in the search bar, and two resulting datasets. One is NYC Street Trees: Census 2015 and the other is the same but from 1995.

Clicking the link to the first brings us to the page for the dataset NYC Street Trees: Census 2015 . After reading the docs, seeing what columns are available to us and looking at the data preview, we'll assume we're happy that this is a good source of data for an interesting app. It may be useful to keep this page open as a reference. If a dataset already has apps using it, those can be great inspiration for an app.

To use this dataset, we need its ID, which is bigquery-public-data/nyc_trees_census_2015. You can get this ID from the URL, or copying the ID from underneath the description. Or you can start with the "Create App" tab. Again, for more details on this, see our doc on using datasets in apps.

Write a Query

Skip this section if you already have your query in mind. For more general details on writing SQL, see our doc on Writing SQL queries.

This is probably the most familiar part, and by far the hardest. In the workplace, you may be trying to answer an interesting cohort analysis question from the marketing team, or maybe the finance team needs the result of some complex joins for their financial modelling. Hopefully you just have a burning question in mind: which NYC neighborhoods have the healthiest trees?

Of course before we publish our app, we can poke around in the Test tab. In this tab, we can grab the line separating the code from the table and see more of one than the other if needed. First running the following shows we have 188 neighborhoods:

Loading...

To get a sense of defining tree health, we might run:

Loading...

Which gives something like:

status health count
Alive Good 528,850
Alive Fair 96,504
Alive Poor 26,818
Alive 1
Dead 13,961
Stump 17,654

Ignoring the single alive tree without a health status, we have five states. This should be enough now to write the general shape of our query, before worrying about parameters:

Loading...

Parametrise / Add Variables

Note: this is a long section. If you're already familiar with our using variables doc, you can skip ahead.

This is great, now we can see the health in percentage terms of trees within 5km of MoMA, split by neighborhood. We could publish this now, assuming we want to share a static app of just data. This is effectively a way of storing views on a dataset, except you can document them and give them a homepage.

But maybe our users don't quite understand why we'd want to filter to trees within 5km of MoMA. Instead of choosing the point of interest and the distance, what if our users could decide? Let's parametrise these instead. We'll go through a few days to do it.

First, in the Define tab, hit "Add Variable" on the right. You can just use the default of type Text and adjust it later, or use the extra menu (arrow) to select number specifically. See the picture:

Using the extra menu for 'Add Variable', selecting the Number type in the 'Add by Type' submenu

First to set up the longitude variable: we know a lot about this. We can bound it, give it a default (probably somewhere in the middle of NYC), an example (let's stick with MoMA) a description, and maybe some documentation. We can even bound this, because we know longitude is from -180º to 180º. Finally, we need to give it a name. Here I used longitude_poi for "point of interest", but simplified the label to "Longitude of Interest":

Defining the longitude variable with the values discussed above. Description is 'Longitude to potentially restrict to being close to.' and the Documentation links to the wiki page of Middle Village in Queens.

Now, replace the hardcoded value of -73.9776 with {{ longitude_poi }} - you should get the name autocompleting as you type it. Similarly, we can do the same for the latitude. However, try something slightly different: highlight the value 40.7615 and right-click, and click "Create Variable from Value":

In the coding box, showing a submenu after right-clicking the highlighted latitude value, with the option 'Create Variable from Value' selected.

I'll let you work out the details for that one - just want to note that if you edit the name in the variable box, before navigating away you'll have a chance to update the old name to the new one in the body too, so you don't have to edit it twice:

In variable editor, we're hovering over the button that pops up after changing a variable name that offers the opportunity to update the varaibles in the body too.

Then edit the resulting column name to e.g. distance_from_poi instead of MoMa.

For the distance, we can do it a third way: replace the value 5000 with {{ max_distance }} in the body. It'll show up gray because no such variable exists with this name. Right-click and select "Add variable to app" (or press ⌘-k ⌘-b with the cursor on top):

In the body, we've right-clicked the variable `max_distance`. In the menu that has openend, we're hovering the option 'Add variable to app'.

I also added a borough Category variable, which can be one of the five boroughs of NYC, or the value "All", with the WHERE clause being ({{ borough }} = 'All' or {{ borough }} = boroname).

Add Metadata / Documentation

We're nearly there. We want to give interested users more context on our app, both for discoverability (say, in the app catalog) and for understanding how best to use our app once they find it.

Your app needs a good title, this is the most prominent part of the app in the catalog.

Your app also needs an ID. This identifies your app: it gives your app its URL, and it's how your app will be called through an API. In many cases, the autogenerated ID from the title is suitable, but you may find it's overly long.

An app can't be published without a description. It can be short, but very useful to quickly get context on the purpose of an app before clicking into it.

Optionally, you can add long-form Documentation. Unlike the plaintext description, this is markdown, so you can add codeblocks, links, tables etc. If you're not familiar with markdown, see our guide to writing markdown, or type / to bring up formatting options.

Publish!

Everything's done, you're ready to publish. Hit the button and enjoy seeing your app in the catalog!

Here's my version if you're interested.

Editing and Deletion

Deletion is possible from the app's homepage or from the catalog. From the app's homepage, click the burger menu next to the share button, then select "Delete App" from the menu:

Burger menu clicked (aria label: 'Edit or Delete App'), with 'Delete App' selected within.

From the catalog, this menu is underneath the expansion button.

True editing is coming soon! For now, see below for using an app as a template: you can recreate an app this way (plus edits), then delete the old one and publish the new one. Assuming you'll keep the same ID, you can't publish the new one till the old one is gone.

Creating an App from a Template

All the above might seem like a lot. Or maybe someone else has a great app already, and you want to build on from it. There's a solution: use an existing app as a template. You'll see this button in the top-left section on an app's homepage, beside the Share button. Clicking it will open up a dialog, where you will choose the name for you new app. It defaults to the name of the app you're building from, adding "Copy of" at the start. Don't worry, you can change this later.

The dialog box that opens after clicking 'Use as Template'. It's asking for the new app's name.

This will bring you to the new apps page, with the same body and variables as the app you were previously on. The new title will be what you selected in the dialog, and the new ID will initially be generated from this new title.

You don't get the old description or documentation. If you want to build off those too, simply copy them over from the previous app.