6

How can I create a popup in Google Sheets using script editor? I'd like to show a popup with a personalised notification.

It should start like this:

    function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menubuttons1 = [ {name: "exampleOption", functionName: "clear1"} ];
ss.addMenu("example", menubuttons1);
  • Hope this is what you mean 1999, can't be more specific. What do you mean with fluff, by the way? – Joris F. Jul 13 '15 at 19:26
  • It's about opening a new function from the menu. Opening that results in a couple of things, of of them I would like to be a popup. – Joris F. Jul 13 '15 at 19:38
  • @JorisF. What do you think about my answer? – Rubén Jul 23 '15 at 0:34
  • 2
    @Rubén I think it's great but I noticed you didn't mention .toast or .show, so I posted another answer about them. – user79865 Jul 23 '15 at 1:03
7

Dialogs and Sidebars in Google Apps

Scripts that are bound to Google Docs, Sheets, or Forms can display several types of user-interface elements — pre-built alerts and prompts, plus dialogs and sidebars that contain custom HTML service or UI service pages. Typically, these elements are opened from menu items. (Note that in Google Forms, user-interface elements are visible only to an editor who opens the form to modify it, not to a user who opens the form to respond.)

Alternatives

Basically there are two alternatives for pop ups triggered from a custom menu: Alerts dialogs and Custom dialogs

Alert dialogs

An alert is a pre-built dialog box that opens inside a Google Docs, Sheets, or Forms editor. It displays a message and an "OK" button; a title and alternative buttons are optional. It is similar to calling window.alert() in client-side JavaScript within a web browser.

Alerts suspend the server-side script while the dialog is open. The script will resume after the user closes the dialog, but JDBC connections will not persist across the suspension.

As shown in the example below, Google Docs, Forms, and Sheets all use the method Ui.alert(), which is available in three variants. To override the default "OK" button, pass a value from the Ui.ButtonSet enum as the buttons argument. To evaluate which button the user clicked, compare the return value for alert() to the Ui.Button enum.

function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .createMenu('Custom Menu')
      .addItem('Show alert', 'showAlert')
      .addToUi();
}

function showAlert() {
  var ui = SpreadsheetApp.getUi(); // Same variations.

  var result = ui.alert(
     'Please confirm',
     'Are you sure you want to continue?',
      ui.ButtonSet.YES_NO);

  // Process the user's response.
  if (result == ui.Button.YES) {
    // User clicked "Yes".
    ui.alert('Confirmation received.');
  } else {
    // User clicked "No" or X in the title bar.
    ui.alert('Permission denied.');
  }
}

Custom dialog

From [2] (follow the link in references to get access to the links in the quoted text)

The HTML service lets you serve web pages that can interact with server-side Apps Script functions. It is particularly useful for building web apps or adding custom user interfaces in Google Docs, Sheets, and Forms. You can even use it to generate the body of an email.

Create HTML files

To add an HTML file to your Apps Script project, open the Script Editor and choose File > New > Html File.

Within the HTML file, you can write most standard HTML, CSS, and client-side JavaScript. The page will be served as HTML5, although some advanced features of HTML5 are not available, as explained in Restrictions.

Your file can also include template scriptlets that are processed on the server before the page is sent to the user — similar to PHP — as explained in the section on templated HTML.

...

Serve HTML as a Google Docs, Sheets, or Forms user interface

The HTML service can display a dialog or sidebar in Google Docs, Sheets, or Forms if your script is container-bound to the file. (In Google Forms, custom user interfaces are only visible to an editor who opens the form to modify it, not to a user who opens the form to respond.)

Unlike a web app, a script that creates a user interface for a document, spreadsheet, or form does not need a doGet() function specifically, and you do not need to save a version of your script or deploy it. Instead, the function that opens the user interface must pass your HTML file as an HtmlOutput object to the showModalDialog()) or showSidebar() methods of the Ui object for the active document, form, or spreadsheet.

These examples include a few extra features for convenience: the onOpen() function creates a custom menu that makes it easy to open the interface, and the button in the HTML file invokes a special close() method of the google.script.host API to close the interface.

CODE.GS

// Use this code for Google Docs, Forms, or new Sheets.
function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .createMenu('Dialog')
      .addItem('Open', 'openDialog')
      .addToUi();
}

function openDialog() {
  var html = HtmlService.createHtmlOutputFromFile('Index')
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .showModalDialog(html, 'Dialog title');
}

INDEX.HTML

Hello, world!
<input type="button" value="Close"
  onclick="google.script.host.close()" />

References

Similar questions

Show cell value on click

7

The easiest way to show a notification in Google Sheets is to use the method toast of the Spreadsheet object. Here's a simple example:

function onOpen() {
  var message = 'The current time is ' + new Date().toString();
  var title = 'Welcome to Google Sheets';
  SpreadsheetApp.getActiveSpreadsheet().toast(message, title);
}

Upon a user opening the spreadsheet, this script shows this "toast" in bottom right.

popup

If you want to show something with more complex formatting, use the method show in combination with the HtmlService; the documentation page has an example.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.