Prerequisites

  • An AppSheet plan that supports database connections.
  • Access to a Google Cloud Platform account.

Nomenclature

The following list defines a few terms with a focus on how they are used in relation to AppSheet.

  • Database Instance: The database instance is the structure that manages a collection of database files. An instance can hold several databases. In the tutorial below, you will generate a public IP address to connect with the database instance.
  • Database: A database is a collection of data. A database generally holds several tables. In AppSheet, each database will appear as an individual data source.
  • Schema: In this context, schema and database are referring to the same thing. It is a collection of data made up of several tables. Schema is the term used in MySQL Workbench. Database is the term used in Google Cloud and AppSheet.
  • Table: A table holds the actual data. A table generally has several columns, each with a specific type of data (e.g. Name, email address, phone number, etc...). Each row contains a unique record. When viewing a form in AppSheet, each field in the form corresponds to a column in the table and saving the form creates a new row.

In summary, a database instance can have multiple databases and a database can have multiple tables.

Create a Database Instance

1. Open your Google Cloud account and click “SQL” in the left hand menu.

2. Click “Create Instance.”

3. Choose your database engine. This tutorial uses MySQL.

Fill in the instance info
4. Choose an Instance ID. This is how your database will be identified in the Google Cloud account.

5. Select a root password.

6. Choose the region. AppSheet servers are located in us-west and europe-west. For best performance, select between these two regions based on where the majority of your users are located.

7. Click the link to expand configuration options.

8. Under “Connectivity” ensure “Public IP” is checked. By checking this box, Google Cloud will create an IP address that you can use to connect your AppSheet account to the database instance.

9. Click “Add network”. 

10. Enter the IP addresses listed here. This allows the AppSheet server to connect directly to your database. Google Cloud requires the IP addresses in CIDR notation. To write the IP address in CIDR notation, add “/32” to the end of each IP address. For example, “20.189.130.98” becomes “20.189.130.98/32”.

11. Select the machine type and storage. Select a machine type with enough memory to hold your largest table.

12. Update additional settings as needed. Generally, the default values for the remaining settings are correct.

13. Click to Create the database instance. This will take a few minutes to complete.

Create a user account for the database instance

1. After the database instance is created, click on the instance id to open the details page.

2. Click “Users” and “Create user account.”

3. Create a user name and password for the account that will be used to connect with AppSheet. Record the username and password. After creating the account, you can change the password, but you cannot retrieve the password.

Create a Database - Method 1: using Google Cloud Console

1. Click "Databases" and "Create database."

2. Enter a database name and click "Create."

Create a Database - Method 2: using Database Visualization Tool

A data visualization tool is a common way to interact with the database. This tutorial uses MySQL Workbench to access and modify the database.

1. To connect to the database with MySQL Workbench, you must add your workstation’s IP address to the firewall allow list. You can find this by searching “What’s my IP address” in your browser.

2. Allow your IP address in Google Cloud by following the same steps used to allow the AppSheet IP addresses.

Open MySQL Workbench and create a new MySQL connection.
3. The “hostname” is the public IP address for your database.
4. The “username” and “password” are from the user account you created above.
5. Click “Test the Connection” then click “Ok.”
6. Create a new schema. Note: The term "schema" in MySQL Workbench and the term "database" in Google Cloud (see Method 1 above) refer to the same thing.

Connect AppSheet to the Database

1. Open AppSheet and navigate to My account > Sources.

2. Click “+ Data Source.”

3. Click “Cloud Database.”

Fill in the database connection information.
4. “Server” is the “Public IP address” for your database instance.
5. “Database” is what you created by either Method 1 or Method 2 above.
6. “Username” and “Password” are from the user account you created for the database instance in Google Cloud.

7. Test the connection. If the test is successful, the “Authorize Access” button will become clickable.

Connect apps to the database

The database and schema are now connected to your AppSheet account. From within an AppSheet app, the process to add tables from a database is the same as adding tables from a spreadsheet. Go to Data > Tables and click “+ Add New Table.” Select the database schema from the list. Then select the required table.

Note: the database may appear in your AppSheet account as "database-1." You can change the name for your database in AppSheet as follows: 

1. Navigate to My account > Sources
2. Click on the database you'd like to update
3. This opens a box where you can modify the name. Enter a new name (e.g. AppSheet_Demo) and click "Cloud Database"

4. Fill in the same connection information and click "Authorize Access." The database will now appear with the new name in AppSheet.

Related article: Using Data from MySQL

Did this answer your question?