Modal dialog doesn't write back to sheet, when google.script.host.close() is used

Question

I have a very simple Google Apps Script which uses showModalDialog() to open an HTML form over a spreadsheet. The popup tries to send an input value back to the sheet and then close itself. The problem is sometimes the popup's POST request (called using google.script.run()) is cancelled and the sheet is not updated.

When I remove the call to google.script.host.close() the request completes successfully and the sheet is updated. EDIT: Arrgh, I swear this used to work, but now I can't get the data written back to the sheet under any circumstances. Removing the close() does allow the callback to be sent successfully, though.

I've tried using async/await or a success handler (see bottom of question) but neither have worked. How do I make the popup self-closing after the data is successfully sent?

Here's the server-side code:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Test Menu')
      .addItem('Open Test Popup', 'showPopup')
      .addToUi();
  return false;
}

function showPopup(){
  var html = HtmlService.createTemplateFromFile('Popup').evaluate().setWidth(400).setHeight(350);
  SpreadsheetApp.getUi().showModalDialog(html, 'Test Title');
  return false;
}

function writeCustomValue(valueToWrite) {
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var cell = sheet.getCurrentCell();
  cell.setValue(valueToWrite);
  return false;
}

Here's the popup client-side code:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
  </head>
  <body>    
   
    <div class="block form-group">
      <label for="valueToWrite">Write this value</label>
      <input type="text" id="valueToWrite">
    </div>
    <button id="writeResponse" onclick='writeResponse()' class="action block">Write this value</button>
        
<script>
function writeResponse(){
   document.getElementById('writeResponse').innerHTML = 'Saving ...';
   document.getElementById('writeResponse').disabled = true;
   //Get the value of the input field 
   var valueToWrite = document.getElementById('valueToWrite').value;
   //Send the value of the text field as an argument to the server side function.
   google.script.run.writeCustomValue(valueToWrite);
   google.script.host.close();
}

</script>

  </body>
</html>

Here's what I've tried:

Putting host.close() in a success handler:

google.script.run.withSuccessHandler(google.script.host.close()).writeCustomValue(valueToWrite);

Async/await:

async function writeResponse(){
   document.getElementById('writeResponse').innerHTML = 'Saving ...';
   document.getElementById('writeResponse').disabled = true;
   //Get the value of the input field 
   var valueToWrite = document.getElementById('valueToWrite').value;
   //Send the value of the text field as an argument to the server side function.
   await google.script.run.writeCustomValue(valueToWrite);
   google.script.host.close();
}

For me your script works without issues. Try to close all instances of the spreadsheet and open again, refresh browser session, change browser, sing out of all accounts but one, use incognito. — ziganotschka, Aug 26 '20 at 10:53
Don't put parentheses on `withSuccessHandler(google.script.host.close())`. That is equivalent to `withSuccessHandler(undefined)` (the return value of the function). Pass just the function name: `withSuccessHandler(google.script.host.close)` — dwmorrin, Aug 26 '20 at 11:03
Thanks @ziganotschka after clearing cache and logging out and back in, things started working as expected. It seems Google Apps Scripts don't properly handle multiple Google accounts being logged in at once. — Martin Burch, Aug 26 '20 at 14:56
@MartinBurch - oh yes, multiple accounts being logged in at once have been a bane of GAS for a long time, some issues are still not fixed :) — Oleg Valter is with Ukraine, Aug 27 '20 at 03:11

dwmorrin · Accepted Answer · 2020-08-26T12:17:41.133

google.script.run.withSuccessHandler takes a function as input, which means you need to pass in the name of the function without parentheses. Adding the parentheses passes in the return value of the function, not the function definition.

You wrote

google.script.run.withSuccessHandler(google.script.host.close()).writeCustomValue(valueToWrite);

when you meant

google.script.run.withSuccessHandler(google.script.host.close).writeCustomValue(valueToWrite);

Your code runs as expected in my Sheet after updating writeResponse accordingly.

As for the async/await, google.script.run.writeCustomValue() does not return a Promise (it's return type is undefined) and is therefore not something you can await. Just adding that keyword does not change the definition of google.script.run or the objects therein. When run, you await nothing and the script *immediately moves onto the next line just as if await were not there.

(* perhaps "immediately" is the wrong word: the value of the expression (undefined) following await is converted into a resolved Promise; I leave it as an exercise to the reader to figure out exactly when it will run, but I believe the effect in this situation is that it is run "immediately" in a loose sense.)

This works well, but is a little slow. I imagine it's because there are now two consecutive round-trips to the server. It's too bad the window can't close itself client side. However, many thanks! — Martin Burch, Aug 26 '20 at 17:31
Well, it will be run depending on how many macrotasks are left before the enqueued microtask will be run. Technically, if this is the only task left, for all intents and purposes it can be considered "immediate", just not "instant". Also, @MartinBurch - see this Q&A for examples on converting callback-based `google.script` APIs to promise-based APIs: https://stackoverflow.com/q/63531403/11407695 — Oleg Valter is with Ukraine, Aug 27 '20 at 03:09

Modal dialog doesn't write back to sheet, when google.script.host.close() is used

1 Answers1

Linked