Tutorial: Synchronize Remote Data and Table Layout

This tutorial shows how to keep Remote Data and a bound worksheet table layout consistent in a SpreadJS collaboration application.

It uses the collaboration overview sample from the SpreadJS distribution package as the starting point.

For the collaboration scope and operation behavior of worksheet tables bound to DataManager tables, see Worksheet Table Binding with DataManager.

Overview

When a worksheet table is bound to a DataManager table that uses Remote Data, the remote data source itself is not synchronized by the collaboration system.

The business application must provide a remote data change notification mechanism. After receiving a notification, each client can refresh the remote data and, when appropriate, synchronize the worksheet table layout.

This tutorial demonstrates two refresh modes:

Default Refresh Mode — Fetch Remote Data and update the worksheet table layout in one step.
Split Refresh Mode — Fetch Remote Data first, then synchronize the worksheet table layout only when the application decides to do so.

Prerequisites

This tutorial assumes that:

You have the SpreadJS collaboration overview sample available.
The page contains a worksheet table bound to a DataManager table that uses Remote Data.
Your application can notify clients when the remote data changes.

The sample uses socket.io as the real-time notification channel. If your application already has a real-time channel, use your existing mechanism instead.

Start the Collaboration Sample

Open the collaboration overview sample in the SpreadJS distribution package:

SpreadJS.Release.x.x.x\SpreadJS\samples\Collaboration\overview

Install dependencies and start the sample:

npm install
npm run start
npm run start-server

Open the sample page:

http://localhost:8081

Create at least two online user views.

Add a Remote Data Change Notification Channel

Install socket.io dependencies:

npm install socket.io socket.io-client

In a production application, you can replace socket.io with your own real-time notification service.

Update the Server to Notify Data Changes

Update the server so that it can:

Serve Remote Data
Accept data changes
Notify connected clients after the remote data changes

The sample server should provide endpoints such as:

GET    /api/records
POST   /api/records
DELETE /api/records/:id
POST   /api/records/reset

After a successful data change, the server should emit a notification event such as:

records:changed

The notification should include a stable identifier that lets the client locate the corresponding DataManager table.

Example message:

{
    dataSourceId: "sales-records"
}

In this example, sales-records is used by the client to find the target DataManager table.

Attach the Client Listener

Attach a listener after the workbook joins the collaboration session.

For example, in the collaboration initialization code:

import { attachRecordsChangeSocket } from "./records-change-socket";

let currentRecordsSocket: { disconnect?: () => void } | null = null;

bind(workbook, doc).then(() => {
    currentRecordsSocket = attachRecordsChangeSocket(workbook);
});

When the workbook is destroyed, disconnect the real-time channel:

if (currentRecordsSocket) {
    currentRecordsSocket.disconnect?.();
    currentRecordsSocket = null;
}

Option A: Default Refresh Mode

Use this mode when the application wants the simplest refresh flow.

In this mode, the client calls fetch(true) on the DataManager table after receiving a remote data change notification.

Create src/client/records-change-socket.ts:

import { io } from "socket.io-client";

export function attachRecordsChangeSocket(workbook: any) {
    const socket = io({
        path: "/records/socket.io/",
        transports: ["websocket"]
    });

    socket.on("records:changed", async (message: { dataSourceId: string }) => {
        const dataManager = workbook.dataManager();
        const table = dataManager.tables?.[message.dataSourceId];

        if (!table) {
            return;
        }

        await table.fetch(true);
        workbook.repaint();
    });

    return socket;
}

This flow fetches the latest Remote Data and updates the bound worksheet table layout in one step.

When integrating this mode into a business application, consider:

Logging when dataSourceId cannot be mapped to a DataManager table.
Handling request failures from fetch(true).
Merging repeated notifications that arrive within a short time.

Option B: Split Refresh Mode

Use this mode when the application needs to control when worksheet table layout synchronization occurs.

In this mode, the client:

Calls refreshRemoteData() to fetch the latest Remote Data.
Calls isTableLayoutDirty() to check whether the worksheet table layout needs synchronization.
Calls adjustTableRangeForBind() when the application decides to synchronize the layout.

Create src/client/records-change-socket.ts:

import { io } from "socket.io-client";

export function attachRecordsChangeSocket(workbook: any) {
    const socket = io({
        path: "/records/socket.io/",
        transports: ["websocket"]
    });

    socket.on("records:changed", async (message: { dataSourceId: string }) => {
        const dataManager = workbook.dataManager();
        const table = dataManager.tables?.[message.dataSourceId];

        if (!table) {
            return;
        }

        await table.refreshRemoteData();
        workbook.repaint();

        if (hasDirtyTableLayout(workbook)) {
            const shouldSync = window.confirm(
                "Remote data changed. Sync the worksheet table layout now?"
            );

            if (shouldSync) {
                syncDirtyTableLayouts(workbook);
            }
        }
    });

    return socket;
}

function hasDirtyTableLayout(workbook: any) {
    const sheetCount = workbook.getSheetCount();

    for (let i = 0; i < sheetCount; i += 1) {
        const tables = workbook.getSheet(i).tables.all();

        for (const table of tables) {
            if (table.isTableLayoutDirty?.() === true) {
                return true;
            }
        }
    }

    return false;
}

function syncDirtyTableLayouts(workbook: any) {
    const sheetCount = workbook.getSheetCount();

    for (let i = 0; i < sheetCount; i += 1) {
        const tables = workbook.getSheet(i).tables.all();

        for (const table of tables) {
            if (table.isTableLayoutDirty?.() === true) {
                table.adjustTableRangeForBind();
            }
        }
    }
}

The window.confirm call is only a minimal trigger for the sample. In a production application, you can replace it with:

A toolbar button
A permission-controlled action
A leader-client strategy
A scheduled synchronization process
A business-specific version control rule

When integrating this mode into a business application, consider:

Checking only worksheet tables associated with the changed dataSourceId.
Handling failures from refreshRemoteData().
Calling adjustTableRangeForBind() only from clients that are allowed to synchronize layout.
Merging frequent notifications before checking layout state.

Validate the Integration

Rebuild and restart the sample:

npm run start
npm run start-server

Open:

http://localhost:8081

Create at least two online user views.

Make sure the page contains a worksheet table bound to Remote Data.

Trigger remote data changes from the browser console or an API client.

Add a Record

fetch("/api/records", {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({
        item: "Marker",
        units: 10,
        cost: 2.5
    })
});

Delete a Record

fetch("/api/records/1", {
    method: "DELETE"
});

Reset the Data

fetch("/api/records/reset", {
    method: "POST"
});

Expected Result

In Default Refresh Mode, clients refresh the Remote Data and update the bound worksheet table layout after receiving the remote data change notification.

In Split Refresh Mode, clients refresh the Remote Data first. The worksheet table layout is synchronized only after the application triggers layout synchronization.

If a newly joined collaborator loads the latest collaboration state and the current remote data no longer matches the worksheet table layout, the application can refresh the remote data and synchronize the table layout using the same flow.

Next Step

For the capability scope and operation behavior, see Worksheet Table Binding with DataManager.