GUID for Cells/Spreadsheets

[chris taubmann]

The main advantages of web-based Spreadsheets over file-based ones are their easy access (you dont need to have a special Program and access to a physical Drive/Folder) and the better control of the data on the server-side (like savepoints, locking, notifications etc.).

However, the flexibility of Spreadsheets means a loss of Control (or a huge Overhead) if you want to extract data or link data across the "File-Border" because it is nearly impossible to check and fix changes like the insertion of rows or columns (of course this problem occurs in file-based Spreadsheets too).

One possibility to solve this is to bind a "globally unique identifier" ( http://en.wikipedia.org/wiki/Globally_unique_identifier ) to every (newely created) Cell and Spreadsheet and of course to save this in the JSON/DB as well. It is relatively easy to create such GUID in Javascript (for examle: http://stackoverflow.com/a/2117523 ) and would probably also speedup things in big tables if the cell-lookup would use "getElementById".

Of course the Format of a GUID ( eg. 3F2504E0-4F89-11D3-9A0C-0305E82C3301 ) could also be adapted to hold Informations like the "Path" (FILE-SPREADSHEET-CELL) but probably this corrupts the idea of GUIDs.

what do you think?
Chris

[Robert Plummer]

We at one time did use getElementById, and it was actually quite a bit slower for cell calculations.  At the time being a virtual spreadsheet is assembled from the the table, and the virtual spreadsheet updated the table.  Much like angular works, but with it.  Using a GUID would actually be a little challenging to add, but would offer a lot of value.

One of the reasons jQuery.sheet is open source is so that it can improve from real world scenarios such as the one you are mentioning.  Would you be interested in forking and implementing?  I would gladly merge in a pull request.

--
Robert Plummer

[chris taubmann]

ok, i will try to figure out how to do this :-)

[Robert Plummer]

Very cool!  Also, keep in mind cell's can be named.  This could potentially be what you are looking for.

On Thu, Jan 9, 2014 at 2:34 PM, chris taubmann <stiller.b...@googlemail.com> wrote:

ok, i will try to figure out how to do this :-)

--
Robert Plummer

[chris taubmann]

I don't think it is a good idea to rely on user's input if you want to address a specific cell.

The easiest way to produce such GUIDs without touching the whole logic is to check/create them in jquery.sheet.dts.js

see
https://github.com/taubmann/jQuery.sheet/blob/master/plugins/jquery.sheet.dts.js

saving of the sheet-sorting is also missing. i try to figure out how to fix this.

chris

[chris taubmann]

i forgot to mention: creating GUIDs is activated with an additional Parameter.

Example:
$.sheet.dts.fromTables.xml(jS, trim, true); or
$.sheet.dts.fromTables.json(jS, trim, true);

To get the GUIDs of your Tables/Cells you can do a rough DOM-Check like this

// this Function checks for Tables/Columns with data-ids and manipulates the title-Tag to show the GUID
function dataId2Title() {
        $('table, td').each(function() {
            if($(this).data('id')) $(this).attr('title',$(this).data('id'));
        });
    };

Respecting the sort-order of sheets seems a little bit difficult for me. what do you think is the better way:

* rearranging the DOM just before triggering "sheetTabSortUpdate" in jquery.sheet.js OR
* respecting the sort-order of the tabs within jquery.sheet.dts.js

chris

[Sage Arbor]

I found this equation for google sheets to create a GUID.

=ArrayFormula(CONCATENATE(DEC2HEX( RANDBETWEEN( 0*ROW(YL$1:YL$8), 15)) ,"-" ,DEC2HEX( RANDBETWEEN( 0*ROW(YL$1:YL$4), 15)) ,"-" ,DEC2HEX( RANDBETWEEN( 0*ROW(YL$1:YL$4), 15)) ,"-" , DEC2HEX( RANDBETWEEN( 0*ROW(YL$1:YL$4), 15)), "-", DEC2HEX( RANDBETWEEN( 0*ROW(YL$1:YL$4), 15)), "-", DEC2HEX( RANDBETWEEN( 0*ROW(YL$1:YL$12), 15))) )