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")

[erodozer]

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.

[ghost]

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.

[ghost]

Sure I'll do, thank you.

[Xrayez]

After revisiting old proposals, I think I've unknowingly implemented a class in Goost which shares a lot of similarities regarding proposed Spreadsheet resource here.

See VariantMap class. Basically, it's a simple two-dimensional data container that map Variant types onto a grid. You can have any type stored in the grid at run-time with set_cell() and access the elements with get_cell().

Unfortunately, Goost does not currently provide any kind of editors for the class to be useful for use cases such as editing spreadsheets etc, but in theory it should be quite possible to do. Since Goost implemented as a C++ module, it should be possible to reuse Godot's property editors to make it work in a general-purpose way, without reinventing the same property editors via script (which I believe would be a major blocker/limitation to implement this feature easily via script).

I've chosen VariantMap name because:

• it handles Variants internally.
• it maps Variant values onto a grid.
• not ambiguous with UI Control classes (like proposed in #2662).
• familiar with classes like:
  • BitMap (which is basically the same thing, but only handles bool values);
  • TileMap (you can use VariantMap to conveniently store tile metadata alongside, which also seems to be a more or less common feature request: godotengine/godot#31529, godotengine/godot#12634).

[mia-bentzen]

I'm quite new to contributing to open source, so apologies if this is the wrong place to say this, or the wrong way to say it.

I think a really useful feature to have in this kind of editor would be some amount of basic formula (i think that's what excel calls it?) support. So you can either set values directly, or (for types that support it) create an expression (that may reference other values in the spreadsheet) that calculates what the value should be. I imagine this would probably be accomplished by using GDScript to handle the expressions.

I personally would be satisfied if it was just regular numbers it supported, but you could probably make it work for vectors and colors too.

[don-tnowe]

I was looking for a way to edit many resources at once and looked for some plugin Didn't find anything... So made exactly that:

Link to repo

It uses folders of Resources instead of some special Table datatype, so game logic does not depend on it. It can show Resources in the Inspector and allows appending text or setting a property in several Resources at once.

Also has some cool tools to manipulate numbers, shift and darken colors, and chop Textures into several AtlasTextures.

Pic:

[Zireael07]

Current CSV import (whether in 3.5 or 4.0) is pretty unwieldy.

This is a sample of CSV data for my space game:

name, winchellX, winchellY, winchellZ, color, planets, multiple, comments, ra, dec, dist-Sol Proxima Centauri, 2.8ly, -3.1ly, 0.1ly, red, yes, -, //7200 AU or 0.13ly away from Alpha Alpha Centauri, 2.9ly, -3.1ly, 0.1ly, yellow, yes, Tau Ceti, -3.4ly, 0.4ly, -11.4ly, yellow, yes, Barnard's Star, 5.0ly, 3.0ly, 1.4ly, red, yes, , , 17.96h, 4.7, 5.95

The way to parse it involves having to remember which column is which index, instead of writing data["name"] or data["color"]...

Currently need to add another column, and honestly, I'll likely try the add-on mentioned above just for that reason.

[jordanlis]

Hi everyone. Personaly, I think it could be really usefull to have something related to database and handling data inside godot. I think it could be really usefull to have a data editor such as the one that don-tnowe showed.

But at some point, what's really important is not editing this file, which could be done in any software like excel.

The real matter is handling the CSV file easily in gdscript code. For example if you have a weapon and that you want you access its properties, you have to parse it and so on.

One thing that could be great could be just to create and access to this database natively in gdscript with something like this :

1. Create a database : database = create_database_from_csv_file("name_of_the_table1.csv", "name_of_the_table2.csv", "and_so_on.csv")

2. access to database entries database.table1.line[1] #Allows to access to each lines of the database, each parameters, etc.

So maybe what we should focus on should be to handle in code a database, a table, entries and parameters.

But maybe ressources already allows it, not sure about it.

[Zireael07]

@jordanlis Access to line is easy. Accessing the correct cell/column is the more annoying part currently.

[jordanlis]

@jordanlis Access to line is easy. Accessing the correct cell/column is the more annoying part currently.

Hi, totally agree with you. I spend 20 hours trying to parse a CSV in a nested dictionary, and it still doesn't work because of the way dictionaries are working so it's obviously really hard to get tabular data as a database in godot and exploit it.

I'll probably switch to handling data in ressources, because it seems to work properly but still, it requires to create one file for each ressource of each kind, which is why Kehom has done this add-on for example : http://kehomsforge.com/tutorials/multi/GodotAddonPack/part07. It allows to create ressources by editing tabular data, just like you would edit a CSV. But this is much more powerfull because you can handle var types inside your database (string, int, bool, image, etc.) such as any database would work.

So, from my point of view we have 2 solutions to answer the users' needs :

1. Create a database from a CSV and edit it in an external tool (optionally inside godot) --> It allows to create a database very easily --> Optional in this case : It could be great to have an editor inside godot to handle this csv database in a spreadsheet editor, but not necessarily -->... But does not provide advanced management (cannot handle images for example, or string/int/bool type, and so on)

2. Create a database inside godot using ressources --> This will allow us to create separate ressources files FROM godot with a tabular editor for example OR to create each ressources in the filesystem and retrieve it inside GODOT, in a tabular editor surch as the one below --> Mandatory in this case : This version would require to create a spreadsheet editor inside Godot to edit database with ressources as entries in the filesystem.

Maybe solution 1 cost less, but maybe the real goal would be to have something like @don-tnow showed because it will use already powerfull existing ressources.

[Zireael07]

One resource per kind is what @don-tnowe does, too. That addon you linked is afaict pretty much the same as @don-tnowe except it has some data access/handling functions. And I can't find Kehom's source code, just the blog

[jordanlis]

One resource per kind is what @don-tnowe does, too. That addon you linked is afaict pretty much the same as @don-tnowe except it has some data access/handling functions. And I can't find Kehom's source code, just the blog

Yep, sorry, here's the addon link : https://github.com/Kehom/GodotAddonPack/tree/target-gd3.2/addons in "keh_gddb" folder.

You're right, this is pretty much the same thing for the 2 addons, which is good so multiple people ended up with the same solution. Maybe it could help use to decide between solution 1 and 2 in my previous message.

[ishax-kos]

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.

After watching one of sakurai's gamedev videos, im now convinced that supporting a spreadsheet format with formulas is necessary. At least insofar that you would be able to read cached formula values.

[jordanlis]

Formulas, I don't know but a database in some format is mandatory according to me. It's just almost the case for the translation part, but it lacks some features or it is not adapted perfeclty.

[ishax-kos]

Google appears to ship an API to read from online spreadsheets. It would need to be build time, and this would be amazing for teams. It would go a long way towards enabling a "single source of truth" for large projects.

[Patchcoat]

It seems like this one feature request encompasses multiple different specific considerations.

1. Tabular data is very useful, necessary even, for large sections of game development. Godot should be able to handle it.
2. Resources are how Godot stores data. We should interface with that and provide an in-engine and type-safe way to edit them.
3. We should improve GDScript support for csv files, one of the simplest forms of tabular data.
4. We should have have formulae available when editing the spreadsheet.
5. We should be able to interface with external tools.

For an internal tool, I believe we should read from and write to a csv file, and improve the GDScript support for csv along the way. This means users can't store their formulae in the saved file, they must bake them down to the results. It also means the user can't store their formulae in an ODS file on the side, because they risk that file going out of sync when the csv is edited in the engine.

However, we keep the interface simple which reduces the surface area for bugs and the effort required to maintain it while serving most of the developers who want tabular data. Once proper csv support is in, then we can worry about reading from ODS files and parsing formulae. I realize the temptation is there to skip straight to ODS, because there are more features, but I believe the simpler problem more people want should be tackled first.

[Gnumaru]

I've made an addon for importing spreadsheets like xlsx, ods and other data file formats like yaml and xml.

https://godotengine.org/asset-library/asset/3048

It's an import plugin meant for editor usage only. It saves parsed data in a JSON resource since it its the only way of saving variant data as resource without using a wrapper script that wrapps the data in a single element array since we cant yet export untyped properties in gdscript.

I do think godot could provide a built-in csv import function for importing csvs as a two dimensional string array. But personally I don't think having a tabular editor itself inside godot for editing csvs is better than just editing an xlsx in excell or ods in libreoffice.

Of course there's the problem of type safety, but it can be mitigated by checking the data at runtime or by creating type checks at import time.