Custom dialog in Google Sheets

A custom dialog is a UI widget that is displayed over the Google Sheet. Unlike alerts and prompts, you can build your own user interfaces within a custom dialog using HTML, CSS and Javascript.

There are two types of custom dialogs:

• Modal dialog: A modal dialog is displayed over the Google Sheets spreadsheet and prevents the user from interacting with the sheet until the dialog is closed.

• Modeless dialog: A modeless dialog is also displayed over the Google Sheets spreadsheet but it enables the user to interact with the sheet.

You can build custom dialogs using Apps Script. These modal dialogs enable you to build custom prompts and alerts to get contextual information from the user. In this tutorial, I will show you how to create custom dialogs in Google Sheets using Google Apps Script.

Prerequisites

This tutorial assumes that you're familiar with the following concepts:

• Basic coding concepts and the basics of coding using Google Apps Script

• Custom menus in Google Sheets

• Familiarity with basic HTML, CSS and JavaScript

• Familiarity with Google Forms

What will you learn in this tutorial?

In this tutorial, you will learn how to:

• Create a custom dialog in Google Sheets using Apps Script

• Setting the width and height of the custom dialog in Google Sheets

• Create a custom dialog in Google Sheets using a HTML file

• Use CSS and JavaScript to build custom user interfaces within a dialog in Google Sheets

• Close the custom dialog manually or programmatically using Apps Script

• Run Apps Script functions from the custom dialog

• Pass data from the custom dialog to your Apps Script functions

• Build data entry forms within custom dialogs in Google Sheets using Apps Script

• Embed a Google Form within a custom dialog in Google Sheets using Apps Script

Create a custom dialog in Google Sheets using Apps Script

To create a custom dialog, you need to write and run some Apps Script code. Custom dialogs are built using HTML, CSS and Javascript.

The user must authorize displaying a custom dialog since this step involves running custom third-party code (i.e., code that was not written by Google). Therefore, you cannot automatically display a custom dialog. Instead, you can only display them when users take some action like clicking on a custom button or selecting a menu item from a custom menu.

This is actually pretty simple to do. In this tutorial we will create a custom dialog that is displayed when users select a menu item from a custom menu.

Selecting the Send feedback menu item will display a custom dialog.

The code to create the above custom dialog is pretty simple. First, we use an onOpen trigger that will create a custom custom menu called Feedback. This menu has a single menu item called Send feedback. When users select this menu item, the function showFeedbackDialog() will be run and the custom dialog will be displayed. When you select the Send feedback menu item the first time, Google will ask you to authorize displaying custom widgets within Google Sheets. Once you authorize it, the dialog will be displayed (sometimes you may need to select the menu item once again for the dialog to be displayed).

//@OnlyCurrentDoc

function onOpen() {
 SpreadsheetApp
   .getUi()
   .createMenu("Feedback")
   .addItem("Send feedback", "showFeedbackDialog")
   .addToUi();
}

function showFeedbackDialog() {
 var widget = HtmlService.createHtmlOutput("<h1>Enter feedback</h1>");
 SpreadsheetApp.getUi().showModalDialog(widget, "Send feedback");
}

The showFeedbackDialog() function is also very simple.

The first line creates an HTML widget. The HTML in this example is very simple but you can use advanced HTML and CSS too.

var widget = HtmlService.createHtmlOutput("<h1>Enter feedback</h1>");

The second line displays the custom dialog with this widget. The title of the custom dialog is set to "Send feedback".

SpreadsheetApp.getUi().showModalDialog(widget, "Send feedback");

Setting the width and height of the custom dialog in Google Sheets

You can set a custom width and height for the custom dialog using the setWidth() and setHeight() methods on the HTML widget.

var widget = HtmlService.createHtmlOutput("<h1>Enter feedback</h1>");
widget.setWidth(100);
widget.setHeight(100);

The width and height parameters are integers and represent the desired width and height of the custom dialog in pixels.

//@OnlyCurrentDoc

function onOpen() {
  SpreadsheetApp
    .getUi()
    .createMenu("Feedback")
    .addItem("Send feedback", "showFeedbackDialog")
    .addToUi();
}

function showFeedbackDialog() {
  var widget = HtmlService.createHtmlOutput("<h1>Enter feedback</h1>");
  widget.setWidth(100);
  widget.setHeight(100);
  SpreadsheetApp.getUi().showModalDialog(widget, "Send feedback");
}

Create a custom dialog in Google Sheets using a HTML file

While you can create custom dialogs using the HtmlService.createHtmlOutput() function, it can be cumbersome to do so especially if your HTML code isn't concise.

A better way to do this is by creating a HTML file in the Apps Script editor and then using this file to create the dialog.

The first step is to create a HTML file. I'm going to name the file Sendfeedback.

Then add the following HTML code within the <body> section of the HTML.

<input type="button" value="Submit feedback" onclick="alert(Feedback submitted);">

The above HTML code adds a button that displays a JavaScript alert message when it is clicked.

When you're done, the HTML file Sendfeedback.html should contain the following code:

<!DOCTYPE html>
<html>
 <head>
   <base target="_top">
 </head>
 <body>
   <input type="button" value="Submit feedback" onclick="alert(Feedback submitted);">
 </body>
</html>

Next, you need to use this HTML file when you create the HTML widget for your custom dialog. In the showFeedbackDialog() function, replace:

var widget = HtmlService.createHtmlOutput("<h1>Enter feedback</h1>");

with:

var widget = HtmlService.createHtmlOutputFromFile("Sendfeedback.html");

When you're done making changes, the full Apps Script code should look like:

//@OnlyCurrentDoc

function onOpen() {
  SpreadsheetApp
    .getUi()
    .createMenu("Feedback")
    .addItem("Send feedback", "showFeedbackDialog")
    .addToUi();
}

function showFeedbackDialog() {
  var widget = HtmlService.createHtmlOutputFromFile("Sendfeedback.html");
  SpreadsheetApp.getUi().showModalDialog(widget, "Send feedback");
}

Now close and reopen the dialog by selecting Feedback —> Send feedback. You should see the [Submit feedback] button.

When you click the button, an alert message will appear.

Use CSS and JavaScript to build custom user interfaces within a dialog in Google Sheets

Since custom dialogs in Google Sheets can be created using a regular HTML file, you can use standard web technologies like JavaScript and CSS to build rich dialog widgets.

Here is an example of how to use JavaScript and CSS in the HTML code for a custom dialog:

<!DOCTYPE html>
<html>
 <head>
   <base target="_top">
   <script>
     function submitFeedback() {
       alert('Your feedback was submitted successfully.');
     }
   </script>
   <style>
     .button {
       background-color: blue;
       color: white;
       padding: 10px;
       border: none;
       border-radius: 5px;
     }
   </style>
 </head>
 <body>
   <h1>Enter feedback</h1>
   <p>
     <input type="button" class="button" value="Submit feedback" onclick="submitFeedback();">
   </p>
   <hr>
 </body>
</html>

In the above HTML, we are using CSS to style the button and a JavaScript function called submitFeedback() that is called whenever users click the [Submit feedback] button on the dialog.

Close the custom dialog manually or programmatically using Apps Script

There are two ways to close a custom dialog.

• The user can manually close the dialog by clicking X.

• The dialog can close itself programmatically.

To close the sidebar programmatically, use the google.script.host.close() JavaScript method from the dialog's HTML code.

<!DOCTYPE html>
<html>
 <head>
   <base target="_top">
 </head>
 <body>
   <input type="button" class="button" value="Close" onclick="google.script.host.close();">
 </body>
</html>

Run Apps Script functions from the custom dialog

It is also possible to run other Apps Script functions from the custom dialog by using the google.script.run API from the dialog's HTML file.

As an example, let us create a simple Apps Script function called displayToast() that will display a toast message.

function displayToast() {
  SpreadsheetApp.getActive().toast("Hi there!");
}

Then, call this function from the dialog's HTML code using JavaScript. Notice that the onclick attribute of the button calls google.script.run.displayToast().

<!DOCTYPE html>
<html>
 <head>
   <base target="_top">
 </head>
 <body>
   <input type="button" class="button" value="Display toast" onclick="google.script.run.displayToast();">
 </body>
</html>

Pass data from the custom dialog to your Apps Script functions

You can also pass data from the custom dialog to an Apps Script function. Below is an example of how to do this. The HTML file has a text box where you can enter your name and this value is passed to the displayToast() Apps Script function that displays a Toast message using the name that you entered.

Here is the code for the HTML file:

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
<script>
 function displayToast() {
   var name = document.getElementById('name').value;
   google.script.run.displayToast(name);
 }
</script>
<body>
  What is your name?
  <input type="text" id="name">
  <br>
  <input type="button" class="button" value="Display toast" onclick="displayToast();">
</body>
</html>

The displayToast() JavaScript function in the above HTML file runs the displayToast() Apps Script function (below) and passes the name value to it. The Apps Script function then displays a Toast message with this name.

function displayToast(name) {
 SpreadsheetApp.getActive().toast("Hi " + name + "!");
}

Build data entry forms within custom dialogs in Google Sheets using Apps Script

You can also build richer HTML pages within a custom dialog. Let us build a simple feedback form using HTML and Javascript. When users submit feedback, this information will be stored in the spreadsheet.

Below is the HTML code for a simple form that lets users enter feedback. The form has two text fields for name and feedback and a radio button group for a satisfaction rating

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <script>
    function submitForm() {
      google.script.run.appendRowFromFormSubmit(document.getElementById("feedbackForm"));
      document.getElementById("form").style.display = "none";
      document.getElementById("thanks").style.display = "block";
    }
  </script>
</head>
<body>
  <div>
  <div id="form">
  <h1>Enter feedback</h1>
  <form id="feedbackForm">
    <label for="name">Name</label>
    <input type="text" id="name" name="name"><br><br>
    <label for="feedback">Feedback</label>
    <input type="text" id="feedback" name="feedback"><br><br>
    <div>
      <label for="rating">Are you satisfied?</label><br>
      <input type="radio" id="yes" name="rating" value="Yes">
      <label for="yes">Yes</label><br>
      <input type="radio" id="no" name="rating" value="No">
      <label for="no">No</label><br><br>
      <input type="button" value="Submit feedback" onclick="submitForm();">
  </form>
  </div>
  </div>
  <div id="thanks" style="display: none;">
    <p>Thank you for sumbitting feedback!</p>
  </div>
</body>
</html>

When you click [Submit feedback] the JavaScript function submitForm() is called and this in turn runs the Apps Script function appendRowFromFormSubmit() and passes the form object to it.

The appendRowFromFormSubmit() then appends a row to the spreadsheet with the information entered in the form.

function appendRowFromFormSubmit(form) {
 var row = [form.name, form.feedback, form.rating];
 SpreadsheetApp.getActiveSheet().appendRow(row);
}

The full Apps Script code for this example is:

//@OnlyCurrentDoc

function onOpen() {
  SpreadsheetApp
    .getUi()
    .createMenu("Feedback")
    .addItem("Send feedback", "showFeedbackDialog")
    .addToUi();
}

function showFeedbackDialog() {
  var widget = HtmlService.createHtmlOutputFromFile("Form.html");
  SpreadsheetApp.getUi().showModalDialog(widget, "Send feedback");
}

function appendRowFromFormSubmit(form) {
  var row = [form.name, form.feedback, form.rating];
  SpreadsheetApp.getActiveSheet().appendRow(row);
}

Here is a video that shows the custom form in a dialog in Google Sheets.

Embed a Google Form within a custom dialog in Google Sheets using Apps Script

Instead of building a data entry form using HTML, CSS and JavaScript, you can simply create a Google Form and embed it in a custom dialog.

First, you need to embed the Google Form in your HTML template:

<!DOCTYPE html>
<html>
 <head>
   <base target="_top">
 </head>
 <body>
   <iframe src="https://docs.google.com/forms/d/e/<your-form-id>/viewform?embedded=true" width="400" height="500" frameborder="0" marginheight="0" marginwidth="0">Loading…</iframe>
 </body>
</html>

The Apps Script code then just displays this HTML file.

//@OnlyCurrentDoc

function onOpen() {
  SpreadsheetApp
    .getUi()
    .createMenu("Feedback")
    .addItem("Send feedback", "showFeedbackDialog")
    .addToUi();
}

function showFeedbackDialog() {
  var widget = HtmlService.createHtmlOutputFromFile("Form.html");
  widget.setWidth(400);
  widget.setHeight(500);
  SpreadsheetApp.getUi().showModalDialog(widget, "Send feedback");
}

Below is a video of a Google Form embedded in a custom dialog.

Conclusion

Custom dialogs enable you to build custom user interfaces in Google Sheets using Apps Script. In this tutorial you learned how to create and use custom dialogs in Google Sheets. Specifically, you learned how to:

• Create a custom dialog in Google Sheets using Apps Script

• Setting the width and height of the custom dialog in Google Sheets

• Create a custom dialog in Google Sheets using a HTML file

• Use CSS and JavaScript to build custom user interfaces within a dialog in Google Sheets

• Close the custom dialog manually or programmatically using Apps Script

• Run Apps Script functions from the custom dialog

• Pass data from the custom dialog to your Apps Script functions

• Build data entry forms within custom dialogs in Google Sheets using Apps Script

• Embed a Google Form within a custom dialog in Google Sheets using Apps Script

Thanks for reading!