Utilities [WIP] Spreadsheet Database 4U (SSDB)

It's data entry time again! Yay ...

Ugh. I know. You dread doing that, because data entry is so:

• uncomfortable & restricted (Unity Editor GUI tools => from awkward to unsuited for data entry)
• tedious & error-prone (text files like C#, JSON, XML)
• overkill & suicidal (Database: choosing, installing, configuring, hosting, account management, connection programming, SQL queries, frequent administration and maintenance, security - just to scratch the surface ...)

You also prefer your data to be available statically typed in code through readable properties?

Then you'll want Spreadsheet Database 4U!

An unsexy tool for an unsexy job that needs to get done anyway in almost any game. With SSDB it will be at least a satisfying and reliable workflow.

Here's how your workflow will look like:

• Open the Spreadsheet program of your choice
  • eg Google Sheets, MS Excel, OO Calc, Apple Numbers, ...
  • Hint: Excel users get a VBA macro to export everything with a button press.
• Enter, sort, filter, format, formalize, visualize, share and analyze your data sets in the best tool for the job (= Spreadsheets)
• Export to Unity (as CSV file, possibly others in future)
• Automatically generated ScriptableObject database with table and record classes gives you statically typed access to your data, including relational references
• Use indexes (int or string) or Linq queries to access your data in editor or runtime scripts, read on for an example

Here's a simple workflow example, in this case with Excel. I love it AND hate it - yet it's still the best by far. I'll provide additional support for Excel since I have many, many years of experience with it.

Here's a "SampleGameData" workbook with a single worksheet named "Difficulties" with the header row defining the property names (Id is mandatory; Id requires a unique value per row; rows can be indexed either with numbers like here but also using strings!):



There's these nicely colord rows and dropdown buttons for filtering & sorting - that's because I created a "Table" of that data. This is not necessary but highly recommended because it makes working with formatted data a lot more comfortable and safer (sorting sorts entire rows, not just individual columns!). I can provide guidance.

Now export the data. Save as CSV. For Excel use the VBA macro I provide to export all worksheets with a button press without changing the document's format (Excel, why? Whyyy?). The exported CSV can be saved anywhere in the Assets folder. Here I simply placed the XLSM in the same folder because it doesn't hurt. The CSV file must have an infix (Google's default of " - ") to differentiate between the names for the Database (SampleGameData) and Table (Difficulties) since SSDB supports importing an entire spreadsheet with multiple worksheets as a single database:



Notice the CSV contents. You'll see that reading CSV is not as simple as using String.Split(","). Here the delimiter is a semicolon (any region that has comma as decimal separator will use semicolon in CSV - thus the delimiter is configurable). Some strings are in quotation, because they contain delimiters. And then you have % and $ format characters, too. Plus all the region and program-specific quirks and variations (if you encounter one that I haven't caught yet => contact me!).

Instantly, the Unity AssetDatabase picks up the added or changed CSV file. This triggers the SpreadsheetPostprocessor which generates the Record, Table and Database classes, creates a ScriptableObject script and asset, then loads the data into the SO asset.

Note: If you only edit, add or remove rows of data the import procedure will be instantaneous! There's no Assembly Reload (recompile) happening in those cases. That means, once you have settled on a data structure all imports will be blazing fast!



Notice how it correctly reads formatted values. Also, percentages are represented as float (10% => 0.1). There's also a Assembly Definition file generated to keep the database scripts in their own assembly and namespace (I intend to make that configurable).

The Difficulties.cs script represents the Table (=worksheet) and contains a dictionary and sorted list of DifficultiesRecord instances, allowing for indexed access (difficulties[3] or difficulties["Medium"] if you use string Id) as well as stable linear enumeration of records.

You simply add a reference to the ScriptableObject database to any of your scripts, then drag&drop the database ScriptableObject asset in the Inspector:

Right now, the Assembly Definition File (ADF) for the database is always generated. That requires adding references to an existing Assembly Definition file on your side. I opt to make the ADF optional and off by default since, while I prefer clean and separate code, it's just going to give new user's a headache and negative initial experience with SSDB.

Then access the database contents by using statically typed references, as in:


Each worksheet generates table and record classes that hold the data. Records represent a row in the spreadsheet, in this example the DifficultiesRecord looks like this:

Code (CSharp):

1. using UnityEngine;
2. using System.Collections.Generic;
3.  
4. namespace SampleGameData
5. {
6.    [System.Serializable]
7.    public sealed class DifficultiesRecord : SSDB.Runtime.RecordBase
8.    {
9.       public new static DifficultiesRecord Create(IList<object> record)
10.       {
11.          return new DifficultiesRecord(record);
12.       }
13.  
14.       [SerializeField] private System.String _name;
15.       [SerializeField] private System.String _description;
16.       [SerializeField] private System.Single _dropChance;
17.       [SerializeField] private System.Int32 _startMoney;
18.  
19.       public System.String Name => _name;
20.       public System.String Description => _description;
21.       public System.Single DropChance => _dropChance;
22.       public System.Int32 StartMoney => _startMoney;
23.  
24.       public DifficultiesRecord(System.Int32 id, System.String name, System.String description, System.Single dropChance, System.Int32 startMoney)
25.       {
26.          _id = id;
27.          _name = name;
28.          _description = description;
29.          _dropChance = dropChance;
30.          _startMoney = startMoney;
31.       }
32.  
33.       public DifficultiesRecord(IList<object> record)
34.       {
35.          _id = System.Convert.ToInt32(record[0]);
36.          _name = System.Convert.ToString(record[1]);
37.          _description = System.Convert.ToString(record[2]);
38.          _dropChance = System.Convert.ToSingle(record[3]);
39.          _startMoney = System.Convert.ToInt32(record[4]);
40.       }
41.  
42.       public override string ToString()
43.       {
44.          return $"DifficultiesRecord(Id={Id}, Name=\"{Name}\", Description=\"{Description}\", DropChance={DropChance}, StartMoney={StartMoney})";
45.       }
46.    }
47. }

I consider SSDB production ready if it were an internal-only tool. But it requires additional work to polish and publish it as a tool that others will use, in a variety of situations. My task list looks as follows:

• documentation
• support multiple databases (= multiple workbooks for different things)
• resolve relational objects: replace ThisOtherSheetId indexes with actual ThisOtherSheetRecord instances to minimize relational queries
• demo data and scene
• test data export/import with common spreadsheet tools
• project settings for:
  • specify delimiter
  • prefix/suffix of import files (in case you use also them for other things)
  • infix of import files (defaults to Google's and my Excel exporter's infix: " - ")
  • namespace (optional, name)
  • make assembly definition file optional
  • probably more ...

It should be in the asset store in February with a price point of tbd (around 20 I suppose).

In the meantime, if you have any questions, suggestions or any other feedback please don't hesitate to leave a comment! The main point of this thread is to gather interest and more requirements.