Description

The guide describes common concepts and advanced settings, available to Excel and Google sheets

Data layout

Here is the illustration about how BGDatabase data is mapped to spreadsheets.

If you do not have a spreadsheet, you want to import, than exporting data will create a required data layout without any additional steps. If you want to import data from existing spreadsheet for the first time, you need to ensure that the data has a proper layout, e.g. your sheets has the same name as your database tables and the first row has field names.

[Optionally] Also, if you want to create a link between your external data rows and database rows, you need to add _id column to your data and ensure a job's "Update ids on import" parameter is toggled on.

Names mapping config (optional)

By default, naming convention is used to map sheets to tables and columns to fields. It can be overridden with names mapping config, which allows to use any names for sheets and columns and map them to tables/fields. Names mapping config is available for every data source and is used while importing/exporting from Unity Editor

Rows mapping config (optional)

By default, we use _id column in the sheet to keep the link between database row and excel/GoogleSheets row, and we recommend to use this default approach. However, additional approaches are also available: you can use int/string field as row's id if you have such columns with unique values or rows indexes (e.g. 1st database row is mapped to 1st sheet row etc.). Use these alternative strategies only if you can not use additional _id column. Rows mapping config is available at datasource page.

References config (optional)

References config allows to override how relational fields values are serialized. The recommended(default) option is by using row's ID. Additional options are by any int/string field as ID. The requirements are: the values for such fields should be unique and not empty - so these values could be used to identify the row. The limitations: if relational field allows multiple values - the vertical bar symbol (|) should not be used in field values, because it's used as values separator

Running Export/Import for the first time

To be able to sync with BGDatabase, you need a corresponding structure created in Excel/GoogleSheets, e.g. you need sheets, named after your tables names and header columns, named after you field names (as described above in "Data Layout" section).

You can easily create this structure by exporting your data to the empty spreadsheet/xls file

• Make sure to target empty spreadsheet/xls file
• Make sure to include all tables/fields you want to export in the settings. Included tables/fields will be highlighted in green
• Make sure all tables you want to include have "Add missing rows" toggle turned on.(cause all the rows will be "missing rows" when you export to empty spreadsheet)
• Export data once - all required structure will be created for you

Adding rows in external tools

Each row has its own id- 22 symbols string. It's computer-generated value, based on Guid generation.

While creating rows in external tool, you can leave it blank, and it will be updated during importing procedure (make sure you toggled "Update Ids on Import" on)

Additional jobs parameters

Name	Description
Update Ids on import	If you are adding new rows in external tools, leave _id column empty and enable this parameter. ID values from database will be transferred to _id column when you import new rows.
Transfer rows order	Rows order from the source will be transferred to the destination

Tables naming restriction

If you plan to use Excel/GoogleSheets, avoid giving your tables similar names with only difference in lower/upper letter(s) (like "test" "Test", "tESt" etc.). It will work fine with BGDatabase, however both Excel and GoogleSheets consider such names to be equal, and it will result in errors when you try to export the data. You can turn on case-insensitive check for tables under "Settings->Preferences->Enable case-insensitive table name check"