Feature request: spreadsheet-like functionality

[springfielddatarecovery]

Continuing to explore and love this software! One feature which is missing for us which is pretty important is the ability to create custom dashboards using a spreadsheet-like interface and being able to store variables/data for those dashboards. While Grist does a great job of showing records and combining information from records into graphs, it doesn't give us the level of control a typical spreadsheet does. It seems the solution to this is to make a custom widget (or export all the data into a CSV and import it into a spreadsheet), which is doable, but more complex than the point & click interface of a regular spreadsheet. If Grist is "spreadsheets but more powerful", this feature of a spreadsheet seems to have been lost somewhere.

Example 1 (Dashboards using information not found in table):

You use grist as a CRM. You have a table for customers (with their information etc) and a table for cases (each case is associated with a customer). You want to make a dashboard exploring how your profit and loss might look different if you changed your pricing. Doing this requires that your dashboard knows both the actual price paid by the customer (would be saved in a case entry) and the price point you want to explore, but that is something that isn't stored in any table. It could be stored as an attribute of the dashboard widget or in a separate table specifically for variables.

Example 2 (importance of variables): Storing single variables in your database not associated with records is important for other use cases as well. For example, let's say you create a bunch of tables and pages/widgets to make a full-blown CRM with grist. It works, awesome! But now you want to share that with other Grist users to use as a template. This CRM may have a number of features (such as clicking a link to send an e-mail to a customer) which require knowing the name of the company using the CRM. Of course, somebody using your template would have to find every instance of "COMPANYNAME" in their tables and change it. It would be much more convenient just to have a variables list/sheet they could edit. I think the best way to do this currently is just to make a table called "VARIABLES" and reference it when needed, but variables are not records so it feels a bit clunky.

Anyways, just submitting this as feedback, not expecting anything to be done about it, I know you all already have a lot on your plate. Thanks for the awesome software, we are in love!

[paparapapapapa]

I absolutely second this. I really would like to migrate all my spreadsheets from Google but at the moment I'm blocked by that lack of flexibility described by OP.

[dsagal]

I'll just mention here two ideas that have floated about at one point or another, which seem relevant. Neither is fully worked out, but something to start with.

1. It is possible (and used, somewhat regularly) to have variables by creating a single-record table (e.g. Global), with a column for each desired variable. These could then be used in formulas as e.g. Global.lookupOne().My_Variable. But it is admittedly clunky. One idea is to allow such a special "record" (collection of variables) to be associated with any table, perhaps refer to it as "table-wide values", and provide a more convenient UI to view/edit them, and to access in formulas (e.g. as Invoices.Company_Name). (Similar to static class variables in object-oriented programming.)

2. Sometimes, one legitimately has many many columns in a table (e.g. a hundred parameters, including calculated ones; or answers to a long survey with many questions). Showing them in a row is inconvenient. Showing them in a card looks better, but a Card View in Grist lacks many features of the Table widget (e.g. ability to easily add new columns and formulas). One idea is to allow a table-like view of a single record. Perhaps just a two-column view, where the first column is field names, and the second column is values. But maybe more flexible cell arrangements are possible too. Such a view could be convenient for working with global or table-wide values.

[paparapapapapa]

Bingo. Just yesterday I made a "complex" calculation from gross payout to net income. "Complex" merely meaning with a lot of steps, and every one of these involved a variable (split rates, 3rd parties commissions, tax rates, etc). Yesterday I achieved that in such a fashion:

+--------+----------+--------------------+------------+------------+--------------------+-----
|  value1   |  variable1  |  value2(=formula1)  |  variable2a   |  variable2b  |  value3(=formula2)  | .......
+--------+----------+--------------------+------------+------------+--------------------+-----

The many variable columns made reading the table difficult, so I made a card widget. Problem is, sometimes you just want to take a glimpse at a table to understand something (perhaps with the help of conditional formatting), while the card only lets you see a single record a time. Or to compare visually two different tables, just to have a feel of some variable modification before actually taking the time to write new calculations. So, yes, a variable table with global or local scope would definitely help. Thank you for listen and answer to your community.