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:
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:
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:
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:
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 */
}