This is an idea
- Get rid of former and unfinished for too long spreadsheet integration in Tiki, with partial support from the developer (not much support at all in the last years, in addition)
- Use maintained code by other people, such as handsontable or others
- Get a table editor (for formulas, adding or editing data like in a spreadsheet) back working as expected in the supported stable tiki versions (including LTS versions)
- Link trackers and spreadsheets trough pretty mature software: pivottable with handsontable editor
- handsontable doesn't have the best FLOSS model that we would like
- Some extra options and libraries needed in handsontable (formula support, etc) doesn't seem to come pre-integrated in the floss versions of handsontable, even if they can be obtained through github and other means with the right license to get them integrated within tiki code base also.
- Even I like the idea and we have proof that it is possible to import Tiki tracker data to a Tiki integrated spreadsheet directly in Tiki with a simple dialogue, there are challenges to match the different concepts of Trackers and Spreadsheets :
Trackers are updated dynamically with a pre-defined form which is usually not altered by users (only by admins) on a per item base where one item represents a full row of a spreadsheet. Thus columns and formulas are not altered, but automatically added on item creation.
Spreadsheets are more static, but when they are edited, users, not only admins, can (and likely will) alter formulas, add or delete colums etc.. Think about a dynamically connected tracker or a spreadsheet that fetches data from a tracker and users add or delete formulas, columns etc. - this will not continue to work.
- basic differences:
|Content added/collected easily by/from large number of non-tecchie users with limited permissions on the tracker, but limited calculation capabilities - even not (yet) a way to sum the content of a column (of one tracker_field over several items)||Very flexible when it comes to calculations - like a matrix, but content is added by few users with high permission level|
|High control level of altering columns and formulas||Low to zero control of who is altering columns, formulas and content in which way, once access is granted|
|Calculation is set up in field definitions and thus valid for all future added content/items||Calculation is setup on a per field or field-range basis, which is very easy for static calculations. Even likely possible in Spreadsheets to calculate added content dynamically to some extent without altering the formulas, this seems to become very complicated to setup when content has to be added automatically by users using forms|
- Maybe it makes more sense to integrate a more maintained jQuery spreadsheed (vendor project) and add a dynamic tracker to spreadsheet export, where the imported table would be automatically locked for all non-superuser users, but calculations could be performed in additional tables of the same spreadsheet ... similar like in Excel/Calc tables can fetch and process data from other tables!?
- In a Calculations Use Case I described a possible way to use a new (not yet existing) field_type very similar to the field_type 'mathematical calculation' and existing field types for optional filtering to calculate the culomns of another tracker.
- I can still imagine, that a good setup spreadsheet with a dynamic tracker data import and calculation triggered automatically could provide kind of autoupdated statistical data ... thus kind of a mini-R system inside a Tiki on a shared hosting without the need of external servers and hired specialists.
- I think a decent integration of Trackers and Spreadsheets or autodynamic data import from trackers to (then locked) spreadsheets makes a lot of sense, but both features for now make imho more sense as two different features in favor of a merge of two different concepts. Basically a tracker is a data fetching form with a lot of display, filter, transition etc. and some calculation capability, which can output aswell a table view, whilst a spreadsheet is nothing else than a table with high calculation capabilities and some options to display results. Too different to merge, similar enough to interlink dynamically.
- Use handsontable library (MIT licensed) to display spreadsheet-like tables, allowing to edit cell contents with movement through keyboard arrows and such, and let you change display of columns and rows with plugin pivottable.
- See Pivottable plugin https://github.com/fbrussa/NovixPivotJS (MIT licensed also)
- we don't need nor want the highchart renderers (due to license issues), but we want the way to integrate handsontable in pivottable.
- Adding rows is easy: extend UI to add a new row to the tracker.
- Adding columns can be acchieved with some link to add new tracker field from the pivottable UI "somehow".
- Alternatively, use jExcel library (MIT Licensed): a lightweight alternative in case we like better the open source model and in case it allows easily to add/hack more formulas than the ones they provide, add ability to make charts from its data, etc.:
jExcel is is a very light jquery plugin to embed a spreadsheet, compatible with Excel, in your browser. You can load data straight to a jExcel table from a JS array, json or even a CSV file. You can copy and paste from or to Excel straight to a jExcel table. You can easily integrate jExcel with other third party jquery plugin to create your own custom columns, custom editors, and much more. jExcel has a plenty of nice features such as key-value dropdown, CSV loading/exporting, multiple spreadsheets and much more. We have a large roadmap ahead and we are constantly improving, so don't forget to send us your ideas.
That's the only alternative as of 2017 that might exist to handsontable (in https://jspreadsheets.com/ ) to be a potential candidate to replace jquery sheet in Tiki. The main developer of jExcel might be hirable for custom improvements?