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)? — Wicket
– Wicket, Commented Jul 6, 2020 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
– Patrick Lollis, Commented Jul 6, 2020 at 20:07

Wicket · Accepted Answer · 2020-07-06 20:55:56Z

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 6, 2020 at 20:55

answered Jul 6, 2020 at 20:12

Wicket

39.4k9 gold badges81 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Patrick Lollis Over a year ago

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. :-(

Wicket Over a year ago

@PatrickLollis Have you tried using Chrome in incognito mode with all the extensions disabled? How about using the old runtime instead of the new?

Patrick Lollis Over a year ago

I tried this, but it didn't work.

<script>     function actionAddEgg(){     google.script.run.withSuccessHandler(google.script.host.close).addEgg();     }     </script>

Wicket Over a year ago

That code has errors. Have you reviewed the link that I included in my answer?

Patrick Lollis Over a year ago

It worked in incognito mode! (the version with the script in the HTML)

|

TheMaster · Accepted Answer · 2020-07-06 20:54:13Z

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 6, 2020 at 20:54

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related