HTML Dialog in Google Sheets google.script.run. fails to run Apps Script function

Question

I'm using Google Sheets for remote gaming with an elderly friend (with the game publisher's permission).

I am trying to use an HTML dialog with buttons to trigger some script. I've worked through all the examples I can find in the documentation and on Yagisanatode's blog. Every part of the thing works except that google.script.run.addEgg() isn't triggering the addEgg() function in Code.gs.

I'm including the relevant bits of code. I'm working on getting one button to trigger one script before building things out further.

EggFoodTuck01(), triggered by clicking an ObjectOverGrid, successfully launches the dialog

function EggFoodTuck01(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName('Decks'));
  sheet.getRange('CurrentCard').setValue(0); // Record Card Number in 'CurrentCard' Cell (0-14)
  sheet.getRange('CurrentSheet').setValue('Player 1'); // Record Sheet Name in 'CurrentSheet'
  
  // Launch dialog
  var html = HtmlService.createHtmlOutputFromFile("EFTDialog");
  ui.showModalDialog(html, "What would you like to do?");
}

The HTML dialog displays correctly. Clicking the [+ Egg] button launches actionAddEgg(), because the dialog closes. For whatever reason, addEgg() isn't running in my Apps Script.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
  </head>
  <body>
  <div>
    <br>
    <br>
    <input type="button" value="+ " class="share" onclick="addEgg()" />
    <br>
    <br>
    <input type="button" value="Cancel" class="action" onclick="google.script.host.close()" />
  </div>
    <script>
    function addEgg(){
    google.script.run.addEgg();
    google.script.host.close();
    }
    </script>
  </body>
</html>

Here's addEgg() in the Apps Script. It's not running at all. The 'card' variable won't even log.

function addEgg(){
  // Get card and sheet data
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName('Decks'));
  var card = sheet.getRange('CurrentCard').getValue();
  var sheetname = sheet.getRange('CurrentSheet').getValue();
  var sheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName(sheetname));
  
  // Get current egg value and +1
  var eggCell = ['F7','M7','T7','AA7','AH7','F16','M16','T16','AA16','AH16','F25','M25','T25','AA25','AH25'];
  var egg = sheet.getRange(eggCell[card]).getValue();
  sheet.getRange(eggCell[card]).setValue(egg + 1);
}

Have you defined a named ranges as `CurrentCard` and `CurrentSheet`? Are you sure that the corresponding getRange are returning a Class Range object instead of `null`? What runtime are you using, the new (V8) or the old (Rhino)? — Rubén, Jul 06 '20 at 19:58
I think so, yes. If I manually run the addEgg() function from the editor after the dialog box closes without launching it, the function performs correctly, and the egg number is incremented up. It's V8. — Patrick Lollis, Jul 06 '20 at 20:07

Rubén · Accepted Answer · 2020-07-06T20:55:56.013

2

You are missing that google.script.run is asynchronous. This means that google.script.host.close() might be executed before google.script.run.addEgg() call be effectively executed.

One option is that you use withSuccessHandler to call google.script.host.close()

Other thing that you are missing is that google.script.run will be executed with default account used to sign-in into Google services. One quick solution is to share your spreadsheet with you default account.

HTML Service Submit Form not calling google.script.run function

edited Jul 06 '20 at 20:55

answered Jul 06 '20 at 20:12

Rubén

34,714
9
70
166

I tried messing with that, but didn't really understand it. I do understand what you said might be happening, and why, so I'll go and read up on that some more. I took out the google.script.host.close() from the actionAddEgg() function, to see if the addEdd() function would run without it. I waited about 20 seconds, and then manually closed the dialog. No dice. :-( – Patrick Lollis Jul 06 '20 at 20:15
@PatrickLollis Have you tried using Chrome in incognito mode with all the extensions disabled? How about using the old runtime instead of the new? – Rubén Jul 06 '20 at 20:21
I tried this, but it didn't work. `````` – Patrick Lollis Jul 06 '20 at 20:22
That code has errors. Have you reviewed the link that I included in my answer? – Rubén Jul 06 '20 at 20:23
I tried code from the link `````` so that everything triggered directly from the button, rather than a script function in the HTML. I edited the code for my script to ``` ``` Still nothing. I will try incognito. – Patrick Lollis Jul 06 '20 at 20:39
2

It worked in incognito mode! (the version with the script in the HTML) – Patrick Lollis Jul 06 '20 at 20:48

score 0 · Answer 2 · answered Jul 06 '20 at 20:54

0

You shouldn't use the same function name(addEgg) for both server and client script functions. Consider changing one of the function's name.
Use close in the successHandler as google.script.run is asynchronous. This is mentioned in this answer

answered Jul 06 '20 at 20:54

TheMaster

45,448
6
62
85

HTML Dialog in Google Sheets google.script.run. fails to run Apps Script function

2 Answers2

Linked