Html Service Basics - Write Form Data To A Spreadsheet

Update 14-Sep-2015: See chapter 7 in  leanpub book This chapter has been fully re-written and expanded
As stated in the previous post, GUI Builder is being phased out by Google and Html Service is now the preferred method for building user interfaces for Google Apps Script.

An earlier blog entry entitled GUI Basics - Write Form Data To A Spreadsheet showed how to build a very simple GUI composed of just two text boxes and two buttons that allowed the user to enter values into the text boxes and then add them to the spreadsheet using a button click.  The code to produce the GUI and implement such trivial functionality was quite lengthy and required some detailed explanation. 

I have now started looking at Google Html Service in some detail and, as part of this learning process, I have used it to re-implement the GUI built in the earlier post.  My verdict so far: I really like it!

• There's a lot less Google Apps Script code.
• It uses HTML and feels very like plain old web development.
• The presentation in the HTML is separated from the Google Apps Script code that works with the spreadsheet.
• I'll be able to practice and use CSS and really cool libraries like JQuery.

Code To Build The GUI

Firstly, we need a file for the HTML. In the Script Editor, select the menu sequence File->New->Html file.  I named the file "index.html" (a conventional name for the default page in web development).

The file contains the following HTML and JavaScript code:
    <b>Add Row To Spreadsheet</b><br />

    <form>
    First name: <input id="firstname" name="firstName" type="text" />

    Last name: <input id="lastname" name="lastName" type="text" />

   <input onclick="formSubmit()" type="button" value="Add Row" />
   <input onclick="google.script.host.close()" type="button" value="Exit" />
   </form>
  <script type="text/javascript">
        function formSubmit() {
            google.script.run.getValuesFromForm(document.forms[0]);
        }
    </script>

Standard Google Apps Script is then written, here's the code to produce a GUI similar to the one constructed in the earlier posting:

function demoHtmlServices() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(),
      html = HtmlService.createHtmlOutputFromFile('index');
  ss.show(html);
}

//getValuesFromForm
function getValuesFromForm(form){
  var firstName = form.firstName,
      lastName = form.lastName,
      sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.appendRow([firstName, lastName]);
}

To see the data input form, execute the function  demoHtmlServices() and switch to the spreadsheet.  The following screenshot shows what is displayed

To see the form in action, add text values to the text boxes and click the button labelled "Add Row" and you should see your form input appear on the active sheet.

Code Notes

• This data entry form requires a lot less code than the previous GUI version that used UiApp but functionally it is equivalent.
• The HTML code is standard except for two non-standard features in the embedded JavaScript:
1. The mechanism used to link the Google Apps Script function getValuesFromForm() to the form formSubmit() action.  This link is made by the line google.script.run.getValuesFromForm(document.forms[0]);
2. The action for the close button google.script.host.close()
• The Google Apps Script function getValuesFromForm() takes a form instance as an argument and uses the name attributes of the text inputs to extract the entered values that it then writes to the sheet.

I did not attempt to discuss HTML here because there are so many books and on-line tutorials available that there really is no need to do so.  One on-line source that I use is the W3C site.

This blog entry is just a very basic introduction to the Google Html Service.  I'll post lots more in the future and will also put at least one chapter on this topic into my work-in-progress book. Having just played with Html Service for an hour or so, I'm really excited about it and I feel it brings a lot to Google Spreadsheet programming and now makes it look and feel a lot like web development.  There is therefore the added incentive of gaining very useful transferable skills.

 See also these later blog entries for more on Html Service

Update 3-Apr-2015: See a recent blog entry for a practical GUI example
Update 11-Apr-2015: See another practical GUI example in this blog entry