Unity3D Google Spreadsheet export/import library
Odin Inspector Asset recommended to usage with this Package (https://odininspector.com)
As an alternative you can use:
- Alchemy Open Source Asset - https://github.com/annulusgames/Alchemy
{
"dependencies": {
"com.annulusgames.alchemy": "https://github.com/annulusgames/Alchemy.git?path=/Alchemy/Assets/Alchemy"
}
}
Add to your project manifiest by path [%UnityProject%]/Packages/manifiest.json new dependency:
"dependencies" : {
"com.unigame.google.api.v4" : "https://github.com/UnioGame/google.api.v4.git",
"unigame.unityspreadsheets" : "https://github.com/UnioGame/unigame.googlespreadsheets.git",
"com.unigame.typeconverters" : "https://github.com/UnioGame/unigame.typeconverters.git",
"com.unigame.addressablestools" : "https://github.com/UnioGame/unigame.addressables.git",
"com.unigame.unicore": "https://github.com/UnioGame/unigame.core.git",
"com.cysharp.unitask" : "https://github.com/Cysharp/UniTask.git?path=src/UniTask/Assets/Plugins/UniTask"
}
- HOWTO create api credentials https://developers.google.com/sheets/api/quickstart/dotnet
IMPORTANT
When you create an Desktop API KEY:
- Setup path to credential .json file and press "Connect Spreadsheet"
- Under target Google Profile allow application access to spreadsheets
- If you done everything correctly, you will see message in the console
Now you can specify your spreadsheets
- Id's of your sheet can be found right from web page url
- Copy your table id and paste into importer window field
- Export Serializable Project Data into Google Spreadsheet
- Support Custom Import/Export data providers
- Support nested synched spreadsheed fields
- Support export/import JSON to serializable classed
- Export/import into Addressables AssetReferences
- Export/import into Unity Scriptable Objects
- Export/import base Unity Assets types
Menu: "UniGame/Google Spreadsheet/Google Spreadsheet Asset"
You can make you own exporter by inheriting from:
- `SerializableSpreadsheetProcessor
BaseSpreadsheetProcessor
public class GameConfigurationProcessor : BaseSpreadsheetProcessor
{
public override bool CanImport => true;
public override bool CanExport => true;
public override ISpreadsheetData ImportObjects(ISpreadsheetData spreadsheetData)
{
return spreadsheetData;
}
public override ISpreadsheetData ExportObjects(ISpreadsheetData spreadsheetData)
{
return spreadsheetData;
}
}Example of writing custom value into spreadsheet. The method try to find related columns from spreadsheet and write value from source object
Important note: You should specify column names in spreadsheet or write column names with code
Some helper methods can be found in SpreadsheetExtensions.cs
public override ISpreadsheetData ExportObjects(ISpreadsheetData spreadsheetData)
{
//get some configs from somewhere
List<WeaponData> weaponConfigs = GetAssets<WeaponData>(locations);
foreach (var config in weaponConfigs)//write confgis one by one into spreadsheet
{
//write value to spreadsheet
spreadsheetData.UpdateValue(config, tableName);
}
//write list of values into spreadsheet
spreadsheetData.UpdateValues(weaponConfigs,tableName);
//write list of values into spreadsheet with custom id field name
spreadsheetData.UpdateValues(weaponConfigs,tableName,"Custom Id");
return spreadsheetData;
}Example of exporting custom value into cell
public override ISpreadsheetData ExportObjects(ISpreadsheetData spreadsheetData)
{
var upgradeConfig = AssetEditorTools.GetAsset<CharacteristicsUpgradeConfig>();
var defaultUpgrades = upgradeConfig.config.defaultUpgrades;
foreach (var data in defaultUpgrades)
{
var idName = nameof(data.Id);
var idValue = data.Id;
//white value into table
spreadsheetData.UpdateValue(data,tableName);
//write some custom value into cell with custom column name and custom id field vale
spreadsheetData.UpdateValue(data.Upgrade,tableName,idName,idValue);
var price = data.Price.Resources;
foreach (var upgradePrice in price)
{
var column = upgradePrice.Currency.ToStringFromCache();
//update cell value by column name and id field value
spreadsheetData.UpdateCellValue(tableName,idName,idValue,column,upgradePrice.Amount);
}
//write child object into table for more flat structure
spreadsheetData.UpdateValue(data.Price,tableName);
}
var levelUpgrades = upgradeConfig.config.upgrades;
return spreadsheetData;
}
Create table and columns in spreadsheet with API
public class GameConfigurationImporter : BaseSpreadsheetProcessor
{
//Id table name for synchronization with spreadsheet
//use can use property with asset name ofc as an id
public string idFieldName = "Id";
public string tableName = string.Empty;
/// <summary>
/// Export all object from unity to spreadsheet
/// </summary>
public override ISpreadsheetData ExportObjects(ISpreadsheetData sheet)
{
//get all assets by type and locations. This method just return the array of asset from somewhere
var weapons = GetAsset<WeaponData>(importLocations)
var fields = typeof(WeaponData).GetInstanceFields();
var sheet = data[tableName];
foreach (var field in fields)
{
if (sheet.HasColumn(field.Name)) continue;
//write column name into spreadsheet
sheet.AddHeader(field.Name);
}
foreach (var config in weapons)
{
//write value to spreadsheet
sheet.UpdateValue(config, tableName,idFieldName);
}
return sheet;
}
}Example of full custom import/export processor
public class GameConfigurationImporter : BaseSpreadsheetProcessor
{
//Id table name for synchronization with spreadsheet
//use can use property with asset name ofc as an id
public string idFieldName = "Id";
public string tableName = string.Empty;
public override ISpreadsheetData ImportObjects(ISpreadsheetData spreadsheetData)
{
//This method just return the array of asset from somewhere
var assets = GetAssets<WeaponData>()
foreach (var weapon in assets)
{
//read data from spreadsheet by table name and default id field name
spreadsheetData.ReadData(weapon, tableName);
//read data from spreadsheet by table name and id field name
spreadsheetData.ReadData(weapon, tableName,idFieldName);
}
var weapons = new List<WeaponData>();
//fill list by table data in single line
//this method will add new items into list each call without clearing
spreadsheetData.FillData(weapons, tableName);
return spreadsheetData;
}
}Example of adding new custom columns and convert data to target format. We are suppose what WeaponData has a lof of fields, but we want to export only some of them into spreadsheet.
[Serializable]
public class ItemEconomicsData
{
public string id;//Id of item in spreadsheet
public WeaponType weaponType;//enum value
public ItemRarity itemRarity;//enum value
public CurrencyID currency;
public int price;//price in currency
}
// Example of Export method to write custom value into spreadsheet.
public override void WriteToSheet(WeaponData value, ISpreadsheetData data, string tableId)
{
var item = new ItemEconomicsData()
{
id = value.Id,
weaponType = value.weaponType,
itemRarity = value.ItemRarity,
inventoryItemType = value.InventoryItemType,
currency = value.CurrencyID,
price = value.Price,
};
data.UpdateValue(item, tableId, idFieldName);
}You can fill list of models by table data in single line of code
var levels = new List<LevelDataModel>();
//here is your data will be filled by table
spreadsheetData.FillData(levels, tableName);
public static int FillData<TValue>(
this ISpreadsheetData spreadsheetData,
List<TValue> asset,
string sheetName,
string keyField = "")
where TValue : new()If you need to format you column or field names you can use these methods from SheetData class:
public static string FieldToColumnName(string fieldName)
public static string ColumnNameToFieldCamelCase(string columnName)
//remove _ prefix from field name
public static string FormatKey(string key) But in most cases it not nessesary, because SheetData will try to apply this formatting automatically.
Allow to mark serializable class as Synched with Google Spreadsheet.
SpreadsheetTargetAttribute(string sheetName = "",string keyField = "",bool syncAllFields = true)
Parameters:
- sheetName. Name of target Table into Google Spreadsheet. If sheetName empty, then class Type name will be used as SheetName
- keyField. Primary key field name for sync between spreadsheet and serialized class
- syncAllFields. If TRUE, then try to sync all class fields data. If FALSE - synchronized only fields marked with attribute [SpreadSheetFieldAttribute]
public class DemoSO : ScriptableObject{
[SpreadsheetIgnore]
public string someValue; // This field will be ignored during import/export process
}All fields marked with this attribute will be synchronized with target Spreadsheet data
public SpreadSheetFieldAttribute(string sheetField = "", bool isKey = false)
Parameters:
- sheetField. Name of target Spreadsheet column
- isKey. If TRUE, then target field will be used as Primary Key field.
[SpreadsheetTarget("DemoTable")]
public class DemoSO : ScriptableObject{
[SpreadSheetField("KeyField",true)]
public string key;
[SpreadSheetField("ValueField",true)]
[SerializableField]
private string value;
}
[SpreadSheetField("DemoTable",syncAllFields: true)]
public class DemoSO : ScriptableObject{
public string id; // Field with name Id | _id | ID will be used as Primary key by Default
private string value; // syncAllFields value active. Import/Export try to find Sheet column with name "Value"
}=============
Nested fields support
[SpreadSheetField("DemoTable",syncAllFields: true)]
public class DemoSO : ScriptableObject{
public string id; // Field with name Id | _id | ID will be used as Primary key by Default
[SpreadSheetField("ResourcesTable",syncAllFields: true)]
private int cost; // syncAllFields value active. Import/Export try to find column with name "Cost" from sheet "ResourcesTable"
}
Nested Table
Spreadsheet library support all base value types.
To add support your custom type you need to realize new converter that extends base interface ITypeConverter. As a shortcut you can implement BaseTypeConverter class and mark with [Serializable] attribute.
public interface ITypeConverter
{
bool CanConvert(Type fromType, Type toType);
TypeConverterResult TryConvert(object source, Type target);
}[Serializable]
public class StringToVectorTypeConverter : BaseTypeConverter
{
....
}Now we can add it into converters list
your can make reference to unity asset by type filtering and specifying name of asset
[SpreadSheetField("DemoTable",syncAllFields: true)]
public class DemoSO : ScriptableObject{
public string id; // Field with name Id | _id | ID will be used as Primary key by Default
private Sprite iconAsset;
}
Same usage as regular unity asset type with only one exception. The type of target field must be AssetReference or inherited from AssetReference
[SpreadSheetField("DemoTable",syncAllFields: true)]
public class DemoSO : ScriptableObject{
public string id; // Field with name Id | _id | ID will be used as Primary key by Default
private AssetReference iconReference;
}
For more complex scenarios JSON serialization can be used
[SpreadSheetField("DemoTable",syncAllFields: true)]
public class DemoSO : ScriptableObject{
public string id; // Field with name Id | _id | ID will be used as Primary key by Default
[SpreadSheetField("ItemsTable",syncAllFields: true)]
private ItemData defnition; // sync item data from json value value
}
[Serializable]
public class ItemData
{
public string id;
public int position;
}
for more complex import scenario we can use Pipeline Importer Asset
Create Menu: "UniGame/Google/Importers/PipelineImporter"
Each step of import pipeline take data from previous one. For custom import step your should implement one of:
- SerializableSpreadsheetImporter: pure serializable c# class
- BaseSpreadsheetImporter: ScriptableObject asset
For example:
- Create Units Prefabs by Spreadsheet Data
- Apply Unit settings from sheets data to assets
- Mark Assets as an Addressables an move them into target group
Selected co-processor execute after main import processor (after parsing and applying every row).
Custom co-processor
[Serializable]
public class MyCustomCoProcessor : ICoProcessorHandle
{
// some properties
public void Apply(SheetValueInfo valueInfo, DataRow row)
{
// some code
}
}Example
For example, Nested Table Co-Processor applies nested google-table where filter is parsing pattern:
