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



14 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. Great article!! I've been searching the web for hours trying to find out how to grab data from a Google HTML Service form and your article was right on point. Thanks for your help.

    ReplyDelete
  3. At last, after searching, searching, searching, a simple, straightforward tutorial about how to accomplish this!

    ReplyDelete
  4. This comment has been removed by a blog administrator.

    ReplyDelete
  5. sorry a bit confused. How do you direct data to a specific spreadsheet? SpreadsheetApp.getActiveSpreadsheet doesn't seem to be enough info

    ReplyDelete
  6. Re:Lawrence Towers
    This script is bound to the spreadsheet, and creates a sort of UI mediator. It is only open when the sheet itself is open, go getActiveSpeadsheet is sufficient.
    If you want to do this in a stand alone app, you'd use getSpreadsheetById() or getSpreadsheetByName(), allowing you to work with any sheet you are allowed to edit.

    ReplyDelete
  7. Not working for me form properties remain undefined

    ReplyDelete
    Replies
    1. sorry about that was running the form in sandbox mode that leaves the properties undefined.

      Delete
  8. Wicked! Thank God.

    I've been to at least 30 websites to try to figure this one piece of info out that I had left to learn with no luck.

    This one simple bit will help me tidy up the Google Add-on I've already launched and do plenty more as well. Thanks for sharing.

    ReplyDelete
  9. What about the opposite? I mean, get values from the spreadsheet to display them in a HTML form?

    ReplyDelete
  10. Many thanks for this! Simple but great!

    ReplyDelete
  11. Thank you- I have followed the instructions and it looks as it should! However, I am not sure when the pop up form will appear when this is published. Will it appear upon a browser refresh? Can I embed a button which will launch the pop up form? I prefer the latter..

    ReplyDelete
  12. Great straightforward approach. Like others I have been looking for hours to find this answer

    ReplyDelete
  13. Do you know if the 'required' feature has been disabled when the form is submitted. Mine does not work. Anything can be submitted

    ReplyDelete