Creating collabrative javascript spreadsheet made easy

In the last post, we have introduced how to create a javascript spreadsheet using FortuneSheet. This time, we will show you how to enable it's collabration feature.

Backend

Before we get into the topic, the first thing to be considered is backend storage, as the sheet data have to be saved so that others can see the lastest sheet state when they enter the page.

Websocket is our main transport of exchanging live data among clients. Here we choose Express as backend server, and MongoDB as database. The main role of express server is to manage websocket connection, serve initial data and process incremental op messages.

In this demo we simply use all documents of a collection for the sheets of our demo workbook.

async function getData() {
  const db = client.db(dbName);
  return await db.collection(collectionName).find().toArray();
}

We will skip the code of creating a simple express server, and focus on the core code. Don't worry, you can find the complete code at the end of this post.

Then, add some code for processing websocket messages, and manage connections for broadcasting.

const connections = {};

const broadcastToOthers = (selfId, data) => {
  Object.values(connections).forEach((ws) => {
    if (ws.id !== selfId) {
      ws.send(data);
    }
  });
};

const wss = new SocketServer({ server, path: "/ws" });

wss.on("connection", (ws) => {
  ws.id = uuid.v4();
  connections[ws.id] = ws;

  ws.on("message", async (data) => {
    const msg = JSON.parse(data.toString());
    if (msg.req === "getData") {
      ws.send(
        JSON.stringify({
          req: msg.req,
          data: await getData(),
        })
      );
    } else if (msg.req === "op") {
      await applyOp(client.db(dbName).collection(collectionName), msg.data);
      broadcastToOthers(ws.id, data.toString());
    }
  });

  ws.on("close", () => {
    delete connections[ws.id];
  });
});

Here, the applyOp function is the core of collabration. It reads the Ops sent by our frontend library, and perform data mutations to the database.

Processing Ops

The Ops are generated by the patch of Immer.js, for example, here is an op when user sets the cell font to be bold on cell A2.

[
    {
        "op": "replace",
        "index": "0",
        "path": ["data", 1, 0, "bl"],
        "value": 1
    }
]

We have to convert this op into MongoDB update query. If we convert it directly, the result will look like:

db.updateOne(
  { index: "0" },
  { $set: { "data.1.0.bl": 1 } }
);

However, considering storage size, we stores cell data sparsely in the database, that is, instead of storing the entire 2-dimension cell array, we store a 1-dimension array of cells that contain values. Thus, a cell in the database is in the form of

{
   r: number, // row index
   c: number, // column index
   v: any, // cell value
}

and the above update query becomes:

db.updateOne(
  { index: "0" },
  { $set: { "celldata.$[e].v.bl": 1 } },
  { arrayFilters: [{ "e.r": 1, "e.c": 0 }] }
);

Updates to others fields of the sheet are similar.

And that's all of our backend server. For the complete code, see https://github.com/ruilisi/fortune-sheet/tree/master/backend-demo

Frontend

Now let's focus on the frontend part, which is quite simple.

Step 1, create a websocket connection.

const wsRef = useRef<WebSocket>();

useEffect(() => {
  const socket = new WebSocket("ws://localhost:8081/ws");
  wsRef.current = socket;

  socket.onopen = () => {
    socket.send(JSON.stringify({ req: "getData" }));
  };
}, []);

Step 2, send ops from local change, receive ops from others and apply them into the workbook.

// A ref of Workbook
const workbookRef = useRef<WorkbookInstance>(null);

// In useEffect
socket.onmessage = (e) => {
  const msg = JSON.parse(e.data);
  if (msg.req === "getData") {
    setData(msg.data);
  } else if (msg.req === "op") {
    workbookRef.current?.applyOp(msg.data);
  }
};

// Workbook declaration
<Workbook
  ref={workbookRef}
  onOp={(op) => socket.send(JSON.stringify({ req: "op", data: op }))} />

For the complete code, refer to https://github.com/ruilisi/fortune-sheet/blob/master/stories/Collabration.stories.tsx

Thanks for reading

The repo is hosted on Github, completely open source. Give us a star if you find it useful 😄. Feedbacks are much appreciated!