Create a Sidebar with Google Apps Script

Google Sheets allows you to create and display a custom sidebar that can communicate with the sheet.

On this page, you will discover a simple example of a sidebar with a form that records the entered contact at the end of a table in the sheet.

For the example, this form can also be displayed as a custom dialog box.

You can copy this Google Sheets document from this page.


Custom Menu

The first function in this file adds a custom menu that allows you to display the form in the sidebar or in a dialog box:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Sheets-Pratique')
    .addItem('Form (sidebar)', 'form')
    .addItem('Form (dialog box)', 'form2')
    .addItem('Form (non-blocking dialog box)', 'form3')
    .addToUi();
}

Preview:

google sheets custom menu png sidebar

Sidebar

The form function displays the form in the sidebar:

function form() {
  const html = HtmlService.createHtmlOutputFromFile('form').setTitle('Add a User');
  SpreadsheetApp.getUi().showSidebar(html);
}

Preview:

google sheets sidebar form png

Dialog Box

The form2 function displays the same form but in a dialog box:

function form2() {
  const html = HtmlService.createHtmlOutputFromFile('form')
    .setWidth(300)
    .setHeight(485);
  SpreadsheetApp.getUi().showModalDialog(html, 'Add a User');
}

Preview:

google sheets dialog box form png sidebar

The form3 function displays a "non-blocking" dialog box (which allows, for example, to modify cells while having the dialog box open):

function form3() {
  const html = HtmlService.createHtmlOutputFromFile('form')
    .setWidth(300)
    .setHeight(485);
  SpreadsheetApp.getUi().showModelessDialog(html, 'Add a User');
}

Preview:

google sheets dialog box form showmodelessmialog png sidebar

HTML

The HTML code of this form (without style or script) is as follows:

<!DOCTYPE html>
<html>
  <head>
  </head>
  <body>
    <p>Name</p>
    <input type="text" name="name" value="">
    <p>First Name</p>
    <input type="text" name="first_name" value="">
    <p>Address</p>
    <input type="text" name="address" value="">
    <p>Postal Code</p>
    <input type="text" name="postal_code" value="">
    <p>City</p>
    <input type="text" name="city" value="">
    <input type="button" value="Add" onclick="add()">
    <span class="cancel" onclick="google.script.host.close()">Cancel</span>
  </body>
</html>

It consists of paragraphs:

<p>Name</p>

Text fields:

<input type="text" name="name" value="">

A button that executes the add function on click:

<input type="button" value="Add" onclick="add()">

And a "link" Cancel that closes the window on click:

<span class="cancel" onclick="google.script.host.close()">Cancel</span>

JavaScript

The JavaScript code for this form (inserted between the script tags) contains the add function which is triggered on button click:

<script>
  function add() {
    const inputs = document.querySelectorAll('input[type="text"]');
    let array = [];

    // Retrieving the values
    for (const input of inputs) {
      array.push(input.value);
    }

    // If all fields are empty
    if (array.join('') == '') {
      alert('The form is empty!');
      return;
    }

    // Clear the fields
    inputs.forEach(input => input.value = '');

    // Sending to the sheet
    google.script.run.addLine(array);
  }
</script>

The function starts by retrieving the text fields of the page:

const inputs = document.querySelectorAll('input[type="text"]');

It then retrieves the values of these fields and adds them to the array:

for (const input of inputs) {
  array.push(input.value);
}

To avoid inserting an empty line, the function checks if there is at least one non-empty field and stops there if not:

if (array.join('') == '') {
  alert('The form is empty!');
  return;
}

The fields are then cleared (for the next addition):

inputs.forEach(input => input.value = '');

And the addLine function (Apps Script) is executed, with the array containing the field values passed as an argument:

google.script.run.addLine(array);

Apps Script

The addLine function inserts the data passed as an argument following the contacts in the sheet:

function addLine(array) {
  SpreadsheetApp.getActiveSheet().appendRow(array);
}

Complete Codes

Finally, here is the complete Apps Script code:

// Custom menu
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Sheets-Pratique')
    .addItem('Form (sidebar)', 'form')
    .addItem('Form (dialog box)', 'form2')
    .addItem('Form (non-blocking dialog box)', 'form3')
    .addToUi();
}

// Sidebar
function form() {
  const html = HtmlService.createHtmlOutputFromFile('form').setTitle('Add a User');
  SpreadsheetApp.getUi().showSidebar(html);
}

// Dialog box
function form2() {
  const html = HtmlService.createHtmlOutputFromFile('form')
    .setWidth(300)
    .setHeight(485);
  SpreadsheetApp.getUi().showModalDialog(html, 'Add a User');
}

// Dialog box (showModelessDialog)
function form3() {
  const html = HtmlService.createHtmlOutputFromFile('form')
    .setWidth(300)
    .setHeight(485);
  SpreadsheetApp.getUi().showModelessDialog(html, 'Add a User');
}

// Inserting data from the form
function addLine(array) {
  SpreadsheetApp.getActiveSheet().appendRow(array);
}

As well as the complete HTML page for the form:

<!DOCTYPE html>
<html>
  <head>
    <style>
      body {
        padding: 0 0.5rem; /* replace with "margin: 0;" if displayed in a dialog box */
        color: #333;
        font-family: Roboto, Arial, sans-serif;
        overflow: hidden;
      }
      p {
        margin: 0.8rem 0 0.3rem;
      }
      .cancel {
        display: inline-block;
        margin-top: 1rem;
        font-size: 0.88rem;
        color: #888;
        cursor: pointer;
      }
      .cancel:hover {
        text-decoration: underline;
      }
      input[type="text"] {
        display: block;
        width: 100%;
        box-sizing: border-box;
        margin-bottom: 1rem;
        padding: 0.6rem 0.7rem;
        background: #f3f3f3;
        color: #444;
        border: none;
        font-size: 1.08rem;
        border-radius: 0.4rem;
      }
      input[type="button"] {
        display: block;
        width: 100%;
        padding: 0.7rem 0 0.6rem;
        border: none;
        background: #455fbb;
        color: #fff;
        font-size: 1.15rem;
        cursor: pointer;
        border-radius: 0.4rem;
      }
      input[type="button"]:hover {
        background: #5874d9;
      }
    </style>
  </head>
  <body>
    <p>Name</p>
    <input type="text" name="name" value="">
    <p>First Name</p>
    <input type="text" name="first_name" value="">
    <p>Address</p>
    <input type="text" name="address" value="">
    <p>Postal Code</p>
    <input type="text" name="postal_code" value="">
    <p>City</p>
    <input type="text" name="city" value="">
    <input type="button" value="Add" onclick="add()">
    <span class="cancel" onclick="google.script.host.close()">Cancel</span>
    <script>
      function add() {
        const inputs = document.querySelectorAll('input[type="text"]');
        let array = [];

        // Retrieving values
        for (const input of inputs) {
          array.push(input.value);
        }

        // If all fields are empty
        if (array.join('') == '') {
          alert('The form is empty!');
          return;
        }

        // Clear fields
        inputs.forEach(input => input.value = '');

        // Sending to the sheet
        google.script.run.addLine(array);
      }
    </script>
  </body>
</html>

CSS

To better understand the CSS style rules, here are some additional comments:

body {
  padding: 0 0.5rem; /* inner margins */
  color: #333; /* text color */
  font-family: Roboto, Arial, sans-serif; /* font */
  overflow: hidden; /* hides the vertical scrollbar in dialog boxes */
}
p {
  margin: 0.8rem 0 0.3rem; /* margins */
}
.cancel {
  display: inline-block; 
  margin-top: 1rem; /* top margin */
  font-size: 0.88rem; /* text size */
  color: #888; /* text color */
  cursor: pointer; /* hand cursor */
}
.cancel:hover { /* hover = on mouseover */
  text-decoration: underline; /* underlined text */
}
input[type="text"] {
  display: block;
  width: 100%; /* width at 100% */
  box-sizing: border-box; /* accounting for margins in width calculation */
  margin-bottom: 1rem; /* bottom margin */
  padding: 0.6rem 0.7rem; /* inner margins */
  background: #f3f3f3; /* background color */
  color: #444; /* text color */
  border: none; /* no borders */
  font-size: 1.08rem; /* text size */
  border-radius: 0.4rem; /* rounded corners */
}
input[type="button"] {
  display: block;
  width: 100%; /* width at 100% */
  padding: 0.7rem 0 0.6rem; /* inner margins */
  border: none; /* no borders */
  background: #455fbb; /* background color */
  color: #fff; /* text color */
  font-size: 1.15rem; /* text size */
  cursor: pointer; /* hand cursor */
  border-radius: 0.4rem; /* rounded corners */
}
input[type="button"]:hover { /* hover = on mouseover */
  background: #5874d9; /* background color */
}