G Suite Business customers can preview App Maker. Ask your domain admin to apply for early access.

Import and export data

As an App Maker developer, you can import data into models from Google Sheets. You can also export data from models to Sheets. When you import and export, you have several options:

  • Import—you can import data into an individual model, or you can import data into all models, including any relations.
  • Export—you can export data from one or more models, or you can export all data from the app, including any relations.

You can import and export to and from Google Drive Tables and Google Cloud SQL models.

How you can import data

You import data to get data into your app in bulk. For example, you can move useful historical data or reference data from a legacy data repository into an App Maker app. You can import data from a single sheet in a Sheet into a model in a specific deployment (the Preview deployment or a different deployment).

You can import all data in a Sheet into a model, adding the imported records to any records that are already present. The import process preserves any relations that exist. To divide the import process into smaller batches, you can divide the source sheet into multiple sheets, each with part of the data.

Access requirements

To import data, you must have:

  • Access to the App Maker app—You must either be the author of the app or it must be shared with you (with Can edit access).
  • Access to the deployment—You must have published the deployment.
  • Access to the Sheet—You must either own the Sheet, or it must be shared with you (with Can view, Can comment, or Can edit access).

Data requirements and validation

Sheet data that you import into a model must conform to specific characteristics (see details below). Prior to an import, you can manually verify that the Sheet conforms. Alternatively, you can try importing a Sheet, to see whether App Maker finds any problems during validation. Correct the errors it finds, and then try again. You need to fix the errors in either case.

A Sheet must conform to these characteristics:

Amount of data

App Maker can import data in batch sizes that are constrained by the first of these to occur:

To import more data, you can:

  • Import multiple batches of data and ensure that the batches don’t reach these limits. For example, limit batch sizes to 500,000 cells.
  • Use server scripts and import the data from a different source; for example, from a CSV file or a database. The 30-minute time limit applies.
  • Use built-in import capabilities (for Google Cloud SQL).

Columns

  • Numbers and names of columns—App Maker expects one column of data for each field in the model. The first row of each column must contain the column name, in matching case.
  • Keys—If the record includes keys, they must be in the first column named _key. If a key is present, App Maker stores it with the record. For records with relations, App Maker uses the key to associate with the appropriate records in other models.
  • Extra columns that are not in the model but that contain data—There must be no extra columns that contain data. Blank columns are fine.
  • Missing columns—There can be missing columns, but we don’t recommend that. To get data into specific fields in specific records in a model later would require server scripts.
  • Order of columns—The order of the columns in the Sheet doesn’t matter.
  • Required fields—For a column that corresponds to a required field in the model, every cell must contain data, irrespective of whether a default value is defined. Default values aren’t used during import of required fields, because the empty cells cause validation to fail. Default values are used during import of fields that aren’t required.

Rows

App Maker imports all rows, from the second row of the sheet until the last row that contains data.

Empty rows are imported as empty records, or they cause the import to fail validation if any fields in the model are required.

Data formats for simple fields

  • Strings can have any values.
  • Numbers must contain only digits, or digits and a decimal point (.). This is possible using a number format that only contains a decimal point (for example, 0.00), the Automatic format, or the Plain text format.
  • Booleans must be either TRUE or FALSE (Boolean values that result from typing true or false in any case), or the string true or false in any case with the Plain text format.
  • Dates must be dates in the date format yyyy-mm-dd or Plain text strings with the format yyyy-mm-dd.
  • Cells for any of these data types can be empty. An empty cell is interpreted as null for String, Number, and Date fields, and as false for Boolean fields.

Data formats for list fields

Individual items in lists for list fields use the same data formats as simple fields. Cells for list fields can contain:

  • Empty array (empty list)—This is interpreted as null for List<String>, List<Number>, and List<Date> fields, and as false for List<Boolean> fields.
  • A single value—This is interpreted as the first element in a JavaScript array of length 1; for example, email[0].
  • More than one value (a comma-separated list)—This is interpreted as multiple elements in a JavaScript array; for example, email[0], email[1], and email[2].
  • More than one value, including missing values—This is interpreted as multiple elements in a JavaScript array, with missing values interpreted as null for List<String>, List<Number>, and List<Date> fields, and as false for List<Boolean> fields.

    Indicate missing values in a list field with single initial and trailing commas, and with double internal commas. Importing this string from a cell for the list field Animals:

    ,horse,,dog,cat,

    Produces the same result as:

    Animals[0] = null;
    Animals[1] = horse;
    Animals[2] = null;
    Animals[3] = dog;
    Animals[4] = cat;
    Animals[5] = null;

Commas in string list fields

Commas are the separator for list fields, but you might want to include a comma or commas in an item in a list. To indicate that commas should be interpreted as commas in a string list item (not as delimiters), surround the item in double quotation marks; for example:

conference days,"Monday,Tuesday,Wednesday"

To include double quotation marks in an item surrounded by double quotation marks, duplicate the quotation marks inside the item; for example:

horse,"Super,""luxurious"" truck"

Import your data from a Sheet

  1. (Optional) Manually verify that the data in the sheet you plan to import conforms to the data requirements.
  2. Open App Maker.
  3. Open the app for which you want to import data into a deployment.
  4. In the left sidebar, click any model.
  5. In the top-left corner, click Import Data from Sheet.
  6. Select the Published deployment and one of the following options:
    • All models and relations data—to import data exported from another App Maker app. You can also check the box to Delete existing data from the app. If you don't delete data, App Maker attempts to merge the imported data with your existing data.
    • Single model data—to import data from a Sheet into one model. Choose the model from the dropdown list.
  7. Click Select, select the Sheet from which you want to import data, and click Select.
  8. Select the Sheet from which to import data.
  9. Click Import.

    App Maker validates the data with respect to the data requirements. If the validation is successful, App Maker imports the data. While the validation and import operations are in progress, you can navigate to other UI pages and do other things, and then return to the Import data page. While on other pages, import status messages appear at the bottom of the App Maker UI.

  10. If App Maker reports errors, correct the errors in the Sheet and click Import again.

  11. After App Maker reports that the import is successful, click Done.

What you can expect as a result

App Maker imports all of the data in the selected sheet. (Finer control is possible with server scripts.)

App Maker creates a record in the model for each Sheet row it imports. If cells in a row are empty, App Maker uses default values from the model when they’re available. App Maker doesn’t use default values for required values, because missing required values cause validation to fail.

If the imported data includes relations, records with _key values are processed as follows:

  • If a record with that key already exists, the record is updated.
  • If a record with that key doesn't already exist, then a new record is created with that key.
  • If an imported record doesn't have a key, App Maker creates a new record and generates a key.
  • For SQL models with a single primary key (such as the column named _key(id) in the sheet below), the _key column has a higher priority. If the _key value is empty, the _key(id) value is used as a primary key. For example:

    App Maker processes key values as follows:

    • google.com—doesn't have a _key value, so App Maker uses the primary key.
    • gmail.com—has a _key value. It overrides the primary key.
    • youtube.com—App Maker uses its _key value.

During an import operation, users who are already using the app might not see the imported data. This depends on how the app is written and on what users do. Users will see the data if they reload the browser page in which the app is running.

Multiple and incremental data imports

Each data import adds all of the imported data to a model. This is an append operation that allows duplicate records. If you import the same data twice, you get two copies of each record. If you care about record uniqueness, manage that in the source Sheets or write server scripts to find duplicate records.

To add data incrementally, for example, only adding new data for a new month, then you must create a separate sheet for the new data.

How you can export data

Export data for use in other apps, for example, for reporting or to back up your app's data. You can export data from individual models, or you can export all data from an app, including any relations.

Access requirements

To export data you must have:

  • Access to the App Maker app—You must either be the author of the app or it must be shared with you (with Can edit or Can view access).
  • Access to the deployment—You must have published the deployment.
  • Access to the Sheet—You own the Sheet, because App Maker creates it in your Google Drive.

Amount of data

App Maker can export data in batch sizes that are constrained by the first of these to occur:

To export more data, you have these options:

  • User server scripts and export the data to a different destination, for example to a CSV file or a database. The 30-minute time limit applies.
  • For Google Cloud SQL, use built-in export capabilities.

Export data to a Sheet

  1. Open App Maker.
  2. Open the app for which you want to export data from a deployment into a Sheet.
  3. Click Settings settings chevron_right Deployments.
  4. In the table entry for the deployment from which you want to export data, click Export data.
  5. Choose one of the following options:

    • All models and relations data—to export all data to a Sheet.
    • Selected models data—to export data from one or more models.
    • Click Export.

What you can expect as a result

App Maker exports all of the data in the selected models in the selected deployment. Finer control is possible with server scripts.

No validation occurs prior to exporting data. Model validation criteria apply to data entry in the app’s UI and to data import.

App Maker creates a new Sheet with these characteristics:

  • A sheet is present for each exported model.
  • App Maker exports all records in a model.
  • In each sheet, a column is present for each exported field, with the field name in the first row.
  • If relations are present in a model, the first column is named _key. It includes key values that are used to associate records in different models.
  • Subsequent columns are in the order of the fields in the model.
  • For models with many-to-one relations, the last columns include key values for associated records in other models, as shown below:

  • For exports with many-to-many relations, those associations are listed in a separate sheet. This sheet includes local key pairs for all existing associations. Records that have no associations aren't included in this sheet. In the following example, Google has associations with several services. The service called applications has associations with two companies:

  • For Google Drive Tables, rows in the sheet are in the same order as records in the model, when the model isn’t sorted.

  • For Google Cloud SQL, if there’s a primary key, then the rows in the sheet are sorted by the primary key. If there’s no primary key, then the rows in the sheet are in the same order as records in the model.

Simultaneous import and export operations

App Maker can perform these import and export operations simultaneously:

  • Different deployments—Simultaneous imports, exports, or imports and exports are possible for any models in different deployments.
  • Different models in the same deployment—Simultaneous imports, exports, or imports and exports are possible for different models in the same deployment.