Saturday, May 25, 2013

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



Thursday, May 23, 2013

GUI Builder Out, Html Service In

It is proving difficult to keep up with all the changes in Google Apps.  I suppose in some ways this is a good thing because it shows that the whole area is moving forward.  However, it complicates matters when you're trying to write about it.  One change of immediate significance is that GUI Builder is being phased out. Quote "The GUI Builder will continue to be available until September 9, 2013. After that point, you will not be able to create or manage GUI Builder components, although existing components will still function."

The Html Service is the wave of the future so I'm planning to read up on this and I'll do a posting on it soon.  One real positive I've taken from the Google link above is that Html Service can be used with JQuery and JQuery UI.  That's a real boon to Google Apps developers in that the skills gained will be transferable to wider web development.

Another link with some more information.

Wednesday, May 22, 2013

Latest From Google - Google Apps Scripts In Docs

I'm preparing a blog entry on manipulation of Google Docs from Spreadsheets and have just noticed that you can now write scripts hosted in Docs just as in Spreadsheets

That entry was date 14th May this year.

Exciting times!

I'll do my blog posting as planned but this Google announcement is a significant development.


Saturday, May 11, 2013

VBA and Google Apps Script Side-By-Side


Appendix A, provisionally entitled  "Appendix A: Excel VBA And Google Apps Script Comparison"
has been added to my book Google Spreadsheet Programming

Download the free sample which includes the appendix covering common spreadsheet programming tasks in both VBA and Google Apps Script.


Please send me any comments or suggestions that you have so that I can add to and improve the material it contains.  More content will be added in due time.

Some Notes On Google Apps Scripts and VBA


The most common type of request that I receive from readers is how to translate Excel VBA into Google Apps Script.  Google Spreadsheets implements most of the standard spreadsheet functions in addition to a few of its own.  However, from a programming perspective Excel and Google Spreadsheets are quite different:

  • VBA is the last survivor of the old pre-VB.NET VB lineage with the main branch ending at VB6.  Google Apps Script is a JavaScript dialect with a very different syntax and philosophy from VBA.  Use of semi-colons to end statements, curly braces to delimit code blocks, and case sensitivity are just a few of the more obvious ways in which Google Apps Script differs from VBA.
  • Google Apps Script, being a JavaScript dialect, comes with some really useful productivity enhancers such as functions as first class objects, very flexible data structures in the form of its arrays and objects, and built-in regular expressions.  See Douglas Crockford's very well-known O'Reilly title JavaScript The Good Parts for all the details.

However, both languages are hosted within applications and are used as the "glue" to integrate applications.  Their APIs are therefore quite similar in philosophy if not in detail.

Check out the PDF and let me know what you think!

Update 22-May-2013
I'm getting positive feedback on this blog entry so there is clearly some interest in this. Accordingly, I will continue to update the PDF with additional code examples.  Suggestions or examples are also very welcome!