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 />

    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="" type="button" value="Exit" />
  <script type="text/javascript">
        function formSubmit() {

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');;

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[0]);
    2. The action for the close button
  • 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


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

  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.

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

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

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

  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.

  7. Not working for me form properties remain undefined

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

  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.

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

  10. Many thanks for this! Simple but great!

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

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

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

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

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

  16. This comment has been removed by the author.

  17. Thank you so much. This was just what I was looking for! Simple and straightforward. I have also been seeking this solution for a long time!

  18. Thank you so much for your article. It is very helpful. I have two difficulties. When I run the command"//getValuesFromForm", I got an error message: TypeError: Cannot read property "firstName" from undefined. (line 23, file "Code"). Another issue is I don't know how to enter the data from form into a specific line or cell but not in order. Thanks

  19. How would you use this example(form Html) to insert, update and delete data from the spreadsheet with the Google application using the html service?
    without using google site.

  20. A very awesome blog post. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. I was exactly searching for. Thanks for such post and please keep it up vpn

  21. I would state, you do the genuinely amazing.This substance is made to a wonderful degree well.

  22. Great info! I recently came across your blog and have been reading along. I thought I would leave my first comment. I don’t know what to say except that I have. die besten vpn

  23. Some tags of standard HTML were presented variously depending on a web browser a visitor uses.coding image example

  24. So luck to come across your excellent blog. Your blog brings me a great deal of fun.. Good luck with the site.

  25. Excellent article plus its information and I positively bookmark to this site because here I always get an amazing knowledge as I expect. Thanks for this to share with us

  26. wow, great, I was wondering how to cure acne naturally. and found your site by google, learned a lot, now i’m a bit clear. I’ve bookmark your site and also add rss. keep us updated. lemigliori vpn

  27. The information you have posted is very useful. The sites you have referred was good. Thanks for sharing this: Buy Google Places Reviews,

  28. Thanks for sharing an honest guide. Such a pleasure to read and apply to cloud backup for business