Godot-proposals: Add a Spreadsheet resource for handling tabular data

[willnationsdev]

Describe the project you are working on:

An RPG project in which characters and abilities have a variety of stats. For now, they are painstakingly edited as individual resources. We at one point experimented with importing CSV/TSV files, but the fact that the data is all strings and had to be separately maintained in another application get too frustrating to deal with.

Describe how this feature / enhancement will help your project:

Having a type-safe tabular data editor integrated into Godot would greatly improve the editing, designing, and balancing work of the team.

• Using an external spreadsheet editor does not provide any sort of type-safety.
• The external editors do not have type-aware property editors for individual cells based on the data type.
• While both Godot and external spreadsheet editors offer a means of defining a cell's value as an expression/function, the API for doing so is different. Therefore, logic must be shared and maintained between both software applications, resulting in cross-application code duplication.

Show a mock up screenshots/video or a flow diagram explaining how your proposal will work:

This concept is very similar to the UE4 DataTable asset type, which I drew pieces from in order to create the mockup. In this example, there is a "skill_data.gd" resource script that is constraining the resource instances and consequent editors available for the fields in the spreadsheet editor. You use the bottom panel to view/add/delete/select resources and use the Inspector to edit the fields of those resources.

Note that...

1. a selected record would show up in a sub-Inspector in the Spreadsheet's Inspector.
2. You'd be able to attach a script to the Spreadsheet that would display read-only aggregate values in exported script properties for the whole data set.

Describe implementation detail for your proposal (in code), if possible:

1. Create a means of exporting type reference as a property, constrained by a parent type. In this use case, a "Resource type" is needed.

   • The custom editor manages instances of an arbitrary resource type and displays the name of the type to the user. Users should be able to specify an engine type or a user-defined type.
   • As a workaround, one could export a String and a Script, respectively, but this involves a lot of code bloat boilerplate since there are two properties that are used to represent a single conceptual value that the engine simply doesn't yet have a means of representing at this time. It makes the code's intent less clear and needlessly complicates the structure of the class.
   • In this use-case, the specified class/script must also extend Resource, so the type must also be constrained to those which derive Resource. This means logic in the String or Script assignment would have to be written that validates whether the provided type refers to a valid Resource type, using ClassDB.is_parent_class(...) on both the provided String and the Script's get_instance_base_type() result.
   • A single export option that implements this logic would make far more sense to have as part of the engine.

2. Create a "Resource type" called Spreadsheet that maps a primary key StringName to a Resource type (in-engine type or scripted type). Without a type defined for the Spreadsheet, it will not accept the insertion of any records.

3. Have the primary key be defined, by default, as an auto-incremented ID number, but optionally allow users to override a virtual method in the Spreadsheet class that derives a primary key from the Resource record. This way, users can decide which property/composite string/other logic is used to decide what the primary key of a record is.

4. Create a ResourceImporter that can convert a CSV/TSV file into a Spreadsheet resource. A valid "Resource type" must be specified to use for the conversion.

   • All column names that match up with a property name on a specified class will be merged over to the generated "Resource type" instances. Print warnings to notify the user of columns that failed to migrate.

5. Create a bottom panel editor that renders a tabular set of data and provides a toolbar for interacting with the records.

   • Support adding/removing records
   • Change the "Resource type" of records in the Spreadsheet
   • Optionally import/export individual records as their own *.tres files.

I recommend writing a spreadsheet module that includes a C++-based plugin for the ResourceImporter and bottom panel editor. The only changes to the engine core that would be optional, yet ideal, is the implementation of the exportable "Resource type" functionality.

If this enhancement will not be used often, can it be worked around with a few lines of script?:

This cannot be worked around with just a few lines of script. It would involve a new resource type, a detailed set of editor changes, and a new export functionality, outlined above.

Is there a reason why this should be core and not an add-on in the asset library?:

Aside from the fact that lots of people use spreadsheets for large-scale data editing / balancing purposes and having an integrated editor that supports Godot-specific data structures would be great to have built into the editor?

All of the functionality outlined above could be achieved through an EditorPlugin except for the "Resource type" concept. And if that were implemented as its own standalone feature in the engine, the rest of it could go into an external plugin. But I believe the use-case (editing tabular data related to Godot projects, built upon Godot data structures) is far-reaching enough that it is something that should be included in the engine itself.

Edit: Updated to use the new ISSUE_TEMPLATE.

[golddotasksquestions]

I would greatly appreciate not having to write a parser for spreadsheets but native CSV support that would already parse the file on import, and provide it to me as Array or Dictionary.
This wound be a feature used by game designers (who are not necessarily programmers), so UI/UX should reflect that.
Usecases: Any game design that requires data driven balancing: Management or strategy games, FPS with a wide range of weapons, cardgames (every row resembles a card), ...
Currently I am working on a cardgame that would need this feature.

[willnationsdev]

@golddotasksquestions Note that, in this example, a CSV would be imported not as a Dictionary or Array, but as a Spreadsheet which wraps the data and would provide various means of accessing it depending on the settings it has been configured with.

For example, the uses_keys bool property would convert the interface between a Dictionary and an Array of Arrays, depending on whether the spreadsheet has been configured to understand how to interpret a row's primary key (in database terms). This is similar to my GDScript-based CSVFile implementation in godot-next (which you know because you've tinkered with it, but I'm stating for context purposes).

[willnationsdev]

I suppose it would be better to implement a full proof of concept in GDScript using an EditorPlugin since...as far as I can tell, all of the features that would be required can be done through an EditorPlugin.

[lentsius-bark]

I make extensive use of tabular data in in two of my projects and would 100% welcome this.

[Zylann]

If there was such resource type, one could then write a plugin to support importing ODS or XLSX spreadsheets :p
But in fact it can already be done entirely as plugin, so question is more should this be core, with basic CSV support, which currently imports as a translation, because... some people edit them in spreadsheets. 🙃

[willnationsdev]

Can't say I haven't thought about also supporting ODS/XLSX to some extent, but I think that once you've gotten to that point, it just goes beyond what is really needed by a game engine. Being able to properly import a CSV as its own spreadsheet resource is as much as we need, and it's a small enough in scope that I think it warrants a place in the main engine repository (doesn't really need to be in the engine core itself - stick mostly to module/editor stuff).

[Zylann]

When I said "core" I meant "built-in", sorry :)

[willnationsdev]

@Zylann Yeah, we really need to decide on official terms to differentiate stuff like that. Currently there's...

• the "engine core", i.e. the /core subdirectory.
• the "core engine", i.e. the parts of the codebase the constitute the actual engine itself:
  
  
  
  • /core
  
  
  • /main
  
  
  • /scene
  
  
  • /servers
  
  
  • possibly parts of /drivers or /platform, although those are SUPPOSED to be separate dependencies. I know for a fact I once tried to separate out the /core/map.h file from the rest of the engine to use it in a GDNative project and low-and-behold it eventually twisted its dependencies into the OS class and from there touched literally everything else in platform and other parts of the engine. -_-
  
  
  • This DEFINITELY does not include /editor and /modules. Probably doesn't include /thirdparty, though I can't be sure since I don't know what all in there is used where.
  
  
• The main engine repository versus external plugins / gdnative plugins, and external engine modules which are kept in separate repositories.

[golddotasksquestions]

Spreadsheets are so essential to gamedesign and the profession of a game designer, it sounds quite absurd to me if my engine of choice would only interpret them as translation files or would need a plugin to parse and access it's data.

[Zylann]

Keep in mind there is absolutely no problem at all loading such data. You can load CSV in a few lines of scripts and write specific editors with plugins too. But a spreadsheet can be interpreted in numerous ways, that's where making such a thing "built-in" becomes difficult because the engine cannot guess what users will do with this. Even translations as CSV is borderline to me, because it forces users to have a convention imposed by the engine (while proper formats even exist). But for game-specific data? There is no convention here. Yes I know it's an array of arrays, but that's not necessarily the way everyone wants to parse (transport format and work format don't always match). With an array of array it could barely implement a spreadsheet editor, which is completely redundant to actual spreadsheet programs.
The only problem I see here really, is to allow plugins to define extra import modes to CSV (if CSV is used).

[willnationsdev]

@golddotasksquestions The File class actually has get_csv_line() and store_csv_line() methods now which allow you to convert PoolStringArrays into CSV rows. The functionality of parsing and using them is all built-in already and allows one to use it however they want. The 3.2 release is also going to have a new CSV importer (not the default, unfortunately), which should simplify the process of keeping CSV files in one's project, parsing them, and editing data inside of them.

@Zylann The problem I have is wanting to edit tabular data in a type-safe way, where the rows are guaranteed to follow a particular schema of data types dictated by a script I've defined. There is no proper editor for handling that. So, this goes beyond "[allowing] plugins to define extra import modes to CSV". It's about having a tabular data editor for Godot-defined data types.

The point of conflict here is whether the use of this editor would be wide-spread enough to warrant it being in the main engine repository since the vast majority of the work for this can be done within a plugin. And I plan on creating said plugin here as a proof-of-concept. If we can show how useful it is / how many people star it / use it, then that should help indicate how valuable it could be as a built-in tool for the editor itself rather than having to be installed via a plugin.

Of course, another thing that could be done is relegating this to a plugin only and then adding a better system for more easily generating projects with a variety of plugins that one always wishes to use, etc. That's a little bit more like #62 though (but without the build-system part).

[golddotasksquestions]

@Zylann
CSVs are textfiles formated in a specific way. If I want to use them as text files to do whatever, I already can with File.new.
A CSV importer however could already interpret those as data sets, convert them to either and Array of Arrays or a Dictionary of Arrays, or translation. In the import setting there could be checkboxes for either, as well as the option to define if the first row or the first column should be the header.

The 3.2 release is also going to have a new CSV importer

Yes, I'm really looking forward to this! Constantly renaming my CSVs from file.csv to file.csv.txt is a more than a pain.

[willnationsdev]

@golddotasksquestions I mean, if you really just want to simplify that workflow, then we could always write a CSV class, like JSON, that essentially does the same thing that File does but on the backend, converting a file to an Array of PoolStringArrays. That's a different request than this Issue though.

var arr = CSV.parse("res://data.csv")

[nhydock]

Would we want the tabular data to remain only simple data types, or should each row be as flexible as a custom resource class and allow embedding of complex types such as vectors, curves, etc.? If it's already wrapped as a Custom "Spreadsheet" Resource type instead of actually saving as .csv files, I figure the row definition should be allowed as any Resource class.

[willnationsdev]

@nhydock Having a Resource script used as a schema for defining the columns of each row is exactly what I had in mind, that way you would get type-safe access to all Godot types.

[willnationsdev]

I recently saw reduz's tweet about typed arrays and the plans to eventually also have typed Dictionaries. If support for that were added, alongside support for user-defined resource type recognition, then I think we'd be well on our way to creating the backend for this since, at that point, it's just a matter of wrapping the data in an updated user interface and maybe having a sheets abstraction for any given Spreadsheet resource.

[aaronfranke]

Maybe you should make an editor plugin as a proof of concept, or as a complete solution. I don't really see why this needs to be in core, I have never had a use for this in Godot, so it sounds like a perfect candidate for an editor plugin instead of an engine feature.

[dragonDScript]

A file.get_csv_column() would be very useful too (in the core engine).

[Calinou]

@dragonDScript Please open a separate proposal for that. Also, use the Edit button (hidden behind the … dropdown next to your comments) instead of multi-posting.

[dragonDScript]

Sure I'll do, thank you.