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).

Note: In addition to offering paid licenses, AppSheet and the AppSheet database are available for free on a trial basis. See AppSheet's pricing guide.

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.

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

3. 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.)
4. Change the name of the table Table 1 to Tasks. (Double click the tab to edit it).
5. Change the first column from Title to Task.
6. Add a new record by clicking + in the upper-left corner .

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

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

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

Note: Some types require a linked AppSheet app to be functional. For example, Metadata... column types only come from app updates.

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.

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

3. 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.

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.

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

4. 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.

5. 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.

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

Once selected, the new value (Sarah) appears in the Owner cell (in 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.

This invokes the column's contextual menu.

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

3. 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.

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.

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

2. Click New AppSheet app to generate a new 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.

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

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

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

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

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

Note: There is a temporary warning about the Email column possibly containing private information. This can be ignored. Table 'Owners' may contain sensitive data in column(s): Email

7. 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.

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

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

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

Congratulations, your app is now complete!

10. 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.

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

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.

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

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

4. 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.

6. Congratulations

You completed the AppSheet database (ASDB) codelab!

Further reading

• AppSheet database official documentation site