Auto close modal dialog - After server code is done, close dialog in Google Spreadsheet

Question

I have a Google Sheet that runs some Apps Script server code to connect to an SQL server. I want to show the message "loading..." in the modal dialog while data is being refreshed. I can get the modal to pop up, but I want to auto-close the dialog as soon as the code is finished.

An example I have set up is:

function testpop () {
  var htmlOutput = HtmlService
    .createHtmlOutput('<p> This box will close when the data has finished loading.</p>')
    .setSandboxMode(HtmlService.SandboxMode.IFRAME)
    .setWidth(250)
    .setHeight(200);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Loading...');
  sleep(1000);
//close the dialog
}

I know this can be called on a client side but need it to be handled in the GS so it fires when the code is done.

score 19 · Accepted Answer · answered Sep 11 '15 at 17:00

The flow of events could be:

User does something
Triggers modal dialog
onLoad event of modal dialog triggers client side code
Client side google.script.run triggers a server side .gs function to run
Server function in .gs script file runs.
database updated from server.
server code sends a return value back to dialog
"withSuccessHandler()" in dialog detects the return from the server
"withSuccessHandler()" runs and closes the dialog using google.script.host.close();

You'll need a <script> tag in your modal dialog.

<script>
  window.onload = function() {    
    //console.log('window.onload ran!');

    google.script.run
      .withSuccessHandler(closeDialog)
      .theFunctionNameToUpdateDatabase()
  };

  window.closeDialog = function() {
    google.script.host.close();
  };
</script>

Right now you are using:

HtmlService.createHtmlOutput(the HTML here)

You could create the HTML from a file instead:

HtmlService.createHtmlOutputFromFile(filename)

score 17 · Answer 2 · answered Jan 29 '18 at 19:05

17

//show dialog

var output = HtmlService.createHtmlOutput('<b>Please wait...</b>');
  SpreadsheetApp.getUi().showModalDialog(output, 'Saving...');

some code

//close dialog

  var output = HtmlService.createHtmlOutput('<script>google.script.host.close();</script>');
  SpreadsheetApp.getUi().showModalDialog(output, 'Loading...');

*optional

  ui.alert("Saved Successfully!")

answered Jan 29 '18 at 19:05

pcw11211

221
3
4

3

I love this. I think its a cleaner solution. Thanks! – IMTheNachoMan Aug 03 '19 at 02:20
2

This is so much easier and robust than the (currently) accepted answer. All the points to you. – Engineer Toast Dec 20 '19 at 21:00
Excellent solution, even with gpt4, it can't give me a proper solution like this one :) – Subaru Spirit Jul 17 '23 at 10:18

Mark · Answer 3 · 2021-02-12T11:34:49.307

I too went with the answer given by @pcw11211. I dressed it up a little by encapsulating the htmlmodalDialog call in a function that can either open or close a dialog. I added a little html styling in there too. Also note the try/catch around the htmlmodalDialog that is there so that the scrip can still run when you are testing from the script editor, otherwise the main script is sometimes(?) halted with a Context error.

/**
* HELPER FUNCTION : TO OPEN & CLOSE MODAL DIALOGUE
*/
function htmlmodalDialog(title, text, close){
 var htmlText = '<div>' + text + '</div>';
 htmlText += '<style type="text/css">';
 htmlText += 'body{text-align: center; font-family: Roboto, Arial, sans-serif; font-size: 14px;}';
 htmlText += 'div{margin: auto;}';
 htmlText += '</style>';
 if(close){htmlText += '<script>google.script.host.close();</script>';}
 var htmlOutput = HtmlService
  .createHtmlOutput(htmlText)
  .setHeight(60)
  .setWidth(200);
try {
    SpreadsheetApp.getUi().showModalDialog(htmlOutput, title);
}catch(e){
    Logger.log('function htmlmodalDialog(title, text, close)');
    Logger.log(e);
}

}

The function is called from the main code as follows with the last argument being an open/close boolean.

htmlmodalDialog('Starting', 'Working ...', false);
    
//do something here

htmlmodalDialog('Finished', 'Operation completed.', true);

Hope this helps somebody.

James VB · Answer 4 · 2021-02-20T19:19:04.000

This solution provides a button to close the dialog immediately and will close automatically after 5000 milliseconds (5 sec.) by using "onload="setTimeout(closer, 5000)" in the body tag. It will pass a google script variable to the HTML to customize the dialog box.

Here's what I came up with...

My GS File:

function myGSFunction(inputVariable){  
  const ui = SpreadsheetApp.getUi();
  let gsVariable = inputVariable.toUpperCase().trim() || null;
  let htmlOutput;

        .
        .
        .
  htmlOutput = HtmlService.createTemplateFromFile('errorModelessDialog');
  htmlOutput.htmlVariable = gsVariable;  //define variable accessible in html
  ui.showModelessDialog(htmlOutput.evaluate().setWidth(400).setHeight(150), 'ERROR');
    .
    .
    .
};

My errorModelessDialog.html File:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body onload="setTimeout(closer, 5000)">
    <p align="center"><?!=htmlVariable?> is not valid.</p>
    <p align="center"><button onclick="google.script.host.close()">Close</button></p>
    <script>
      function closer(){
        google.script.host.close();
      }
    </script>  
  </body>
</html>

Auto close modal dialog - After server code is done, close dialog in Google Spreadsheet

4 Answers4

Linked