Create custom forms with GUI Builder and Google Apps Script

Using Google Forms, you can create custom data form, but the ability to customize them both in terms of functionality and visual aspect can, in some cases, be limiting. In this post I will explain how you can create a custom data form to record entered data in a Google Spreadsheet document using Google Apps Script.
Google Apps Script provides a simple tool for visual graphical user interfaces creation named GUI Builder that we will employ to create our custom form.
First create a new Spreadsheet document, then activate the Script Editor using the menu “Tools -> Script editor …”:

Create custom forms with GUI Builder and Google Apps Script 1

Select “Blank Project” to create an empty script project and save that by using the menu item: “File -> Save”.

Create custom forms with GUI Builder and Google Apps Script 2

The editor will ask you to name the project, insert: “Custom Form to Spreadsheet” and click on the “OK” button to confirm.

Create custom forms with GUI Builder and Google Apps Script 3

Once you assign a name to the project, you can use the GUI Builder to create our own custom interface. To access the GUI Builder tool you need to use the menu item “File -> Build a user interface …”

Create custom forms with GUI Builder and Google Apps Script 4

The GUI Builder tool will load as follows:

Create custom forms with GUI Builder and Google Apps Script 5

The GUI Builder is composed by three main areas, the left bar contains components or items to be used inside your application, the right bar contains properties for the used components and the application preview area presents you a preview of your application. It’s possible to interact with this preview and edit it. Having to create a data form we must first select the “Form Panel” item from the the left bar:

Create custom forms with GUI Builder and Google Apps Script 6

Using the mouse, drag the “Form Panel” item and drop it inside the application area:

Create custom forms with GUI Builder and Google Apps Script 7

At this point, we can expand the Forms Panel item to consume most of the available space in our application. Widen it by dragging the active points (blue squares) to the desired position. We should get something like the following:

Create custom forms with GUI Builder and Google Apps Script 8

Once created our Form Panel, we can begin putting fields in the form. However as conceived by Google, the Form Panel component accepts only one child component, obviously our form can’t be composed of only one field.
The explanation is simple, in our form we have to insert a container component that represents the alignment of child components. In this case we will use the “Absolute Panel” as alignment panel, this panel allows us to insert components inside it which will be placed in an absolute manner or in the exact position in which we place them. To insert the Absolute Panel select it from the left bar:

Create custom forms with GUI Builder and Google Apps Script 9

Like before, using the mouse, drag the “Absolute Panel” item and drop it inside the “Form Panel” component:

Create custom forms with GUI Builder and Google Apps Script 10

Use the active points to broaden the component to occupy the greatest possible proportion of the space offered by the Form Panel:

Create custom forms with GUI Builder and Google Apps Script 11

Finally, we can place the components or fields of our form. We start by inserting a label selecting the “Label” component from the left bar:

Create custom forms with GUI Builder and Google Apps Script 12

The Label component has to be dragged inside the ”Absolute Panel” in the desired position, try to place it in the upper left corner:

Create custom forms with GUI Builder and Google Apps Script 13

By default, the system will assign the text “Label1″ to our component. Obviously, for us, this name is not correct so we can change it using the right bar by entering the desired text in the “Text” field of the “Text” section:

Create custom forms with GUI Builder and Google Apps Script 14

At this point our label should display correctly the words: “Full Name:”. We must now place a component that allows us to enter a value for the label “Full Name”. To do this, select the component “Text Box” from the left bar:

Create custom forms with GUI Builder and Google Apps Script 15

This component will have to be dragged to the right of the label inside the Absolute Panel:

Create custom forms with GUI Builder and Google Apps Script 16

Still using active points broaden the component “Text Box”:

Create custom forms with GUI Builder and Google Apps Script 17

By default, the system will insert “TextBox1″ as text, actually we want the field to be initially empty and then allow the user to enter their name. To do this we use, as before, the right bar and clear the “TextBox1″ value contained within the “Text” field of the “Text” section:

Create custom forms with GUI Builder and Google Apps Script 18

To allow the user to send the form we have to insert a button. Select the component “Submit Button” from the left bar:

Create custom forms with GUI Builder and Google Apps Script 19

Drag it into the Absolute Panel at the bottom left:

Create custom forms with GUI Builder and Google Apps Script 20

By default, the button will be named “SubmitButton1″ to change it use the right bar at the “Text” field of the “Text” section and insert the value “Submit”:

Create custom forms with GUI Builder and Google Apps Script 21

Now we lack a button to allow the user to clear the form, the component to be used is called “Reset Button”. Select “Reset Button” at the left bar:

Create custom forms with GUI Builder and Google Apps Script 22

Drag the component into the Absolute Panel next to the “Submit” button:

Create custom forms with GUI Builder and Google Apps Script 23

In this case, the button name is by default “ResetButton1″, change it by selecting the “Text” field of the section “Text” of the right bar and insert the value “Clear”:

Create custom forms with GUI Builder and Google Apps Script 24

In order to handle the incoming data simply, assign a friendly name to the component “Text Box”. To do so, we must first select it:

Create custom forms with GUI Builder and Google Apps Script 25

At this point, using the right bar, inside the field “ID” of the “Base” section insert the value “userFullName”:

Create custom forms with GUI Builder and Google Apps Script 25

The “userFullName” has to be also inserted inside the field “Name” of the “Input Fields” section:

Create custom forms with GUI Builder and Google Apps Script 27

Save our newly created interface by using the menu item “File -> Save”:

Create custom forms with GUI Builder and Google Apps Script 28

The GUI Builder will ask you to name the project, insert: “My Custom Form” and click on the “OK” button to confirm.

Create custom forms with GUI Builder and Google Apps Script 29

You can now return to the Script Editor to load the GUI Builder project into your application. Inside the script, enter the following code:

function myFunction() {
  var app = UiApp.createApplication();
  app.add(app.loadComponent("My Custom Form"));
  SpreadsheetApp.getActiveSpreadsheet().show(app);
}

Now press the “play” button on the toolbar to start your script. Go back to the original Spreadsheet document, if all went well you should see the following screen:

Create custom forms with GUI Builder and Google Apps Script 30

As you can see our form has (intentionally) some cosmetic defects such as edges and the size is not appropriate. In order to solve these problems go back in the GUI Builder using the menu “File -> Build a user interface …” of the Script Editor. After loading our interface using the mouse select the Form Panel:

Create custom forms with GUI Builder and Google Apps Script 31

Using the right bar under “Border Width” field of the “Borders and Margins” section, insert the value 0:

Create custom forms with GUI Builder and Google Apps Script 32

As you can see the edges of the Form Panel are gone:

Create custom forms with GUI Builder and Google Apps Script 33

Repeat the same operation for the Absolute Panel, select it:

Create custom forms with GUI Builder and Google Apps Script 34

Always using the right bar under “Border Width” field of the “Borders and Margins” section, insert the value 0:

Create custom forms with GUI Builder and Google Apps Script 35

At this point the edges for the Absolute Panel are gone. Let’s save our project via the menu “File -> Save” and execute our script as we did previously. At this point in the Spreadsheet document should look like the following screen:

Create custom forms with GUI Builder and Google Apps Script 36

Much better. Now the real engine of the form or the ability to record the data entered by the user is missing. For this reason, go back to the Script Editor and insert the following code:

function doPost(theForm) {
  var app = UiApp.getActiveApplication();
  var userFullName = theForm.parameter.userFullName;
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().appendRow([userFullName]);
  app.close();
  return app;
}

Everything is ready to receive and record data. We run our script again and return to Spreadsheet document to display the form. Insert the value “Sample Name” in the field “Full Name”:

Create custom forms with GUI Builder and Google Apps Script 37

Press now the “Submit” button to save the form data. As you can see the Spreadsheet document will be correctly populated with values:

Create custom forms with GUI Builder and Google Apps Script 38

Congratulations! You have created ​​your first custom form using Google Apps Script and his GUI Builder.
Of course there is much to say about creating forms with GUI Builder and Google Apps Script, this is just a starting point from where to build on.

Happy scripting!

Consultant, Lean Thinker, Agilist, Technology Lover. Dream: Being worth a TED talk. Project Manager in a wide variety of business applications. Particularly interested in innovation projects, as well as close interaction with costumers.

Taggato con: , , ,
Pubblicato in Google Apps Script, GUI Builder, Spreadsheet
20 commenti su “Create custom forms with GUI Builder and Google Apps Script
  1. jonas says:

    This does not work….
    When i run the script it says TypeError: Cannot call method “show” of null. (line 4)

    • Hello Jonas, for what I can understand seems that the script is not finding the current Spreadsheet document. Just to be sure, have you created the script inside a Spreadsheet document or standalone?

      • Dewunmi says:

        I get the following Error “TypeError: Cannot find function Show in object Spreadsheet. (line 4, file “Code”)”

        I created the script when I selected “Blank Project” which I linked up from an already created spreadsheet.

    • iBen says:

      Thanks for this tuto; I’ve the same problee than Jonas about TypeError: Cannot call method “show” of null. (line 4).
      I created the script standalone

  2. Tom says:

    Marcello. Good tutorial. The Google documentation of the GUI builder is pretty sparse, and your tutorial is a real help in getting started.

    I am having trouble using list boxes. Is there any way to populate a list box with values for the user to select among?

  3. lasa says:

    Hi, Great tutorial, Thanks lot.
    I have few issues
    1. How can i Add file upload function to this GUI?
    2. It gives a error when i publish this as a “web app”

    • Hello Lasa,
      to add file upload functionality please refer to the Google Apps Script documentation specifically here: https://developers.google.com/apps-script/class_fileupload
      In the documentation you will find a sample to demonstrate file upload capabilities using UI Apps.
      Regarding the web app publishing you are right the script is not thought to be used a web app since it’s missing the doGet method and it uses the SpreadsheetApp.getActiveSpreadsheet().show(app); do display the app. For a web based UI App you should just: return app;

      • Alcino Major says:

        Hello Marcello

        Tanks for sharing your knowledge with this very explained tutorial.
        I am a complete noob to google apps scripts, yet, i have managed to get this simple form working following your great tutorial, but when i deploy as a web app, i get this error: “Unknown macro doGet”
        can you post here the right scrip code for make this form work as a web app?

        Tanks for your time

        my current code: “function myFunction() {
        var app = UiApp.createApplication();
        app.add(app.loadComponent(“My Custom Form”));
        SpreadsheetApp.getActiveSpreadsheet().show(app);
        }

        function doPost(theForm) {
        var app = UiApp.getActiveApplication();
        var userFullName = theForm.parameter.userFullName;
        SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().appendRow([userFullName]);
        app.close();
        return app;
        }”

  4. Mike says:

    Hey, I just came across this tutorial and it has been quite helpful. I’m running into the error “Cannot read property “parameter” from undefined.” Now I did alter your code slightly (i called my textbox fields some different than you) and I made those changes in the code but i’m still getting that error. any thoughts?

  5. Steve says:

    I followed your instruction but it doesn’t work for me.
    Click “debug” to see if there would be any error but nothing.

  6. Alexander1st says:

    How can I insert an Image in this form?

  7. Vince Day says:

    Hi,
    This tutorial is great! I’m looking to expand this form to create a form that elicits feedback from folks that attend professional development workshops. However, I’m new to this so I’m running into problems. Do you have any more in-depth tutorials that show how to use radio buttons, list boxes, etc.? I’m also having problems making the form bigger. I have expanded the form and absolute panels but when I run the script I can only view half of the form. Any help you can provide would be greatly appreciated.
    Thanks!

    • Hello Vince and thanks for the feedback. Maybe you don’t know that Google has officially deprecated the UI Builder and it will stop working soon on september 16. If you want to create a project using UI App I can strongly suggest you to not use UI Builder and try to code the interface (form) by hand using the UI App API. For what I can understand the Google’s preferred way to build apps script interfaces is HTML service. You can find more informations here:

  8. Liz says:

    Hi,

    Isn’t GUI builder just great huh!. Your tutorial seems fab, but sadly I only stumbled on it in my search for why google are pulling the plug on it… Dont know if the html service is so straightforward…

  9. null says:

    what does theForm inside the doPost refer to? i cannot get the data entered into th spreadsheet. i only get undefined inside the spreadsheet

  10. Lenita du Plessis says:

    Hi, What do I do when there appears the words ‘DEPRECATED!’ next to ‘build a user interface’?

  11. Lenita du Plessis says:

    Sorry ignore previous post….I just noticed that Google has discontinued this.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>