Build a no-code app using the native AppSheet database

1. Before you begin

AppSheet database (ASDB) is AppSheet's first-party native database for organizing and managing the data that powers AppSheet apps. Its interface and improved performance produces a better experience for both the app creator and end user compared to other commonly used external data sources such as Google Sheets, Big Query, and Cloud SQL. AppSheet database comes as a default data option for any AppSheet application.

What you'll learn

  • How to create a new AppSheet database with example data or imported data from a Google Sheet.
  • How to customize data in the AppSheet database editor.
  • How to generate an AppSheet application from an AppSheet database.
  • How to alter the database and regenerate tables in AppSheet to account for table structure changes.

What you'll build

This codelab walks you through how to generate and customize a new AppSheet database, first from scratch with default data provided by an AppSheet database, and then from existing data in a Google Sheet. It then walks through how to generate a new AppSheet application from an AppSheet database. Finally, it shows how to alter a database table in the database editor and then regenerate the table in the AppSheet app editor.

What you'll need

  • Access to AppSheet development environment ( appsheet.com).

2. Create and customize a new AppSheet database from scratch

To get started, you create and customize a new AppSheet database from scratch using its default starter database. This gives you a chance to experiment with the database editor without breaking any of the later steps in the codelab.

  1. Navigate to the AppSheet ( appsheet.com) home page, and then click Create > Database > New database.

New database from scratch

  1. A new example database is created and the AppSheet database editor appears.

Image of a new example database in the AppSheet database editor.

  1. Customize the database by changing the name of the database from Untitled database to Tasks DB. (Double-click the default title, Untitled database, to edit the name of the database.)
  2. Change the name of the table Table 1 to Tasks. (Double click the tab to edit it).
  3. Change the first column from Title to Task.
  4. Add a new record by clicking + in the upper-left corner .

Image of how to add a new record by clicking + in the upper-left corner

  1. Double-click the Status column to see how the Enum Type and Dropdown Item type are used.

Property editor for 'Status' column. Type: 'Enum' and Item type: 'Dropdown' are used

  1. Optional: Click on the buttons after the = signs to colorize each dropdown option.

Colorizing the Dropdown options with a color picker.

  1. Optional: Experiment further with the editor by adding new options for the Status Enum (click Add option) along with new colors.
  2. Optional: Experiment with different column types such as Color, Progress, Yes/No, and Phone, and then observe their behaviors.

Contextual menu for all the supported data types in ASDB.

Feel free to experiment further with the database editor. In the next steps, you create a new database.

3. Create and customize new database by importing from a Google Sheet

These next steps show how to import data from a Google Sheet.

Create the database from a Sheet

  1. First, make a copy of this public Google Sheet and rename it as ASDB: IO Codelab.

Notice that it has two tabs, Tasks and Owners.

  1. As before, navigate to the AppSheet ( appsheet.com) home page and then click Create > Database > Import from Sheets.

Create database from sheets import

  1. In the Google Drive File Picker, select your copy of the Sheet ASDB: IO Codelab or whatever you named it.

This results in a new AppSheet database generated from the data in the Sheet.

Screenshot of the generated AppSheet database from a Sheet, in the database editor.

Add a reference to the Tasks table

Next, you add a reference to the Owners table from the Tasks table.

  1. In the Tasks table in the AppSheet database, double-click the Owner column to edit its settings.
  2. Change the type from Text to References > Reference.

selecting reference type

  1. Set Table to reference as Owners, and then click Save.

Dialog to set Type: Reference and Table to reference: Owners

  1. A warning appears about changing the column type. In this case the data in this column is compatible with the new column type, so you can click Yes to proceed.

Change column type Yes/No dialog.

  1. AppSheet database then attempts to match the corresponding rows in the Owners table with the names in the Tasks table. Since this dataset matches, the AppSheet database should populate the Owner column with the correct references to the Owners rows.

Tasks table in the ASDB editor showing the correct Owner column value.

  1. To test the reference, click on an Owner cell and update it to another owner such as Sarah.

Selecting another Owner via a popup supplied by the reference.

Once selected, the new value (Sarah) appears in the Owner cell (in the Tasks table).

After selecting a different Owner, the Owner's name, Sarah,  appears in the Owner column of the Tasks table.

Change the Label column of the referenced table

AppSheet database allows you to select a Label column for any table. This allows you to specify which column you wish to appear in a parent table. This also governs what is shown in the parent table in a linked AppSheet frontend app as well. So instead of displaying the Name column from the Owners table in the Tasks table (shown earlier), you could instead display the Email column from the Owners table.

  1. To do this, change Label column in the Owners table to Email by hovering on the right side of the Email column header and clicking on the vertical ellipsis.

Image showing the vertical ellipsis on the right side of the Column header.

This invokes the column's contextual menu.

The column's contextual menu appearing with 'Use column as label' selected.

  1. Select Use column as label on the contextual menu to set the column label to the Email column.

Email column header with the Column label marker at the top left of the header.

  1. To see that the Email column is selected as the column label referencing the Tasks table, click the Tasks table tab and notice that the Owner column now displays email addresses. You may have to refresh the browser to see the updated Owner column.

The Owner column of the Tasks table showing email addresses since the referred Owners table's Email column was set as the column label.

4. Generate an AppSheet app from an AppSheet database

Once you've finished your database customizations, you can generate an AppSheet app directly from the database. This is helpful if you want a custom frontend UI, need to add automations or security filters etc. To do this, do the following:

  1. Click on Apps on the right side of the ASDB editor.

Screenshot of the AppSheet database editor with the 'Apps' button on the right highlighted.

This invokes the contextual menu on the right to generate a new app.

Same screenshot as before, but with the dialog with the 'New AppSheet app'  button.

  1. Click New AppSheet app to generate a new app.

Screenshot of the generated AppSheet app.

As the app is generated, notice that the app has a warning:

Column "Owner" in Tasks_Schema has a reference to an unknown table or slice "Owners". Open the column definition to select a source table.

This is because the generated app only has the Tasks table, and its Owner column has a reference to an unknown table, Owners.

To remedy this, add the Owners table to the AppSheet app.

  1. In the Data page in the AppSheet editor, click +.

Data page with the '+' icon highlighted.

  1. In the Add data contextual menu, select AppSheet Database as the datasource.

Add data contextual menu.

  1. Then select the ASDB: IO Codelab database (or whatever you named your database in prior steps).

Select database menu option.

  1. In the subsequent dialog, select the default SELECT ALL checkbox, and then click Add to app.

Dialog to select Owners table to add to the AppSheet app.

As the Owners table is added to the app, the Reference error no longer appears.

Screenshot of the AppSheet app with no Reference errors.

  1. Now that the new Owners AppSheet table has been added, you can create a new View to display this data by clicking on the Views page in AppSheet and clicking on + next to PRIMARY NAVIGATION.

The Views page, with the '+' icon highlighted.

  1. In the Add a new view dialog, click Create a new view

Add a new View dialog.

  1. As the new view is created, set the following options:

View name: Owners For this data: Owners View type: deck

View editing page for Owners view.

Congratulations, your app is now complete!

  1. To test the reference between Tasks and Owners, click on either the Owners view or the Tasks view in the mobile preview, and then select a record to see the related (referenced) records.

Screenshot of the AppSheet app with the mobile preview on the right highlighted.

For example, you can click on the Owners view, and then click on a record to drill down and see its related Task records.

Image showing selecting an Owner and drilling down to see a related Task.

5. Alter the database and regenerate the Tasks table

Now that you've created an Appsheet database and generated a new app from it, try altering a table in the database. Change a column data type and observe what happens in the linked AppSheet app.

  1. In the Tasks table in the database, change the Due Date column's type to Date and time > Date.

Altering the Task table's Due Date column's type to Date in the ASDB editor.

Notice as you return to the AppSheet app, it detects that the database has been updated with a warning:

App Schema for table 'Tasks' is out of sync with the schema in AppSheet database. Please regenerate the table structure

AppSheet's editor detecting a schema change in the database and providing a warning.

  1. To regenerate, click on the d1a956498c05d75f.png regenerate icon in the upper-right corner.
  2. This triggers a warning dialog:

Regeneration warning dialog.

  1. Click Regenerate to continue. This updates the Due Date column's type in the app to be of Date type, and enables the date picker input element in the Edit Form for Task records.

AppSheet app edit form showing the DatePicker input element.

6. Congratulations

You completed the AppSheet database (ASDB) codelab!

Further reading