Log in to start a topic

Synchronizing TableSheet Edits with an In‑Memory Hierarchical Data Source

Posted by: ichioka.yuji on 2 March 2026, 2:59 am EST

  • ichioka.yuji

    Posted 2 March 2026, 2:59 am EST

    Hello,

    I am developing an application that edits hierarchical data (a collection of row data that contains a Level field) using a SpreadJS TableSheet.

    The data source for the hierarchical data is an in‑memory array of JSON objects. I want changes made by editing cells in the TableSheet to be automatically reflected in the in‑memory data source immediately after the edit.

    • When using the DataManager’s addTable() method, which of the following is the correct way to bind the in‑memory JSON object array as the data source for the table?
    • I want edits made in the TableSheet to be automatically reflected in the in‑memory data source right after a cell is edited. Is it correct to register a cell‑editing event handler using Workbook.bind(), and have that handler call saveRow()? Is there any other recommended approach?
    • When rows are added, deleted, or updated, how can the Table object or the data source detect these changes? Is it possible to know exactly which rows were added, deleted, or updated?

    I would greatly appreciate any advice you could provide.

  • ichioka.yuji

    Posted 2 March 2026, 12:08 pm EST

    I’m sharing my current implementation code. When a value is entered in the Name column, a string that ends with “.” represents a folder, and any other string represents an item. If a row is a folder, the rows that follow it are assigned a hierarchy level increased by one.

    interface Row {
  rowOrder: number;
  level: number;
  name?: string;
  type?: 'folder' | 'item';
}
            const workbook = new Sheets.Workbook(host, { sheetCount: 0 });
        const tableSheet: TableSheet.TableSheet = workbook.addSheetTab(0, 'Tags', Sheets.SheetType.tableSheet);

        // An in-memory datasource of hierarchal row items
        const rows: Row[] = [];

        const dataManager = workbook.dataManager(); 
        const table = dataManager.addTable('', {
          data: rows,
          schema: {
            columns: {
              rowOrder: { dataType: 'rowOrder' },
              level: { dataType: 'number' },
              name: { dataType: 'string' },
            },
            hierarchy: {
              type: 'Level',
              column: 'level',
            },
          },
        });

        table.fetch().then(() => {
          const view = table.addView(
            'myView',
            [
              { caption: 'Name', value: 'name', outlineColumn: true },
              { caption: 'Type', value: 'type', readonly: true },
            ]);
          tableSheet.setDataView(view);
        });

        const onEditEnded = (_: Sheets.Workbook, args: Sheets.IEditEndedEventArgs) => {
          tableSheet.saveRow(args.row).then(
            () => {
              // Sort rows by order number before scanning them
              rows.sort((r1, r2) => r1.rowOrder - r2.rowOrder);

              let rowOrder: number = 0;
              let level: number = 0;

              for (const row of rows) {
                const name = row.name;
                if (name) {
                  // A row having a name ending with '.' means a folder.
                  if (name.endsWith('.')) {
                    row.type = 'folder';
                    row.level = level++;
                  }
                  else {
                    row.type = 'item';
                    row.level = level;
                  }
                }
                else {
                  delete row.type;
                  row.level = level;
                }

                // Re-assign an oder number for each row
                row.rowOrder = rowOrder++;
              }

              tableSheet.getDataView().fetch(true).then(() => workbook.repaint());
            }
          );
        };
        workbook.bind(Sheets.Events.EditEnded, onEditEnded);

  • chirag.gupta

    Posted 3 March 2026, 5:24 am EST

    Hi Yuji,

    Thank you for your detailed explanation.

    Below is a summary of the recommended approach when working with hierarchical data in a TableSheet using an in-memory JSON array.

    1. Binding the in-memory data source

      The correct way to bind an in-memory JSON array is by using DataManager.addTable() and passing the array through the data property:

         const table = dataManager.addTable("TagsTable", {
     data: rows,
     autoSync: true,
     schema: { ... }
   });

      When autoSync: true is enabled, the TableSheet and the underlying JSON array are automatically synchronized.

    2. Reflecting edits immediately in the JSON array

      In a TableSheet, cell edits are committed using:

         tableSheet.saveRow(rowIndex)

      It is recommended to call saveRow() inside the EditEnded event. Once saveRow() completes, the corresponding object in the in-memory array is updated immediately.

      No manual synchronization logic is required.

    3. Detecting added, updated, and deleted rows

      TableSheet internally tracks row-level changes. You can retrieve them using:

         const changes = tableSheet.getChanges();

      The returned object contains structured collections:

      • added
      • updated
      • deleted

    This allows you to determine exactly which rows were modified.

    1. Hierarchical behavior

      When using a Level-based hierarchy configuration in the schema, updating the level field or calling hierarchy APIs such as promoteHierarchyLevel() / demoteHierarchyLevel() correctly updates both the UI and the underlying data source.

    We have attached a working sample below demonstrating:

    • In-memory binding
    • Automatic synchronization
    • Change tracking
    • Hierarchical level management

    Please let us know if you require any further assistance.

    Best regards,

    Chirag

    Attachment: https://jscodemine.mescius.io/share/ZV6-XgVlB0SVvTjP7D_z8w

  • ichioka.yuji

    Posted 3 March 2026, 10:58 am EST - Updated 3 March 2026, 8:51 pm EST

    Thank you for the sample. I tried it with several editing patterns. When changing “Folder 1.” to “Folder 1” in the top row, nothing happened, although I expected all rows to become flat at level 0. I couldn’t figure out what caused it. I would really appreciate it if you could help me further.

    Considering paste/delete operations on cell ranges and drag‑fill operations as well, I think the most reliable approach is to calculate/update all level values in the in‑memory data source and then reload the whole table whenever the name column of any row is edited. What do you think about this approach?

  • chirag.gupta

    Posted 5 March 2026, 3:44 am EST

    Hi,

    Thank you for your detailed testing and for sharing your observations.

    You are correct that when the hierarchy is derived from the Level column, any logic that recalculates the level values in the in-memory data source must ensure that the TableSheet rebuilds its internal hierarchy tree. Simply updating the data source does not automatically refresh the hierarchy structure in the UI.

    Based on your suggestion, we implemented an approach similar to what you proposed:

    1. When a cell edit is completed, the edited row is committed using saveRow().
    2. The hierarchy is then recalculated in the in-memory data source by recomputing the type, level, and rowOrder values for all rows based on the rule (names ending with . are treated as folders).
    3. After updating these values, the DataView is refreshed using fetch(true).
    4. The view is then rebound to the TableSheet using setDataView() so that the hierarchy tree is rebuilt and the changes are immediately reflected in the UI.

    This ensures that scenarios such as:

    • converting a folder to an item (e.g., changing “Folder 1.” => “Folder 1”),
    • paste or drag-fill operations,
    • multiple row edits,

    correctly trigger a full hierarchy recalculation and update the TableSheet display accordingly.

    We have prepared a working sample demonstrating this approach here: https://jscodemine.mescius.io/share/XXrNl1Ag7UW_Yldj7uMCRg

    Please feel free to review the sample and let us know if you have any further questions.

    Best Regards,

    Chirag

  • ichioka.yuji

    Posted 6 March 2026, 12:59 am EST - Updated 6 March 2026, 4:38 am EST

    Thank you!! This brings me much closer to what I want to achieve.

    Based on your sample, I reviewed and updated my implementation, so I’d like to share it with you.

    index-table-sheet.zip

    However, one issue remains: among the editing operations in TableSheet, the following operation events cannot be captured:

    • Undo after a cell edit
    • Deleting or pasting a cell range

    I have an additional question. For the remote property included in the dataSourceOptions argument of the DataManager.addTable() method, I am supposed to assign an event handler of type RemoteChangeHandler. In this event handler, what value is the correct one to return?

    I would appreciate your guidance so I can make sure I understand it correctly.

    https://developer.mescius.com/spreadjs/api/modules/GC.Data#remotechangehandler

            const dataManager = spread.dataManager();
        const table = dataManager.addTable("TagsTable", {
            batch: true,
            schema: {
                hierarchy: {
                    type: "Level",
                    column: "level"
                },
                columns: {
                    name: { dataType: "string" },
                    address: { dataType: "string" },
                    type: { dataType: "string" },
                    level: { dataType: "number", defaultValue: 0 },
                    rowOrder: { dataType: "rowOrder" },
                }
            },
            remote: {
              read: async () => window.rows,
              batch: async (rows) => {
                // Hierarchal rows recomputation after batch changes
                const [newRows, shouldReload] = recomputeRows(rows);
                window.rows = newRows;

                if (shouldReload) {
                  setTimeout(async () => {
                    const view = sheet.getDataView();
                    await view.fetch(true);
                    sheet.setDataView(view);
                  });
                }

                // ** What value should be returned? **
                return rows;
              }
            }
        });

  • priyam.kushwaha

    Posted 9 March 2026, 6:09 am EST

    Hi Yuji,

    Thank you for sharing your updated implementation — the overall architecture looks solid and well thought out. Let me address each of your outstanding questions.

    1. Capturing Undo, Paste, and Delete Operations

    You are right that most standard sheet events are not supported in TableSheet. For undo and delete operations, the recommended approach is to use commandManager().addListener(), which fires for every command executed through the UI. Inside the listener, you can check args.actionType === 1 to identify an undo action, and inspect args.command.cmd to handle specific commands like delete accordingly:

    spread.commandManager().addListener("App", (args) => {
  if (args.actionType == 1) {
    console.log("undo", args.command.cmd);
    requestIdleSubmitChanges();
  }
});

    Regarding RangeChanged not firing: this event is only supported on regular Sheets, not TableSheets, so it can be safely removed from your implementation. Your existing use of CellChanged, DragDropBlockCompleted, and DragFillBlockCompleted is already the correct approach for those operations.

    For paste operations, bind to ClipboardPasted on the sheet:

    sheet.bind(GC.Spread.Sheets.Events.ClipboardPasted, (e, args) => {
  console.log('ClipboardPasted', args);
  requestIdleSubmitChanges();
});
    1. What value should the batch handler return?

      According to the RemoteChangeHandler API, the batch function must return a Promise resolving to the rows that were actually committed — that is, the rows after all your custom logic has been applied.

    In your current implementation, you are returning the original rows argument before recomputeRows() runs. This can cause the DataManager’s internal cache to fall out of sync with window.rows, which may be the root cause of the unexpected behavior. The fix is straightforward — return newRows instead:

    batch: async (rows) => {
  const [newRows, shouldReload] = recomputeRows(rows);
  window.rows = newRows;

  if (shouldReload) {
    setTimeout(async () => {
      const view = sheet.getDataView();
      await view.fetch(true);
      sheet.setDataView(view);
    });
  }

  // Return the recomputed rows, not the original argument
  return newRows;
}

    Best regards,

    Priyam

  • ichioka.yuji

    Posted 23 March 2026, 12:04 am EST - Updated 23 March 2026, 12:21 am EST

    Thank you for your help.

    Regarding capturing user operations that modify data, I implemented the code below. In particular, I updated the logic that checks whether args.command.cmd is ‘clearValues’ or ‘undo’, so that it works correctly even when performing an undo after a drag‑fill-block or drag-copy‑block operation.

            sheet.bind(GC.Spread.Sheets.Events.DragDropBlockCompleted, ({type}, args) => {
          console.log(type, args);
          requestIdleSubmitChanges();
        });

        sheet.bind(GC.Spread.Sheets.Events.DragFillBlockCompleted, ({type}, args) => {
          console.log(type, args);
          requestIdleSubmitChanges();
        });

        sheet.bind(GC.Spread.Sheets.Events.ClipboardPasted, ({ type }, args) => {
          console.log(type, args);
          requestIdleSubmitChanges();
        });

        spread.commandManager().addListener("App", (args) => {
          const cmd = args.command.cmd;
          switch (cmd) {
            case 'clearValues':
            case 'undo':
              console.log(cmd, args);
              requestIdleSubmitChanges();
              break;
          }
        });

    Additionally, is it possible for me to capture the event that occurs after executing the “Add After” / “Add Before” row actions from the context menu?

  • priyam.kushwaha

    Posted 23 March 2026, 6:20 am EST

    Hi Yuji,

    Thank you for sharing your updated implementation — the event handling logic looks clean and well structured.

    Capturing “Add Before” / “Add After” Context Menu Actions

    You can use the RowChanged event for this. It will be triggered whenever “Add Before” or “Add After” is selected from the context menu. Refer to the snippet below:

    sheet.bind(GC.Spread.Sheets.Events.RowChanged, function (e, info) {
    console.log("rowchanged", info);
});

    Reference:

    Regards,

    Priyam

  • ichioka.yuji

    Posted 23 March 2026, 9:32 pm EST - Updated 23 March 2026, 9:39 pm EST

    Thank you very much.

    Following your advice, I was able to successfully capture the events for the “Add After” / “Add Before” row actions as well. I’m sharing the updated source below.

    index-table-sheet.zip

    Unfortunately, I have run into a new issue.

    As shown in the attached video, when I enter the name “Folder2.” into the row added by the “Add After” action, the expected hierarchical data does not appear.

    SpreadJS v19 TableSheet - clipped.zip

    The image below shows a dump of the internal JSON object array that serves as the data source. The array elements have the correct content and order, but the order of the hierarchical row items displayed in the TableSheet (through the View) does not match this.

    1554×423 68 KB

    In this operation, the View is being reloaded internally — is it actually functioning correctly?

  • priyam.kushwaha

    Posted 24 March 2026, 7:51 am EST

    Hi,

    Thank you for sharing the sample — we were able to replicate the behavior you described.

    Before we investigate further, we would like to clarify the expected hierarchy structure. When you type “Folder2.” into the row added via “Add After”, what is the intended visual output in the TableSheet you expected?

    For example, should it appear as:

    Option A — Folder2 as a sibling of Folder1 (both top-level):

    Folder1.

    Item1

    Item2

    Folder2.

    Option B — Folder2 nested inside Folder1 (as a sub-folder):

    Folder1.

    Item1

    Folder2.

    Item2

    Option C — Something else entirely:

    Could you please clarify the expected behavior with a concrete example? If possible, a screenshot or short video showing how the hierarchy should look after the operation would be very helpful.

    Once we have a clear picture of the expected output, we will investigate on our end and get back to you with our findings.

    Best regards,

    Priyam

  • ichioka.yuji

    Posted 24 March 2026, 9:38 am EST - Updated 24 March 2026, 5:15 pm EST

    What I expect is Option B.

    When executing the “Add After” command on the second row, an empty item (with Level = 1) is inserted into the third row, and the “Item2” item (Level = 1) moves to the fourth row.

    Then, when I enter “Folder2.” in the Name column of this third row, the “Folder2.” item (Level = 1) should remain in the third row, and the “Item2” item in the fourth row should move up to Level 2. This behavior always matches the internal JSON object array.

    In the current actual behavior, however, the “Folder2.” item moves to the fourth row immediately after entering “Folder2.” into the empty item in the third row, which seems incorrect.

    1280×720 127 KB

  • priyam.kushwaha

    Posted 25 March 2026, 8:13 am EST

    Hi,

    Thank you for the information. We have checked this on our end but were unable to identify the cause of the behavior, so we have escalated it to the development team. The internal tracking ID is SJS-34317.

    We will update you as soon as we receive any information.

    Regards,

    Priyam

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels

  • © 2023 MESCIUS USA, Inc. All Rights Reserved.  ·  1.800.858.2739

    All product and company names herein may be trademarks of their respective owners.