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:
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.
|
1 2 3 4 5 6 |
[{ "orange":"na", "green":3, "blue":"na", "purple":4 }] |
In this tutorial we will look at the following :
- Creating a Menu Item and Sub-Item in the Add-on’s menu.
- Setting up a Dialog Box.
- Preparing a HTML file to set inside the Dialog Box.
- Getting the data from the Dialog Box and Sending it Server-side.
Look at whatever portion of this tutorial you need to help you in your own project.
First, let’s look at…
The Code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
//--GLOBALS-- var ui = SpreadsheetApp.getUi(); function onOpen(e){ // Create menu options ui.createAddonMenu() .addSubMenu(ui.createMenu("Admin") .addItem("Test","test")) .addToUi(); }; function test(){ //Call the HTML file and set the width and height var html = HtmlService.createHtmlOutputFromFile("testUI") .setWidth(450) .setHeight(300); //Display the dialog var dialog = ui.showModalDialog(html, "Select the relevant module and unit"); }; function runsies(values){ //Display the values submitted from the dialog box in the Logger. Logger.log(values); }; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 |
<!DOCTYPE html> <html> <head> <base target="_top"> <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"> </head> <body> <div> <table> <col width="60"> <col width="50"> <col width="50"> <col width="50"> <col width="50"> <col width="50"> <col width="50"> <col width="50"> <col width="50"> <col width="50"> <tr> <th></th><th><strong>Unit:</strong></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th> </tr> <tr> <th><strong>Module</strong></th> <th><strong>n/a</strong></th> <th><strong>1</strong></th> <th><strong>2</strong></th> <th><strong>3</strong></th> <th><strong>4</strong></th> <th><strong>5</strong></th> <th><strong>6</strong></th> <th><strong>7</strong></th> <th><strong>8</strong></th> </tr> <tr> <td>Orange </td> <td><input type="radio" name="orange" value="na" checked></td> <td><input type="radio" name="orange" value="1"></td> <td><input type="radio" name="orange" value="2"></td> <td><input type="radio" name="orange" value="3"></td> <td><input type="radio" name="orange" value="4"></td> <td><input type="radio" name="orange" value="5"></td> <td><input type="radio" name="orange" value="6"></td> <td><input type="radio" name="orange" value="7"></td> <td><input type="radio" name="orange" value="8"></td> </tr> <tr> <td>Blue </td> <td><input type="radio" name="blue" value="na" checked></td> <td><input type="radio" name="blue" value="1"></td> <td><input type="radio" name="blue" value="2"></td> <td><input type="radio" name="blue" value="3"></td> <td><input type="radio" name="blue" value="4"></td> <td><input type="radio" name="blue" value="5"></td> <td><input type="radio" name="blue" value="6"></td> <td><input type="radio" name="blue" value="7"></td> <td><input type="radio" name="blue" value="8"></td> </tr> <tr> <td>Green </td> <td><input type="radio" name="green" value="na" checked></td> <td><input type="radio" name="green" value="1"></td> <td><input type="radio" name="green" value="2"></td> <td><input type="radio" name="green" value="3"></td> <td><input type="radio" name="green" value="4"></td> <td><input type="radio" name="green" value="5"></td> <td><input type="radio" name="green" value="6"></td> <td><input type="radio" name="green" value="7"></td> <td><input type="radio" name="green" value="8"></td> </tr> <tr> <td>Purple </td> <td><input type="radio" name="purple" value="na" checked></td> <td><input type="radio" name="purple" value="1"></td> <td><input type="radio" name="purple" value="2"></td> <td><input type="radio" name="purple" value="3"></td> <td><input type="radio" name="purple" value="4"></td> <td><input type="radio" name="purple" value="5"></td> <td><input type="radio" name="purple" value="6"></td> <td><input type="radio" name="purple" value="7"></td> <td><input type="radio" name="purple" value="8"></td> </tr> </table> <input type="button" value="Submit" class="action" onclick="form_data()" > <input type="button" value="Close" onclick="google.script.host.close()" /> </div> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"> </script> <script> function form_data(){ var values = [{ "orange":$("input[name=orange]:checked").val(), "blue":$("input[name=blue]:checked").val(), "green":$("input[name=green]:checked").val(), "purple":$("input[name=purple]:checked").val() }]; google.script.run.withSuccessHandler().runsies(values); closeIt() }; function closeIt(){ google.script.host.close() }; </script> </body> </html> |
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”.
(Note Add-ons has been updated to Extensions)
Here is how it will look:
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
//--GLOBALS-- var ui = SpreadsheetApp.getUi(); function onOpen(e){ // Create menu options ui.createAddonMenu() .addSubMenu(ui.createMenu("Admin") .addItem("Test","test")) .addToUi(); }; |
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:
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:
showModalDialog(HTML, title)– This prevents the user from interacting with the Sheet or Document when the dialogue is up.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:
|
14 15 16 17 18 19 20 21 22 23 24 |
function test(){ //Call the HTML file and set the width and height var html = HtmlService.createHtmlOutputFromFile("testUI") .setWidth(450) .setHeight(300); //Display the dialog var dialog = ui.showModalDialog(html, "Select the relevant module and unit"); }; |
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 |
<!DOCTYPE html> <html> <head> <base target="_top"> <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"> </head> <body> <div> <table> <col width="60"> <col width="50"> <col width="50"> <col width="50"> <col width="50"> <col width="50"> <col width="50"> <col width="50"> <col width="50"> <col width="50"> <tr> <th></th><th><strong>Unit:</strong></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th> </tr> <tr> <th><strong>Module</strong></th> <th><strong>n/a</strong></th> <th><strong>1</strong></th> <th><strong>2</strong></th> <th><strong>3</strong></th> <th><strong>4</strong></th> <th><strong>5</strong></th> <th><strong>6</strong></th> <th><strong>7</strong></th> <th><strong>8</strong></th> </tr> <tr> <td>Orange </td> <td><input type="radio" name="orange" value="na" checked></td> <td><input type="radio" name="orange" value="1"></td> <td><input type="radio" name="orange" value="2"></td> <td><input type="radio" name="orange" value="3"></td> <td><input type="radio" name="orange" value="4"></td> <td><input type="radio" name="orange" value="5"></td> <td><input type="radio" name="orange" value="6"></td> <td><input type="radio" name="orange" value="7"></td> <td><input type="radio" name="orange" value="8"></td> </tr> <tr> <td>Blue </td> <td><input type="radio" name="blue" value="na" checked></td> <td><input type="radio" name="blue" value="1"></td> <td><input type="radio" name="blue" value="2"></td> <td><input type="radio" name="blue" value="3"></td> <td><input type="radio" name="blue" value="4"></td> <td><input type="radio" name="blue" value="5"></td> <td><input type="radio" name="blue" value="6"></td> <td><input type="radio" name="blue" value="7"></td> <td><input type="radio" name="blue" value="8"></td> </tr> <tr> <td>Green </td> <td><input type="radio" name="green" value="na" checked></td> <td><input type="radio" name="green" value="1"></td> <td><input type="radio" name="green" value="2"></td> <td><input type="radio" name="green" value="3"></td> <td><input type="radio" name="green" value="4"></td> <td><input type="radio" name="green" value="5"></td> <td><input type="radio" name="green" value="6"></td> <td><input type="radio" name="green" value="7"></td> <td><input type="radio" name="green" value="8"></td> </tr> <tr> <td>Purple </td> <td><input type="radio" name="purple" value="na" checked></td> <td><input type="radio" name="purple" value="1"></td> <td><input type="radio" name="purple" value="2"></td> <td><input type="radio" name="purple" value="3"></td> <td><input type="radio" name="purple" value="4"></td> <td><input type="radio" name="purple" value="5"></td> <td><input type="radio" name="purple" value="6"></td> <td><input type="radio" name="purple" value="7"></td> <td><input type="radio" name="purple" value="8"></td> </tr> </table> <input type="submit" value="Submit" class="action" onclick="form_data()" > <input type="button" value="Close" onclick="google.script.host.close()" > </div> |
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.
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.
|
25 26 27 28 |
function runsies(values){ //Display the values submitted from the dialog box in the Logger. Logger.log(values); }; |
Reviewing the log data <Crtl>+<Enter> we can see that the array is now server-side (Old IDE).
UPDATE 16 Jan 2021 – NEW IDE – With the new IDE (Apps Script editor) the log will not report the results after the execution of a script from Google Sheets, nor will the shortcut “ctrl + enter” work to reveal the log.
You will need to go to the editor’s sidebar and select “Executions” then select Runsies from the menu. You should then see the logged results.
An alternative to the Logger.log(values) in the runsies() function might me to replace it with the code below so you know it is working:
|
1 2 3 4 5 6 7 8 |
function runsies(values) { //Display the values submitted from the dialogue box in the Logger. let range = SpreadsheetApp .getActiveSpreadsheet() .getActiveSheet() .getRange("A1") .setValue(values) }; |
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.
Need help with Google Workspace development?
Go something to solve bigger than Chat GPT?
I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.
Schedule a free consultation today to discuss your needs and get started or learn more about our services here.
Thank you so much ! That’s just awesome, I’ve been looking for it for a long time !
Thanks VH! I’m glad you found it useful.
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.
This is a fantastic walkthrough, got me up and running in no time. Thank you!
Hi Jake,
Thanks so much. I glad you found it useful.
Cheers,
Yagi
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!
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.
Perfect! Thanks again!
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.
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:
~Yagi
This is really helpful! Is it possible to submit data and close the dialog box with just the submit button?
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 thecloseIt()function.Cheers,
Yagi
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);
Yeap. Definitely a bug there Stefano. Thanks for pointing it out. I have updated the code.
Cheers,
Yagi
Hey,
I am struggling that the function form_data() is not called at all. Anyone else experiencing this?
Best,
Max
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.
Hi Alpha,
What error are you getting? You are on the right track.
Feel free to paste your code below.
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
Hi Paul,
Nice suggestion. I will add it to my tutorial list.
Thanks,
Yagi
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
Hi, did you find a solution?
I might be facing the same issue. runsies function is not triggered at all.
I can’t find a way to retrieve data from the form to the server side.
I might be facing the same issue.
I can gent the values from the form to the server side.
runsies() function never executed.
I didn’t make any changes in the code. Example as it is, does not work for me.
Any help?
Hi horaciux,
I think I understand what the issued might be. With the new Google Apps Script IDE, the log will not be displayed below the code. You will need to go to the editor’s sidebar and select Executions then select Runsies from the menu. You should then see the logged results.
An alternative to the Logger.log(values) in the
runsies()function might me to replace it with the code below so you know it is working:I’ll make an update to the tutorial.
Thanks for making me aware of the changes.
~Yagi
Thank you. It solved my problem. I had been looking for that answer for a long time and I just found it here.
Hi Yagisanatode! your blog is very helpfull! I realy appreciate your job.
i tried to use this post to my day job, working with ClientSide and ServerSide, and i’m having troubles with this.
Can u help me with some explanations?
the problem is describle here at stackoverflow community, link below.
https://stackoverflow.com/questions/65370339/server-side-and-client-side-communications-functions-with-values-at-google-apps
Hi Lucas,
Thanks for your email. I took a look at your StackOverflow post and it looks like you have resolved the issue. Yes, in hindsight, using the submit attribute would have been a better methodology. I’ve updated the tutorial. Thanks.
However, I am a little baffled as to why the submit input did not run your function. I tried to replicate the issue but didn’t have much luck. If you want me to further check, you can share a copy of your Google Sheets project with me as editor. If you are happy with your solution. No worries.
Happy coding!
~Yagi
Hi, wonderful tutorial explained very well, congratulations!
About showModalDialog (HTML, title) I have a question that I can’t solve: how suspend the server-side script while the showModalDialog is open?
Basically I would like the script resumes after the user dismisses the dialog.
Sorry for my bad English I hope I made myself clear.
Thanks bye
Gabriele
Hi Gabriele,
Depending upon what you are doing, Prompt might be your best bet here. You can return a selection value from the prompt depending on the button that is selected.
Cheers,
Yagi
Hi Yagi!
Thank you for yet another wonderful tutorial. I’m struggling, though, to convert this to my need. I’m trying to setup a dropdown menu in the dialog, so I combined this with the w3schools post about dropdowns to create the following:
but in this the
runsies()function isn’t triggering at all. How can I convert your code sending an array of options into a dropdown? Thanks!