Apps Script Course: Custom Windows

It is possible to create custom windows, displayed in the center (like dialog boxes) or all the way to the right of the Google Sheets window (sidebar).

For example, for a form (with good formatting), you need to create an HTML page, create the page style in CSS, create client-side JavaScript functions, create server-side Apps Script functions, and ensure that everything communicates well together.

The goal here, however, is not to do something complex, on the contrary, the project will be minimalist and without formatting to get to the essentials and give you a simple and functional example.


HTML File

For this example, the goal will be to create a small form containing 2 fields, with a button to insert the form data at the end on a sheet.

The first step is to add an HTML file from the editor (which you can name "Form"):

google sheets apps script add html file custom windows

Then the basic content of this page:

<!DOCTYPE html>
<html>
  <head>
  </head>
  <body>

    <script>
    </script>
  </body>
</html>

These 3 lines are then added (just after the opening of the body tag) to create a very basic form with 2 fields and a button:

<p>Name: <input type="text" id="name"></p>
<p>First Name: <input type="text" id="firstName"></p>
<p><input type="button" value="Add"></p>

Displaying the Window

To get a first glimpse of this HTML page, add the following Apps Script code:

function showForm() {
  const html = HtmlService.createHtmlOutputFromFile('Form')
    .setWidth(300)
    .setHeight(115);
  SpreadsheetApp.getUi().showModalDialog(html, 'Add a User');
}

In this function, Form corresponds to the name of the HTML file to display, Add a User is the title, and setWidth / setHeight define the dimensions of the window.

You can now run the function to display the window:

google sheets apps script custom window windows

Client-Server Communication

Before you can send this data to the sheet, you need to retrieve the form data when the user clicks the button.

Start by adding onclick="add()" to execute the add function after a click:

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

Then create the add function between the script tags, which retrieves the values of the 2 fields here:

function add() {
  const name = document.getElementById("name").value;
  const firstName = document.getElementById("firstName").value;
}

Now you need to create another server-side function (so Apps Script) to receive this data and insert it at the end in a chosen sheet:

function addUser(array) {
  SpreadsheetApp.getActive().getSheetByName('Sheet1').appendRow(array);
}

And finally, you need to call the addUser function from the add function and pass it the data to be inserted on the sheet:

google.script.run.addUser([name, firstName]);

Complete Code

The code for this example for the HTML file:

<!DOCTYPE html>
<html>
  <head>
  </head>
  <body>
    <p>Name: <input type="text" id="name"></p>
    <p>First Name: <input type="text" id="firstName"></p>
    <p><input type="button" value="Add" onclick="add()"></p>
    <script>
      function add() {
        const name = document.getElementById("name").value;
        const firstName = document.getElementById("firstName").value;
        google.script.run.addUser([name, firstName]);
      }
    </script>
  </body>
</html>

The code for this example for the Apps Script file:

function showForm() {
  const html = HtmlService.createHtmlOutputFromFile('Form')
    .setWidth(300)
    .setHeight(115);
  SpreadsheetApp.getUi().showModalDialog(html, 'Add a User');
}

function addUser(array) {
  SpreadsheetApp.getActive().getSheetByName('Sheet1').appendRow(array);
}

Sidebar

If you prefer a sidebar to a centered window, replace the showForm function with this one :

function showForm() {
  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile('Form').setTitle('Add a User'));
}
google sheets apps script sidebar custom windows

Almost Unlimited Possibilities

This example is deliberately very minimalist, but know that it is also possible to create advanced management tools using this feature (and a lot of work):

google sheets apps script custom window advanced example windows