Post

How to build a real-time chat app with Google Sheets

Learn how to build a real-time chat application using JavaScript, Google Apps Script, and Google Sheets.

Posted Updated
By creme332
5 min read

Having scarce knowledge of backend technologies, I decided to build 1bxoxb1, a simple anonymous chat app, with Google Sheets. The back-end code, written entirely in Google App Script, surprisingly took less than 40 lines of code. The goal of this post is to document the process to build this project. The source code for the project is available on Github under the MIT license.

GIF of chat app A real-time no-login online chat app at https://creme332.github.io/1bxoxb1/

Getting started

Build project

Create a Google Sheets database

  1. Go to Google Sheets to create a new spreadsheet with a name of your choice.

  2. Open your spreadsheet and add three columns names date, username, and message :

dateusernamemessage
   
   

Create AppScript project

Go to the AppScript dashboard to create and open a new AppScript project with a name of your choice.

AppScript dashboard

You will see a single file Code.gs in your project. Create 3 new HTML files to make your file structure look like this :

1
2
3
4
5

Files
│_   Code.gs
|_   Index.html
|_   Stylesheet.html
|_   JavaScript.html

Write server-side code

All server-side code must be written in the Code.gs file.

Add spreadsheet information

Add 3 global constants to store database information.

1
2
3

const SPREADSHEET_URL = "PLACE YOUR URL HERE";
const spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
const worksheet = spreadsheet.getSheetByName("YOUR SHEET NAME");

Add boilerplate code

1
2
3
4
5
6
7
8
9
10

function doGet() {
  return HtmlService
      .createTemplateFromFile('Index')
      .evaluate();
}
 
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
    .getContent();
}

The doGet() function for templated HTML generates an HtmlTemplate object from the HTML file. Then doGet() calls its evaluate() method to execute the scriptlets and convert the template into an HtmlOutput object that the script can serve to the user.

The custom server-side include() function imports the Stylesheet.html and JavaScript.html file content into the Index.html file. When called using printing scriptlets, this function imports the specified file content into the current file.

Update database with data from client

When a user sends a message, the server-side function addNewRowToSheet() is called from the client-side.

addNewRowToSheet() will append the date, the name of the sender, and the message to the spreadsheet.

1
2
3

function addNewRowToSheet(username, user_input) {
  worksheet.appendRow([new Date().toString(), username, user_input]);
}

The date must be in string format so that it can be passed to the client later. Read more about legal parameters in AppScript here.

Send spreadsheet to client

getSpreadsheetData() will return a 2D array containing all database information to the client.

1
2
3
4
5
6
7
8
9
10
11
12

function getSpreadsheetData() {
  //get data from first three columns
  const AllData = worksheet.getRange("A:C").getValues();
 
  //remove column heading
  AllData.shift();
 
  // remove empty rows from AllData and return result
  return AllData.filter(function (el) {
    return el[0] != "";
  });
}

Write client-side code

HTML

Edit the Index.html file.

Add <?!= include('Stylesheet'); ?> in head tag and <?!= include('JavaScript'); ?> just before end of body tag.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <?!= include('Stylesheet'); ?>
</head>
<body>
  <div class="container">
    <div id="message-container"></div>
    <div class="bottom-container">
      <textarea placeholder="Type a message" id="input-container" cols="30" rows="1"></textarea>
      <button id="sendbtn"> > </button>
    </div>
  </div>
  <?!= include('JavaScript'); ?>
</body>
</html>

Wireframe of html page Wireframe of the website

CSS

Edit the Stylesheet.html file.

The CSS code is available here. The code itself is not important and can be modified.

Remember to include <style> tags in Stylesheet.html.

JavaScript

Edit the JavaScript.html file.

I will explain only the important parts of the code. The full JS code is available here.

Remember to include <script> tags in Stylesheet.html.

Send message to database

Each time the send-btn element is clicked, saveToSpreadsheet() is called. saveToSpreadsheet()takes input from the input-container and calls the server-side function addNewRowToSheet() which then saves the message to the database.

1
2
3
4
5
6
7
8
9
10
11

function saveToSpreadsheet() {
    //ignore empty messages
    if (userInputBox.value == "") return;
   
    //send username and message to spreadsheet
    google.script.run.addNewRowToSheet(MY_USERNAME, userInputBox.value);
   
    //reset userInputBox
    userInputBox.value = "";
}
sendButton.addEventListener("click", saveToSpreadsheet)
Update messages in real-time

Every REFRESH_RATE milliseconds, the following line is executed :

1

 google.script.run.withSuccessHandler(onSuccess).getSpreadsheetData();

  • script.run.getSpreadsheetData() calls the server side function getSpreadsheetData().

  • The withSuccessHandler(onSuccess)part will call the function onSuccess() if the server-side function returns successfully. The return value of getSpreadsheetData(), which is a 2D array, becomes the argument of the OnSuccess() function.

  • onSuccess() will look for new messages since the last time getSpreadsheetData() was called. These messages are then displayed in the message-container element.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

  function updateMessages() {
    function onSuccess(spreadsheetDataArray) {
      //loop through new rows since last update
      const lastRowIndex = spreadsheetDataArray.length - 1;
      for (let i = currentRowIndex + 1; i <= lastRowIndex; i++) {
        let date = spreadsheetDataArray[i][0];
        let user = spreadsheetDataArray[i][1];
        let msg = spreadsheetDataArray[i][2];
        let timePosted = getTime(date);
 
        // code to add the above information to message-container
        // ....
      }
      currentRowIndex = lastRowIndex;
    }
    google.script.run.withSuccessHandler(onSuccess)
      .getSpreadsheetData();
  }
  setInterval(updateMessages, REFRESH_RATE);

The number of messages currently being displayed in the message-container is $ \text{currentRowIndex + 1} $, where $ \text{currentRowIndex} $ is a global variable.

The default value of REFRESH_RATE is 2000 milliseconds. It can be reduced to make refreshing of messages faster but in doing so, the number of concurrent users on the chat app will be reduced.

Deploy project

  • Deploy your project as a web app.
  • The web app requires you to authorize access to your data.

If you face the “Sorry unable to open file at this moment” problem, try opening the web app in incognito. Read this for more information.

Limitations

As you may have guessed, using Google Sheets as your “backend” does come with some limitations:

  • A maximum of 30 concurrent users is allowed.
  • Google Sheet API allows at most 300 requests per minute.

There are also some drawbacks of using the AppScript Online IDE:

  • No version control system like Git.
  • No keyboard shortcuts available like in VSCode.
  • Rename Symbol option was not working at the time when I wrote my code.
Tutorial
AppScript javascript
This post is licensed under CC BY 4.0 by the author.