function won't run when executed from htmlService?

Question

Im trying to switch my current sheet after 5s of the htmlService modalDialog opening. For some reason SwitchToSheet1() does not work...

.gs

function openModal() {
  var ui = SpreadsheetApp.getUi();
  var html = HtmlService.createTemplateFromFile('BarcodeLoadingHTML')
  .evaluate()
  .setWidth(400)
  .setHeight(250);
  ui.showModalDialog(html, "‎");
}

function SwitchToSheet1() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Bestandsliste'), true);
};

BarcodeLoadingHTML.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <?!= include('jQuery'); ?>
    <?!= include('Stylesheet'); ?>
    <?!= include('lottieplayer'); ?>
  </head>
  <body>
    <div id="loading">
        <lottie-player id="lottie-load" src="https://static.staticsave.com/lottie/cloudupload.json"  
          background="transparent"  
          speed="1" 
          loop 
          autoplay>
        </lottie-player>
        <lottie-player id="lottie-success" src="https://assets3.lottiefiles.com/private_files/lf30_qXYuJE.json"  
          background="transparent"  
          speed="1">
        </lottie-player>
        <p class="loadingtext" id="text">Bitte warten<span>.</span><span>.</span><span>.</span></p>
    </div>
    <script>
        $(window).ready(setTimeout (function() {
            $('#lottie-load').hide()
            $('#text').text('Upload Erfolgreich!')
            $('#lottie-success').fadeIn()
            $('#lottie-success').get(0).play();
        }, 2500));
        
        $(window).ready(setTimeout (function() {
            google.script.run.SwitchToSheet1();
            google.script.host.close();
        }, 5000));

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

@TheMaster removing google.script.host.close(); does nothing besides the dialog not closing anymore — Rookie_Js, Sep 13 '22 at 15:53
Provide modal dialog code. Need [mcve] to reproduce. Provide the minimal code needed to reproduce the issue — TheMaster, Sep 13 '22 at 15:54
I'm guessing that `setActive()` won't work from that context. — TheMaster, Sep 13 '22 at 15:56
@TheMaster no err in the execution logs but i got this in chrome: " There was an error during the transport or process…this request. Error code = 10, Path = /wardeninit', name: 'TransportError', " — Rookie_Js, Sep 13 '22 at 16:06
If the answer you marked was correct, shouldn't my first suggestion of removing `host.close()` worked? Did you guess or did you actually try my suggestion? — TheMaster, Sep 13 '22 at 17:24
i did try it, nothing happened idk if there was a something else going wrong but rubens solution worked. — Rookie_Js, Sep 13 '22 at 17:31

Rubén · Accepted Answer · 2022-09-13T17:01:37.270

Replace

google.script.run.SwitchToSheet1();
google.script.host.close();

by

google.script.run
.withSuccessHandler(() => google.script.host.close())
.SwitchToSheet1();

The above because google.script.host.close() is executing before google.script.run.SwitchToSheet1(). This happens because calls to server code using google.script.run are asynchronous, in other words, the Google servers not all the time responds instantly when they are called by run, so google.script retry until the server responds, but if the dialog is closed, there aren't more attempts to call the server.

score -2 · Answer 2 · answered Sep 13 '22 at 16:30

-2

FWIW, assuming I have a spreadsheet with two sheets named Sheet1 and Sheet2, and Sheet2 is the active, the following code works for me:

// Code.gs
function showDialog() {
  const html = HtmlService.createHtmlOutputFromFile('page')
   .setWidth(400)
   .setHeight(400);
  SpreadsheetApp.getUi().showModalDialog(html, 'My custom dialog'); 
}

function SwitchToSheet1() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sh = spreadsheet.getSheetByName('Sheet1');
  spreadsheet.setActiveSheet(sh, true);
};

And the HTML page:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>            
    <script>
      window.addEventListener('DOMContentLoaded', () => {
        setTimeout (function() {
            google.script.run.SwitchToSheet1();
            google.script.host.close();
        }, 5000)
      })
    </script>
  </body>
</html>

answered Sep 13 '22 at 16:30

Ben

213
2
11

1

This might not work all the time (see my [answer](https://stackoverflow.com/a/73706218/1595451)). – Rubén Sep 13 '22 at 16:31
@Rubén I realize that google.script.run is async; I just don't know if it's a relevant issue here. I think that because .run comes before .host, it will always be invoked. It might be executed on the server after the modal closes but that shouldn't hinder its execution. Do you see it differently? – Ben Sep 13 '22 at 16:46
sometimes `close` might run before `run` is called. When this happens the call to the server will not be done. – Rubén Sep 13 '22 at 16:52
I think that the server not all the time responds instantly when it's called by `run`, so google.script retry until the server responds, but if the dialog is closed, there aren't more attempts to call the server. – Rubén Sep 13 '22 at 17:00
My understanding is that the server may not respond instantly, but I it will never ignore a .run command. It will eventually execute it. Can you please point me to documentation that shows how the client makes multiple .run calls until it gets a response? – Ben Sep 13 '22 at 17:58
1

While no one at Google had documented this, it is known by testing. If you're unable to reproduce this, try different browsers. `close()` is a client side close and is probably faster than the `run` request to server, which may stop midway(say `POST` fails midway). – TheMaster Sep 13 '22 at 21:57
@Ben I think that it might be possible "documented" by someone (Google I/O speakers, IEEE paper authors, bloggers, etc.) but I can't give you a link at this time. I suggest you to spend some time learning about profiling web applications, more specifically about tools and methods to measure client-server communication performance. – Rubén Sep 14 '22 at 12:58

function won't run when executed from htmlService?

2 Answers2

Linked

Related