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 dialogue 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 dialogue box looks like:

Dialog Form in Google Apps Script

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

In this tutorial we will look at the following :

Look at whatever portion of this tutorial you need to help you in your own project.

First, let’s look at…

The Code

TOP

Creating Menu Items and Sub-Items in the Add-ons Dropdown.

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 set up 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 dialogue box in Google Apps Script is really simple. First, you need to decide what type of dialogue 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 dialogue is up.
  2. showModalessDialog(HTML, title) -This does not prevent the user from interacting with the Sheet or Document when the dialogue is up.

We don’t want the user to do anything with the Sheet when the dialogue 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 dialogue box. We’ll also need to create the testUI.html file, but we will talk about this later.

To set up the HTML for our dialogue 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 dialogue on line 21 with the variable dialog. This will initialize the dialogue and pop it up over the Sheets screen.

Next, let’s create the testUI.html file that will be displayed in the dialogue 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 exist 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 dialogue 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 and 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 dialogue box and stops the program from continuing to run.

Note! I created a function called “CloseIt()” to do the same thing you could just as easily replace this with the “CloseIt()” function, but I wanted you to see that you could use google.script.host.close() right in the input. 
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 module and puts them in an object array to be pushed to the server-side function runsies() in code.gs and then closes the dialogue 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().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. 

We then run a closeIt() function to close the dialog box.

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

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro!

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

21 thoughts on “Google Apps Script – Getting Input Data From a Dialog Box in Google Sheets”

      1. Hey, nice code!

        Do you know the code to pop pop open already existing menu items (File > Make a Copy) using App Script?

        The code can stop there.

        Thanks again.

  1. Thank you for the thorough tutorial…very helpful. One question…after passing the “values” array to the runsies function, how can one access the individual elements of the array? Thanks!

    1. Hi Jeff,

      This particular array has an object in it.
      To get the objects in the array you would do something like this:

      var value = [{
      "orange" : "na",
      "green":3,
      "blue":"na",
      "purple":4
      }];

      alert(value[0].orange);//Do something here.

      I hope this helps.

  2. Thank you so much for this detailed tutorial, would you please show us how to use the same approach but to upload file to google drive and the display the result in the spreadsheet? I would be thankful.

    1. Hi Mohammed,

      Thanks for the kind words.

      Your request is actually quite a deal more complex and different from the original post. I do like the concept though and will add it to my list of tutorials I want to write and hopefully get to in a couple of months or so.

      I would recommend you look to Fiverr if you need to get some custom Google Apps Script written well and quickly. A project like this might cost you around $200 US depending on your specific needs. Here’s a link to the Fiverr page for Google Apps Script developers. The first one is an affiliate link if you want to support me – it won’t cost you any extra, but the little commission I get helps to pay for the running of this site. Otherwise, feel free to click that direct link if you want to go that direction:

    2. Fiverr (Support Yagi)
    3. Fiverr
    4. ~Yagi

    1. Hi Alpha,

      Yes, the submit button should already be doing this. When the button is clicked. It references the form_data( function which collects the data before closing the dialogue box with the closeIt() function.

      Cheers,

      Yagi

    2. there’s a bug in his code on line 100

      google.script.run.withSuccessHandler(closeIt).runsies(values);

      should be

      google.script.run.withSuccessHandler(closeIt()).runsies(values);

      1. Yeap. Definitely a bug there Stefano. Thanks for pointing it out. I have updated the code.

        Cheers,

        Yagi

        1. Hey,

          I am struggling that the function form_data() is not called at all. Anyone else experiencing this?

          Best,
          Max

  3. How do you access the values stored in the array? I tried to implement var valuesObj = values[0][“orange”]
    Logger.log(valuesObj); into runsies, but that breaks it.

      1. Hi Yagi,

        Very help and detailed tutorial , clean and clear code,

        Suggestion: maybe create a html service/ dialog box with dependent list
        ex ( box 1 fruits) after selecting ( Box 2 country)
        this will be application in many ways as this could be used as a
        data entry form ( like your flight booking article )
        part entry / data validation

  4. Hi,

    This post is great. However I wonder if something has changed with G Suite authorizations. This code works perfectly on my personal Google account. However, it does not work in my G Suite Account ( I am the Admin!). It may be unrelated, but when I approve the App in my G Suite account, I do not see the popup for “This App isn’t Verified”. I do see that popup box when I approve it in my personal account.

    The command:

    google.script.run.withSuccessHandler().runsies(values);

    Does not appear to work in the G Suite account.

    I have not found any way to retrieve information from custom HTML dialog box in my G Suite Account. Any help you can provide would be wonderful!!!!!

    Thank you,

    Renya

  5. Thank you. It solved my problem. I had been looking for that answer for a long time and I just found it here.

Leave a Reply