Creating "Google Sheets" via Websockets on Node.js

Introduction

Hi all! My name is German Panov and in this article we will develop a spreadsheet editor – a Google Sheets analogue (in a simplified form) based on websockets, in order to get acquainted with the ways of using this technology in browsers.

Since the goal is to get familiar, the code will not be very “clean”, but for a basic example this will be enough. We will use Node.js as a server platform, we will also need a package wswhich provides an API for working with websockets on the server.

Task

Create a spreadsheet editor according to the following requirements:

Functional requirements

editing table cells
saving table state
possibility of simultaneous use by several users in real time

Load requirements

Theory. Web socket protocol

Protocol WebSocket(“web socket”) provides the ability to organize a constant two-way data exchange between the browser and the server. The server sends a message to the client at any time, regardless of whether the client requested it or not. To do this, the browser needs to establish a connection with the server via handshake (“handshakes”)

The browser, using special headers, checks whether the server supports Websocket data exchange, and if so, “communication” will take place over WebSocket.

At the application level, text messages arrive in UTF-8 encoding; JSON format is used for convenient processing. You can view messages received via WebSocket in the Network -> WS tab in the browser’s devtools.

The transport protocol is TCP. Since data is fragmented, even large messages can be sent over Websocket. Each will be broken into frames, which are essentially similar to http messages.

A frame is a small header + “payload”. The header contains service information about the frame and the data being sent. A payload is any application data similar to the body of an http message.

Frame type:

The browser has an API for working with websockets, to establish a connection, you need to create a Websocket object, passing the URL as a parameter. By analogy with HTTP, there are two types of connection: with encryption (wss) and without (ws).

To work with data, you need to define callback functions for the created object. There are 3 types of handlers for network events: onopen, onerror, onclose and one handler for send messages events: onmessage. (You can read more here).

To send data to a socket, there is a send method.

const ws = new WebSocket('ws://localhost/echo');

ws.onopen = event => {
    alert('onopen');

    ws.send("Hello Web Socket!");
};

ws.onmessage = event => {
    alert('onmessage, ' + event.data);
};

ws.onclose = event => {
    alert('onclose');
};

ws.onerror = event => {
    alert('onerror');
};

On the server, we will use the popular WebSocket client-server library ws For Node.js. The API provided is very similar to the browser API.

You can see the full list of tools that support working with WebSocket Here

Basically, WebSocket is used for services that need constant communication:

Implementation. Editor writing

You will need to install the Node.js framework. Installation instructions can be found on the official website

The project will consist of two files:

HTML file with markup, styles and script for rendering the client side
JS file with server-side logic describing network interaction

Let’s create a project skeleton:

Let’s create a separate directory for the project.
We initialize a new git repository in the created directory.
We initialize a new npm package that will contain the project code.
Install the necessary packages for work. Of the third-party dependencies, we only need the package ws.
Let’s create a .gitignore file, in which we will write the “node_modules” directory, since it is not worth pulling a directory with installed dependencies in git.

# Создадим каталог проекта и перейдем в него
mkdir websocket-example && cd websocket-example

# Инициализируем git-репозиторий
git init

# Инициализируем npm-пакет, который будет содержать код проекта
npm init -y

# Установим библиотеку для работы с WebSocket на сервере
npm install ws

# Укажем git не учитывать каталог с установленными зависимостями
touch .gitignore && echo "node_modules" >> .gitignore

The basic environment is set up, you can start writing the application.

We organize the following project structure:

node_modules
public/index.html
src/server.js
.gitignore
package-lock.json
package.json

Define server tasks:

Saving the current state of the message history.
Sending the current state when a new user connects.
When a new message is received, send it to all connected clients.

Let’s start writing server logic.

server.js

Configuration
- Let’s declare the necessary dependencies
```
const ws = require("ws");
const fs = require("fs");
const http = require("http");
```
- Let’s prepare a document that will return the http server
```
const index = fs.readFileSync("public/index.html", "utf8");
```
- Let’s create an http server
  1. Set the host address and port that the server will listen on.
  2. We create a new instance of the server, in the callback we indicate that for all requests the server will return a prepared index.html document with a 200 response code.
  3. We start the server so that it starts listening on the specified host and port. Let’s display information about the successful start of the server in the logs.
```
const HOST = "127.0.0.1";
const PORT = 8000;

const server = http.createServer((req, res) => {
    res.writeHead(200);
    res.end(index);
});

server.listen(PORT, HOST, () => {
    console.log(`Server running at http://${HOST}:${PORT}/`);
});
```
- Let’s create a Websocket server running on top of an http server
```
const wss = new ws.WebSocketServer({ server });
```
- Let’s create an array to store the message history
```
const messages = [];
```

Networking logic

New connections

/*
  Класс WebSocketServer имеет метод on, позволяющий погрузиться
  внутрь жизненного цикла клиентского соединения и производить обработку каких-либо событий.

  Метод принимает первым аргументом событие, а вторым колбэк на это событие

  Типы событий: "connection" | "message" | "error" | "close"

  Вторым аргументом передается колбэк, параметрами которого будут
  текущее подключение и запрос, позволяющий получить служебную информацию
*/

wss.on("connection", (websocketConnection, req) => {
    // здесь будет логика взаимодействия
});

Handling a new connection

With a new connection, we will log the ip-address of the connected client and send out the current message history.

wss.on("connection", (websocketConnection, req) => {
    const ip = req.socket.remoteAddress;

    console.log(`[open] Connected ${ip}`);

	broadcastMessages(messages, websocketConnection);
});

Handling the receipt of a message

When a new message is received, we will output it to the logs, add it to the array of existing messages and send it to other clients.

wss.on("connection", (websocketConnection, req) => {
    const ip = req.socket.remoteAddress;
    console.log(`[open] Connected ${ip}`);

	broadcastMessages(messages, websocketConnection);

    websocketConnection.on("message", (message) => {
        console.log("[message] Received: " + message);

		messages.push(message);

        broadcastMessage(message, websocketConnection);
    });
});

Client Disconnect Handling

When the client is disconnected, we will display its ip-address in the logs.

wss.on("connection", (websocketConnection, req) => {
    const ip = req.socket.remoteAddress;
    console.log(`[open] Connected ${ip}`);

	broadcastMessages(messages, websocketConnection);

    websocketConnection.on("message", (message) => {
        console.log("[message] Received: " + message);

		messages.push(message);

        broadcastMessage(message, websocketConnection);
    });

    websocketConnection.on("close", () => {
        console.log(`[close] Disconnected ${ip}`);
    });
});

Secondary functions

Message history distribution function

function broadcastMessages(messages, client) {
    messages.forEach((message) => {
        if (client.readyState === ws.OPEN) {
            client.send(message, { binary: false });
        }
    });
}

Send new message feature

/*
  Доступ к списку текущих подключений осуществляется
  через свойство clients экземпляра сервера.

  Не забудем проверить, что клиент готов к получению и исключим клиента,
  сгенерировавшего это событие (у него оно уже есть).
*/

function broadcastMessage(message, websocketConnection) {
    wss.clients.forEach((client) => {
        if (client.readyState === ws.OPEN && client !== websocketConnection) {
            client.send(message, { binary: false });
        }
    });
}

This is where the server logic ends.

index.html

In the client part there will be a table that can be filled with data. When you click on any cell, it is highlighted and becomes available for editing.

Everything will be in public/index.html: markup, styles, dynamics. This must be done in index.html, since the server only returns it.

Define the structure of the html document

<!DOCTYPE html>
<html lang="en">
    <head>
        <style>
	         <! -- здесь будут стили -->
        </style>
    </head>
    <body>
  		<! -- таблица, которая будет динамически обновляться -->
        <table id="table"></table>
    </body>
    <script>
	    <! -- здесь будет логика -->
    </script>
</html>

Let’s configure the table

const COLUMNS = ["A", "B", "C", "D", "E", "F", "G", "I", "K", "L", "M", "O"];
const ROWS_COUNT = 30;

const table = document.querySelector("#table");

Create an object to store table cell values
```
const cells = {};
```

Initiating a Websocket connection to the server

const HOST = "127.0.0.1";
const PORT = "8000";

const API_URL = `ws://${HOST}:${PORT}/`;

const socket = new WebSocket(API_URL);

Event receiving logic

socket.onmessage = function (event) {
    const data = JSON.parse(event.data);
  
    const cell = cells[data.cell];
  
    cell.value = data.value;
};

Event dispatch logic
Each cell has a keyup event listener. When data is entered into a cell, the listener will work and generate an event from which it will be possible to “pull out” the address of the cell in which it occurred and the entered text.
Next, we send this data to the socket
```
function onKeyup(event) {
    const message = {
        cell: event.target.id,
        value: event.target.value,
    };
  
    socket.send(JSON.stringify(message));
}
```

Secondary functions

Table generation function

function generateTable(table, columns) {
	const tr = document.createElement("tr");

    tr.innerHTML =
		    "<td></td>" +
	      columns.map((column) => `<td>${column}</td>`).join("");

    table.appendChild(tr);
}

String generation function

function generateRow(table, rowIndex, columns) {
    const tr = document.createElement("tr");

    tr.innerHTML =
        `<td>${rowIndex}</td>` +
        columns
            .map(
                (column) =>
                    `<td><input id="${column}${rowIndex}" type="text"></td>`
            )
            .join("");

    table.appendChild(tr);

    columns.forEach((column) => {
        const cellId = `${column}${rowIndex}`;

        const input = document.getElementById(cellId);

        input.addEventListener("keyup", onKeyup);

        cells[cellId] = input;
    });
}

Table filling function

function fillTable(table) {
    for (let i = 1; i <= ROWS_COUNT; i++) {
    	generateRow(table, i, COLUMNS);
    }
}

Table rendering
To draw the table, we will call the prepared auxiliary functions for generating and filling the table
```
generateTable(table, COLUMNS);

fillTable(table);
```

Styles

*,
html {
    margin: 0;
    padding: 0;
    border: 0;
    width: 100%;
    height: 100%;
}

body {
    width: 100%;
    height: 100%;

    position: relative;
}

input {
    margin: 2px 0;
    padding: 4px 9px;

    box-sizing: border-box;

    border: 1px solid #ccc;

    outline: none;
}

input:focus {
    border: 1px solid #0096ff;
}

table,
table td {
    border: 1px solid #cccccc;
}

td {
    height: 20px;
    width: 80px;

    text-align: center;
    vertical-align: middle;
}

If you followed all the steps correctly, then when you start the server you will see a message about a successful start

websocket-example git:(main) ✗ node src/server.js
Server running at <http://127.0.0.1:8000/>

Now if you go to http://127.0.0.1:8000/ An editable table will open.

By launching several browser instances, you can see how everything works. Check that the new connection will open the data-filled table, if any changes have already been made to it.

By looking at the console of the running server, you can see the logs about the events taking place. Also, all sent / received messages can be viewed on the Network -> WS tab in the browser devtools

server.js

const ws = require("ws");
const fs = require("fs");
const http = require("http");

const index = fs.readFileSync("public/index.html", "utf8");


const HOST = "127.0.0.1";
const PORT = 8000;

const server = http.createServer((req, res) => {
    res.writeHead(200);
    res.end(index);
});

server.listen(PORT, HOST, () => {
    console.log(`Server running at http://${HOST}:${PORT}/`);
});

const wss = new ws.WebSocketServer({ server });

const messages = [];

wss.on("connection", (websocketConnection, req) => {
    const ip = req.socket.remoteAddress;
    console.log(`[open] Connected ${ip}`);

    broadcastMessages(messages, websocketConnection);

    websocketConnection.on("message", (message) => {
        console.log("[message] Received: " + message);

        messages.push(message);

        broadcastMessage(message, websocketConnection);
    });

    websocketConnection.on("close", () => {
        console.log(`[close] Disconnected ${ip}`);
    });
});


function broadcastMessages(messages, client) {
    messages.forEach((message) => {
        if (client.readyState === ws.OPEN) {
            client.send(message, { binary: false });
        }
    });
}

function broadcastMessage(message, websocketConnection) {
    wss.clients.forEach((client) => {
        if (client.readyState === ws.OPEN && client !== websocketConnection) {
            client.send(message, { binary: false });
        }
    });
}

index.html

<!DOCTYPE html>
<html lang="en">
    <head>
        <style>
            *,
            html {
                margin: 0;
                padding: 0;
                border: 0;

                width: 100%;
                height: 100%;
            }

            body {
                width: 100%;
                height: 100%;

                position: relative;
            }

            input {
                margin: 2px 0;
                padding: 4px 9px;

                box-sizing: border-box;

                border: 1px solid #ccc;

                outline: none;
            }

            input:focus {
                border: 1px solid #0096ff;
            }

            table,
            table td {
                border: 1px solid #cccccc;
            }

            td {
                height: 20px;
                width: 80px;

                text-align: center;
                vertical-align: middle;
            }
        </style>
    </head>
    <body>
        <table id="table"></table>
    </body>
    <script>
        const COLUMNS = ["A", "B", "C", "D", "E", "F", "G", "I", "K", "L", "M", "O"];
        const ROWS_COUNT = 30;

        const table = document.querySelector("#table");

        const cells = {};

        const HOST = "127.0.0.1";
        const PORT = "8000";

        const API_URL = `ws://${HOST}:${PORT}/`;

        const socket = new WebSocket(API_URL);

        socket.onmessage = function (event) {
            const data = JSON.parse(event.data);

            const cell = cells[data.cell];
            cell.value = data.value;
        };

        function onKeyup(event) {
            const message = {
                cell: event.target.id,
                value: event.target.value,
            };

            socket.send(JSON.stringify(message));
        }

        function generateTable(table, columns) {
            const tr = document.createElement("tr");

            tr.innerHTML =
                "<td></td>" +
                columns.map((column) => `<td>${column}</td>`).join("");

            table.appendChild(tr);
        }

        function generateRow(table, rowIndex, columns) {
            const tr = document.createElement("tr");

            tr.innerHTML =
                `<td>${rowIndex}</td>` +
                columns
                    .map(
                        (column) =>
                            `<td><input id="${column}${rowIndex}" type="text"></td>`
                    )
                    .join("");

            table.appendChild(tr);

            columns.forEach((column) => {
                const cellId = `${column}${rowIndex}`;

                const input = document.getElementById(cellId);

                input.addEventListener("keyup", onKeyup);

                cells[cellId] = input;
            });
        }

        function fillTable(table) {
            for (let i = 1; i <= ROWS_COUNT; i++) {
                generateRow(table, i, COLUMNS);
            }
        }

        generateTable(table, COLUMNS);

        fillTable(table);
    </script>
</html>

The entire code for the project can be viewed at Github

That’s all. I hope the material was useful to someone. If you find an error or inaccuracy, write about it in the comments 🙂

Sources

Creating “Google Sheets” via Websockets on Node.js

Introduction

Task

Theory. Web socket protocol

Implementation. Editor writing

server.js

index.html

We review and practice popular OSITN tools

Book “Roblox: Play, Program and Create Your Own Worlds”

Smartphone Marketing Tricks. Where is there no progress?

Honest review about 3 courses for developers from Yandex Practicum

Installing Packages

Telegram – how to create a chatbot

Leave a Reply Cancel reply

Introduction

Task

Theory. Web socket protocol

Implementation. Editor writing

server.js

index.html

Similar Posts

Leave a Reply Cancel reply