How do I run Server-side functions using HtmlService

1
votes

I am new to programming and I am trying to wire up a couple of buttons with jQuery using Google-apps-script. I have a spread sheet and a menu added to it the opens a dialog box from HtmlService.

In the dialog box I have two buttons, one closes the dialog the other executes a server function, which for now only writes "hello world to cell a1. The "close" button works perfectly, however the "update" doesn't seem to do anything. I'm not exactly sure how to debug the client-side.

<script>
  $(document).ready(function(){

      $("#update").click(function (){
        var params = {}
        params.url = $("#url").val()
        params.owner = $("#owner").val()
        params.type = type
        google.script.run.update(params);
      });

      $("#close").click(function(){
      // This one works. why not the "update" button???
        google.script.host.close()
      })

  })

 </script>
 <title>AJAXtabs.html</title>
  </head>
<body>
<div id="content">
  <table border="1">
    <tr>
      <th><?= type ?>URL</th>
      <td><input type="text" id="url" name="url"></td>
    </tr>
    <tr>
      <th>New Owner email</th>
      <td><input type="text" id="ownerEmail" name="ownerEmail"></td>
    </tr>
    <tr>
      <td colspan="2" id="buttonRow" ><button id="update" type="button" >Update</button><button id="close" type="button">Close</button></td>
    </tr>
  </table>
</div>
<div id="message">
</div>
</body>
</html>

Code.gs excerpt

function update(params){
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
      var row = sheet.getLastRow()
      var col = sheet.getLastColumn()
      sheet.getRange('a1').setValue('Hello world!!')
    }
    function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [];
   // When the user clicks on "addMenuExample" then "Menu Entry 1", the function function1 is
   // executed.
   menuEntries.push({name: "Set file", functionName: "fileUi"});
  menuEntries.push(null); // line separator
  menuEntries.push({name: "Set Folder", functionName: "folderUi"});

  ss.addMenu("Setters", menuEntries);
}

   function fileUi(){
 var htmlApp = HtmlService.createTemplateFromFile('View template')
 htmlApp.type = 'File';

   SpreadsheetApp.getActiveSpreadsheet().show(htmlApp.evaluate().setHeight(300).setTitle('Chan ge Owner'));
 }

   function folderUi(){
 var htmlApp = HtmlService.createTemplateFromFile('View template')
  htmlApp.type = 'Folder'

   SpreadsheetApp.getActiveSpreadsheet().show(htmlApp.evaluate());
}
1
javascriptjqueryhtmlgoogle-apps-scriptgoogle-sheets
Can you share the code for your .gs file where you implement update – Arun Nagarajan

1 Answers

3
votes

Below are modified versions of your html and gs files, in which both buttons work. I believe that the only thing that needed to change was the inclusion of the jQuery library.

Debugging

Generally speaking, the best place to debug your client-side functions is in the debugger / IDE, using the techniques appropriate there. You may find some ideas that help you in this tutorial, and these answers:

To support debugging, this script relies on Peter Herrmann's BetterLog library. You will need to add that to your project, by "Resources - Manage Libraries...". With it, plus the helper function included below, you will have an effective way to log operations of both your client and server side functions. (Since you're using a spreadsheet already, you can log to it... the utility will create a new tab.)

The additional use of BetterLog gives you a way to trace execution across multiple platforms or environments, with better history keeping than the built-in Logger. This example is barely scratching the surface of what that utility does - but it's enough for most purposes!

Various log messages have been left in place, to illustrate.

Example Logs

2013-07-31 00:02:17:332 -0400 000128 INFO in ready
2013-07-31 00:02:17:419 -0400 000094 INFO In html script
2013-07-31 00:02:23:508 -0400 000178 INFO in update.click
2013-07-31 00:02:24:081 -0400 000163 INFO in update (server)
2013-07-31 00:02:24:104 -0400 000186 INFO {"url":"adsfasdfsad","owner":null,"type":null}
2013-07-31 00:02:24:166 -0400 000248 INFO done update (server)
2013-07-31 00:03:14:355 -0400 000248 INFO in close.click

Code.gs

Logger = BetterLog.useSpreadsheet('--Spreadsheet-ID--');

/**
 * Make BetterLogger available to client-side scripts, via
 * google.script.run.log(string).
 */
function log(string) {
  Logger.log(string);
}

function update(params){
  Logger.log('in update (server)');
  Logger.log(JSON.stringify(params));
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var row = sheet.getLastRow()
  var col = sheet.getLastColumn()
  sheet.getRange('a1').setValue('Hello world!!')
  Logger.log('done update (server)');
}


function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  // When the user clicks on "addMenuExample" then "Menu Entry 1", the function function1 is
  // executed.
  menuEntries.push({
    name: "Set file",
    functionName: "fileUi"
  });
  menuEntries.push(null); // line separator
  menuEntries.push({
    name: "Set Folder",
    functionName: "folderUi"
  });

  ss.addMenu("Setters", menuEntries);
}

function fileUi() {
  var htmlApp = HtmlService.createTemplateFromFile('View template')
  htmlApp.type = 'File';
  var html = htmlApp.evaluate()
                    .setSandboxMode(HtmlService.SandboxMode.NATIVE)
                    .setHeight(300)
                    .setTitle('Change Owner');
  SpreadsheetApp.getActiveSpreadsheet().show(html);
}

function folderUi() {
  var htmlApp = HtmlService.createTemplateFromFile('View template')
  htmlApp.type = 'Folder'
  var html = htmlApp.evaluate()
                    .setSandboxMode(HtmlService.SandboxMode.NATIVE)
                    .setHeight(300)
                    .setTitle('Change Owner');
  SpreadsheetApp.getActiveSpreadsheet().show(html);
}

View template.html

This has been restructured as per the best practices, and of course log messages are included.

<div id="content">
  <table border="1">
    <tr>
      <th><?= type ?>URL</th>
      <td><input type="text" id="url" name="url"></td>
    </tr>
    <tr>
      <th>New Owner email</th>
      <td><input type="text" id="ownerEmail" name="ownerEmail"></td>
    </tr>
    <tr>
      <td colspan="2" id="buttonRow" >
      <button id="update" type="button" >Update</button>
      <button id="close" type="button">Close</button>
      </td>
    </tr>
  </table>
</div>
<div id="message">
</div>

<script src="http://code.jquery.com/jquery-1.9.1.js"></script>
<script>
  google.script.run.log("In html script");
  $(document).ready(function(){
    google.script.run.log("in ready");

    $("#update").click(function (){
      google.script.run.log("in update.click");
      var params = {}
      params.url = $("#url").val()
      params.owner = $("#owner").val()
      params.type = type
      google.script.run.update(params);
    });

    $("#close").click(function(){
      google.script.run.log("in close.click");
      google.script.host.close()
    })
  })
</script>