Import and export data

As an App Maker developer, you can import data from Google Sheets to Cloud SQL and export data from Cloud SQL to Google Sheets.

Drive Tables models are deprecated and you can't import data to them any more, but you can still export data from them.

Import data

You can import data in one of the following ways:

• Import data from a single selected sheet into a selected model in a selected deployment.
• Import all data from a spreadsheet into all models in a selected deployment, preserving any relations.

App Maker imports all data from the selected sheet or the selected spreadsheet, depending on your import settings. You can add data to a model or update existing data during an import, but you can't add and update data at the same time. If you need finer control over what data you import, use server scripts.

Caution: If the source sheet or spreadsheet contains sensitive or confidential data, such as personally identifiable information, the deployment of the app will also contain that information. Before and after you import data, ensure that the app is configured for security and privacy.

Import requirements

Before importing data, review the following requirements for app access and data format. You can either manually verify the data before importing it, or you can try importing the data. If App Maker finds problems during data validation, you need to resolve them and retry.

Requirements for importing data
Access	Access to the App Maker app—You must be the author of the app or it must be shared with you (with Can edit access). Access to the deployment—Your app must be published to a deployment. Access to the sheet—You must own the spreadsheet or it must be shared with you (with any access level).
Amount of data	Data is limited by the first of the following conditions that it meets: The source data in Google Sheets can't exceed 5,000,000 cells. The import operation must be less than 30 minutes. To import more data, you can: Import data in smaller batches. For example, create batches of data that contain only 500,000 cells. Use server scripts and import the data from a different source, such as a CSV file or a database. The 30-minute time limit still applies. Use the built-in import capabilities of 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. Column names should have the same capitilization. Keys—If the records include keys, they must be in the first column and the column must be named _key(primary-key-id). App Maker stores the key with the record. For records with relations, App Maker uses the key to associate the record with corresponding records in other models. For more information about relations and keys, see Represent relations in Google Sheets. Extra columns that are not in the model but that contain data—The sheet can't have extra columns that contain data. Blank columns are OK. Missing columns—We don’t recommend having missing columns. Adding data later can be challenging and you must use server scripts. Order of columns—Except for the key column, the order of the columns doesn’t matter. Required fields—If a column corresponds to a required field in the model, every cell must contain data, even if a default value is defined. Default values aren’t applied when you import required fields, and the empty cells cause the data validation to fail. Default values are applied when you import non-required fields.
Rows	App Maker imports data from the sheet from the second row through the last row that contains data. Empty rows are imported as empty records. Note: If there are required fields in the model but the sheet contains empty rows, data validation fails.
Data formats	Cells for any of the following data types can be empty. An empty cell is interpreted as null for String, Number, and Date fields, and as false for boolean fields. Note: If a field is required, the cell cannot be empty or else data validation fails. Strings can contain any values. Numbers must contain only digits, with or without a decimal point (.). You can use a number format that contains a decimal point, the Automatic format, or the Plain text format. Booleans must be true or false, and are not case-sensitive. Date, time, and time zone must comply with ISO 8601, such as: 2017-09-01T10:42:42.123-03:00 2017-09-01T10:42:42.123-0300 2017-09-01T10:42:42-03:00 2017-09-01T10:42:42Z 2017-09-01T10:42:42 2017-09-01 If time data is imported without specifying a time zone, App Maker uses the app's time zone setting.
Relations	For details, see Represent relations in Google Sheets.

How data is imported

App Maker creates a record in the model for each imported row or updates an existing record if you specified that for the import.

Records with _key or _key(primary-key-id) values are processed as follows:

• If a record with the key exists and update existing data is selected, the record is updated.
• If a record with the key doesn't exist and update existing data is selected, the record isn't added.
• If a record with the key doesn't exist and update existing data isn't selected, then a record is created with that key.

• For Cloud SQL models with a single primary key (such as the column named _key(id) in the following example), 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 and a primary key. The _key value overrides the primary key.
  • youtube.com—Has a _key value, so App Maker uses that.

During an import operation, users who are already using the app might not see the imported data. The visibility of the imported data depends on how the app is written and what users do. Users will see the data when they reload the browser page where the app is running.

Note: If users edit data in the sheet while it's imported, the result of the import isn’t well-defined. The imported data might include all changes made in the sheet during the import, some of the changes, or none of the changes.

Import data from Google Sheets

1. (Optional) Verify that the data in the sheet meets the data requirements.
2. In App Maker, open the app that you want to import data to.
3. In the navigation pane, click any model.
4. In the top-left corner, click Import Data from Sheet.
5. In the Published deployment drop-down list, select the deployment to import data to.
6. Choose an import type:
   • To import all data from a spreadsheet into all models, select All models and relations data.
   • To import data from a single sheet into one model, select Single model data.
7. If you selected Single model data, click the Model drop-down list and select the model to import data to.
8. Click Select, select the spreadsheet you want to import data from, and click Select again.
9. If you selected Single model data, click the Sheet drop-down list and select the sheet that has the data to import.

10. Choose options to update or add data:

    1. To update existing model data with the imported data, click Advanced and select Data already exists? update existing data.

       • The records must have key values, either _key or _key(primary-key-id). App Maker uses the key values to correlate the imported data with the existing data. Existing data is updated.
       • New data isn't added when Data already exists? update existing data is selected.

    2. To add data to a model, don't select Data already exists? update existing data. If you selected All models and relations data, you can delete all the existing data before adding new data. Click Advanced and select Delete all existing data before import.

       • If the new data don't have key values, new records are added and key values are created for them. If you import the same data again without deleting existing data, the records are added again with new keys. Your model data will contain duplicate records with different keys.
       • If the imported data has keys values, the key values can't match the values of existing records unless you delete the existing data. The import operation will fail because two records can't have the same key.

11. Click Import.

    App Maker validates the source data against the data requirements. If the validation is successful, App Maker imports the data.

    While the validation and import operations are in progress, you can work in other pages in the App Maker editor. Import status messages appear at the bottom of the App Maker UI.

12. If App Maker reports errors, correct the errors in the sheet and click Import.

13. When App Maker reports that the import is successful, click Done.

Export data

You can export data from individual models or all data from an app including any relations. App Maker exports all the data in the selected models in the selected deployment. If you need finer control over what data you export, use server scripts.

Caution: If the model contains sensitive or confidential data, such as personally identifiable information, then the sheet also contains that information. Before and after you export data, ensure that the sheet is managed for security and privacy. Exporting data isn’t intended for data backup.

App Maker doesn't validate data on export. Model validation criteria apply only to data entry in the app’s UI and to data import.

Export requirements

Before exporting data, review the following requirements for app access and data format.

Requirements for exporting data
Access	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—Your app must be published to a deployment. Access to the Sheet—You will own the spreadsheet, because App Maker creates it in your Google Drive.
Amount of data	Data is limited by the first of the following conditions that it meets: The source data doesn't exceed 5,000,000 cells. The export operation is less than 30 minutes. To export more data, you can: Use server scripts and export the data to a different destination, for example to a CSV file or a database. The 30-minute time limit still applies. Use the built-in export capabilities of Cloud SQL.

How data is exported

App Maker creates a spreadsheet in Sheets with the following characteristics:

• A sheet is created for each exported model and contains all records in the model.
• Each exported field is a column with the field name in the first row. Columns are in the order of the fields in the model.
• If you export all models and relations, the first column is named _key(primary-key-id). It includes key values that are used to associate records in different models. Relations information is listed in the model sheet or a separate sheet, depending on the type of model. For details, see Represent relations in Google Sheets.
• If the model has a primary key, the rows in the sheet are sorted by the primary key. If the model doesn't have a primary key, the rows are in the same order as records in the model.

• Data that includes date or time information is handled as follows:

  • TIMESTAMP fields—App Maker exports date and time in ISO 8601 format with a time zone designator.
  • DATETIME fields—App Maker exports date and time in ISO 8601 format with no time zone designator. The time is in the time zone set for the app under App Settings.
  • DATE fields—The date is exported in YY-MM-DD format.

Note: If users edit data in the models while the models are exported, then the result of the export is not well-defined. The exported data might contain all changes made in the data during the export, some of the changes, or none of the changes.

Export data to Google Sheets

1. In App Maker, open the app.
2. Click Settings Deployments.
3. Next to the deployment that has the data to export, click the Down arrow , then click Export data.
   Note: For the Preview deployment, choose any deployment. You can select Preview for the deployment during the export setup.

4. Choose an export option:

   • To export all data to a spreadsheet, select All models and relations data.
   • To export data from one or more models to a spreadsheet, select Selected models data.

5. Click Export.

Represent relations in Google Sheets

The way you represent relations in Google Sheets depends on whether the relation is one-to-one, many-to-one, or many-to-many.

One-to-one and many-to-one relations

In the sheet for a model that uses one-to-one or many-to-one relations, the last columns are named for the relation ends. The column values are the primary key names for associated records in the models.

In the following example:

• The CompanyRating model tracks company rating data.
• The Company model tracks company information, including compiled ratings data.
• The CompanyRating model has a many-to-one relation between "Ratings" and "Company".

• The last column in the CompanyRating sheet is named "Company", for the relation end. It contains the primary key names for the companies that are associated with each rating.

  

Many-to-many relations

For many-to-many relations, relations are listed in a separate sheet that's named relation-name (start-model-name - end-model-name). This sheet contains 2 columns, one column for each of the models in the relation. The rows list local key pairs for all associations. If a record doesn't have associations, it isn't included in this sheet.

In the following example:

• The Company sheet lists three companies and the Region sheet lists four regions.
• The Companies - CompanyRegions (Company - Region) sheet contains the relation data. "Companies - CompanyRegions" is the name of the relation.
• The many-to-many relation is represented with two columns, "Company" and "CompanyRegions", one for each model in the relation. The values are primary key names from the relation end models.

• The relation data shows that company Vendor 3 has associations with two regions and the North America region has associations with two companies.

  

To further explore how model relations are exported, you can use the Vendor Ratings template. Deploy the app and add some data to it. Then, export the data and look at the spreadsheet.

Simultaneous import and export operations

While one import or export operation at a time always works, App Maker supports the following simultaneous operations by you and any other developers for your app:

• You can simultaneously run data exports of any type for any deployments.
• You can simultaneously import data to different deployments.
• While you import all models and relations to a deployment, you can't simultaneously run any other type of data import to the same deployment.
• While you import data to a single model, you can simultaneously import data to another model. However, you can't simultaneously import data to the same model.