Google Apps Script – Getting Input Data From a Dialog Box in Google Sheets

Google Apps Script, Jquery, Javascript, HTML

One of the weekly tasks in my day job as an academic administrator is to look at a number of ‘modules’ or courses that our college is running and complete some analysis and crosschecking. Each week the students complete a ‘unit’. However, during some quarters, not all modules are doing the same unit. Before I can run my code I need to determine what modules are running and what units we are up to for me to run my automated code.

To do this I created a dialog box when the code is run from the add-on bar. In a few clicks, I can then choose the relevant modules and units and then run the selected code.

Let’s take a look at what the dialog box looks like:

Dialog Form in Google Apps Script

Upon “Submit”, the dialog box returns an array  of objects of checked values from the radio buttons that can be uses in the server-side Google Apps Script.

In this tutorial we will look at the following :

Look at what ever portion of this tutorial you need to help you in you own project.

First, let’s look at…

The Code

TOP

Creating Menu Items and Sub-Items in the Add-ons Drop down.

Our first task is to create a convenient way to access my automation program. At a later date I might want to make this into an Add-on for my company to use so I am going to put the access button in the Add-ons drop-down menu. I might also have some other executions running to analyse my data so I am going to put this program in a sub-menu called “Admin”.

Here is how it will look:

Sub-menu for Google Apps Script

We’re just going to call this program, “Test”because it is not the whole program we will be running.

Let’s look at the code.

First up we need to get the User Interface (UI) class so we can work inside the Google Sheets UI on line 3 with SpreadsheetApp.getUi();.

Now we’ll call a special Google Apps Script function called a trigger that will run when the spreadsheet or documents open (onOpen).

Next, the UI class is called again on line 8 and we setup the menu with ui.createAddonMenu() inside this method we can either add an item link directly or we can add a sub menu. For this tutorial we’ll create a sub menu of “Admin” (Line 9) just in case we want to create more automation codes at a later date.  You’ll probably note that “Admin” is not the main menu category, it “Dialog” this is actually the name of the project:

Project Name Google Apps Script

Inside this sub menu we will add an item with the addItem(title, function name) method. This method takes two arguments, the title, in our case, “Test”, and the function name the menu will lead to, in our case “test”.

Once the menu has been set up, it needs to be added to the ui, line 11.

Now when we go into Add-ons>Dialogs>Admin>Test, it will run the test() function.
TOP

Setting Up A Custom Dialog Box

Setting up a custom dialog box in Google Apps Script is really simple. First you need to decide what type of dialog you need. There are two types of custom dialogues:

  1. showModalDialog(HTML, title) – This prevents the user from interacting with the Sheet or Document when the dialog is up.
  2. showModalessDialog(HTML, title) -This does not prevent the user from interacting with the Sheet or Document when the dialog is up.

We don’t want the user to do anything with the Sheet when the dialog is up so we will choose option one.

Let’s take a look at the code:

Before we can call the showModalDialog() method, we need to get the html document that we put all our html, CSS and client-side Javascript that we are going to use to display what will be inside our dialog box. We’ll also need to create the testUI.html file, but we will talk about this later.

To setup the html for our dialog we call the HtmlService class and create what we want to display from the file “testUI” with createHtmlOutputFromFile(file name) on line 16. The method can also take a width and a height. In our case I have set the width to 450 pixels and the height to 300 pixels.

Once the variable html has been set up we can create the dialog on line 21 with the variable dialog. This will initialize the dialog and pop it up over the Sheets screen.

Next, let’s create the testUI.html file that will be displayed in the dialog box.
TOP

Preparing a HTML file to set inside the Dialog Box

Our goal here is to create a simple set or radio buttons that the user can choose from to select a unit for each module. If a unit does not exists for the quarter, then the radio button will be left alone and remain at “n/a” for not applicable. Otherwise, the user will select a unit for each module. Here is the dialog again.

Dialog Form in Google Apps Script

As you can see, the sheet is styled pretty neatly. To save time, I just went with Google’s recommended CSS stylesheet. This stylesheet also has some custom classes for you to modify things like buttons – you can see that I added it to line 5 below.  You can also add some custom CSS if you wish to do some more tweaking to the layout of your UI.

Let’s quickly take a look at the html side of the code. I’ve kept it fairly simple without any looping with jquery so it’s easier to read.

The html and body tags close down in line 109 an 110.

NOTE:  You can see that I did not create a <form>. Forms will prompt a new page an cause problems with collecting the data from the page upon submission.

After setting the table on line 10, we dive into creating custom column widths to tidy things up.  This is, of course, is optional.

Line 22 creates the First header for the table and then line 25-34 creates the second level headers. The Google CSS creates an underline for the header which looks quite nice.

You can then see the 4 blocks of modules with their radio buttons. Be sure to set the name value for each line of radio buttons to the same so you can determine the checked ones. As you can see I named each radio input for each module the same as the colour of the module.

Finally down on line 85 we create the “submit” input that, when clicked, will run the form_data() function. I added the class “action” to simply provide the blue colour to the button using the Google custom CSS.

Line 86 then adds the “Close” button. Google Apps Script has a custom close method google.script.host.close() that closes the dialog box and stops the program from continuing to run.
TOP

Getting the data from the Dialog Box and Sending it Server-side.

When the ‘submit’ button is clicked (Line 85 testUI.html) it calls the form_data() function. This function grabs all the checked results from the radio buttons for each modules and puts them in an object array to be pushed to the server-side function runsies() in code.gs and then closes the dialog box.

Let’s take a look at the Javascript code in the testUi.html file:

First we import jQuery using Google’s api link on line 89-90. Then we dive into the form_data() funciton.

First we create the values variable to store the 4 object keys, Orange, Blue, Green and Purple to represent each module. We use jQuery to find the ‘value’ identifier that was check, val() , of  each input by their ‘name’ identifier in the html.

jQuery Get Checked Value from Radio Button

Once the array is ready we call the google.script.run.withSuccessHandler(closeIt).runsies(values); on line 99. This is a callback function that is better read from right to left. When this script is run it sends the values to the runsies() function in code.gs server-side. Then if all is successful, it will call the client-side closeIt() function (line 101-103) to close the dialog. 

Another example of how to use these callback functions can be found here:

Finally, going back to the code.gs script, we see that the function runsies() simply logs the array. Of course we can do much more with this data once we have it server-side, but that is for another time.

Reviewing the log data <Crtl>+<Enter> we can see that the array is now server-side.

Server-side log
TOP

Conclusion

That’s it! All done! In this tutorial you have looked at how to create a custom dialog for Google Sheets using Google Apps Script, grab the results and prepare them for server-side use. If you  want to hone your skills try and add some other html inputs to the values array. Alternatively, you could see how you might make use of the server-side array  data in the Code.gs file.  Let me know what you come up with.

Leave a Reply