Google Apps Script-templates, Javascript, CSS
So, I started working on a larger Google Sheet Sidebar project in Google Apps Script recently and I quickly realised that it was going to be a mess if I didn’t separate my Javascript, CSS and even some of my HTML into separate files. However, if you have ever noticed in the script editor that there is no way for you to add script or style files to the code. Your only two options are Google Script files *.gs and Html files *.html.
Then, what’s the trick?
The trick is to create separate HTML files for your CSS and Javascript and include or import them into your main HTML file. Unfortunately, you can’t do this with the standard:
HtmlService.createHtmlOutputFromFile(filename)
Instead, we will need to use the templating method:
html = HtmlService.createTemplateFromFile(filename)
We will get into a little more detail on templates later in this tutorial.
My main reference for this was the Google Apps Script UI best practice guide, and you will see code snippets of the first example there that I have modified for my own example.
The thing is, the explanations were a bit vague for me to work out clearly so I really needed to create an example of my own to work through how to use it. The example below breaks down the steps to create file relationships to make your code look neater. It also dives into some uses of template statements in HTML.
The Final Result
The goal of our little sidebar project will be to display a sidebar with coloured paragraph text, a list generated with Javascript and a randomly assigned body page the contain the text “Body 1” or “Body 2”.
Take a look at the demo:
Let’s take a look at the code:
Code.gs
|
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 |
/* * Using templating, connecting CSS, JS and HTML files to main HTML file. * * https://developers.google.com/apps-script/guides/html/best-practices * https://developers.google.com/apps-script/guides/html/templates * */ // --Globals-- // Current File var ui = SpreadsheetApp.getUi(); //Set up the Menu Dropdown. function onOpen(e) { ui.createMenu("Multi HTML") .addItem("Show Sidebar","setupSideBar") .addToUi(); }; //Set up for first time use. function onInstall(e){ ui.alert("Starting"); onOpen(); }; function setupSideBar(){ var html = HtmlService .createTemplateFromFile("index") .evaluate(); //Once created it becomes a HtmlOutput html.setTitle("Sections to Sheets"); ui.showSidebar(html); }; // Creates an import or include function so files can be added // inside the main index. function include(filename){ return HtmlService.createHtmlOutputFromFile(filename) .getContent(); }; |
Line 11 grabs the user interface (ui) for the spreadsheet we will be working on. You could equally use DocumentApp.getUi() if you want your sidebar to be for a document.
onOpen(e)
The onOpen(e) function (lines 15-19) is a Google Apps Script function that will run as soon as the linked Google file is opened. See Simple Triggers for more information.
When we open the file we want a menu with the title “Multi HTML” to appear that, when clicked, will reveal a drop-down with a “Show Sidebar” link that is clicked and in turn reveals our sidebar. Line 16 creates the main menu. Line 17 then ads an item to that menu. The item takes a name and a function. Finally, Line 18 adds this setup to the user interface.
onInstall(e)
This function (Lines 22-25) is more useful if you are going to create an add-on and you want to provide instructions or special code the first time the project is associated with the file. It isn’t really necessary in this example.
setupSideBar()
The setupSideBar() function is called when the user clicks “Show Sidebar” in the “Multi HTML” menu. Instead of simply displaying a file we are going to create a template from our main HTML file index.html.
This will also allow us to use templating language to add the other files into the index.html.
On line 28 we call the HtmlService class to create the template from the index.html file. This file is then evaluated which means it will look at all the elements and template statements and complete them before the index.html is displayed.
Once the template has become evaluated, it then becomes a file – in our case the html variable – and we can set the title (line 32) and input it into the sidebar (Line 33).
include()
This function is basically an import function that allows the user to call one of their pages and insert into the main index.html file. I gleaned this function directly from the Best Practices page.
index.html
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<!DOCTYPE html> <html> <head> <base target="_top"> <?!= include('css') ?> </head> <body> <p>Hello<p> <? if(Math.floor(Math.random() * 2) == 1){ ?> <?!= include('body1') ?> <? } else { ?> <?!= include('body2') ?> <? } ?> <div id="MyList"></div> <?!= include('js') ?> </body> </html> |
This is the main index page. We grab our css.html file and add it to the <head> (line 5). You’ll see the template scriptlet like this:
<?!= include('css') ?>
This particular scriptlet calls the include function from our Code.gs file and inputs not only the text but the code data. If you’ve ever used PHP or python’s Flask or Django this will be familiar.
There are 3 types of template scriptlets:
<? something ?>– allows you to run code like a function or anifstatement. Does not return a value in the HTML.<?= something ?>– allows you to print or displays everything in-between as raw text. So if you use this to import another HTML document then it will print stuff like:<div id=isVisible><p>I'm text!</p></div>instead of just:I'm text!Contextual escaping to the pros.<?!= something ?> – allow forced printing, which means that you can run scripts from other pages.
Line 11-15 is just an added bonus to help you better understand how to use the different scriptlets. Let’s take a look.
Line 11 creates an if statement that randomly call either 1 or 0. No results of this code will be printed so we use <? something ?> only.
If one (1) is raised, then we will call our include function to grab our body1.html file and put its contents in the index.html file – in this case, Body 1. Because we want the results of this function call to be displayed, we will need to use the <?!= something ?> template scriptlet (Line 12).
Alternatively, if one (1) is not raised, we will call our include function to grab our body2.html file resulting in, Body 2 (Line 14).
You notice on line 13 and 14, we add the rest of the syntax of the if statement using the <? something ?> scriptlet.
Note that the if statement here is just an example of what we can do with the template scriptlets. If you just want to add the contents of another HTML file, simply run the include function.
In line with best practices, we add the js.html file to the bottom of the index.html file.
css.html
|
1 2 3 4 5 6 |
<style> p { color: purple; } </style> |
In our example, we import the css.html file into the index.html file with our include function. Our CSS here simply changes the colour of our paragraph <p> tag. You can add any code you want here. However, if you are calling a script link like Google Apps Script’s own CSS package, it is recommended to do it in your main file.
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
js.html
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<script> // A list created from an array in JS. var theList = ["This", "is", "a", "list"]; var ul = document.createElement('ul'); document.getElementById('MyList').appendChild(ul); theList.forEach(function(name){ var li = document.createElement('li'); ul.appendChild(li); li.innerHTML += name; }); </script> |
In our example, we import the js.html file into the index.html file with our include function. This is our Javascript file where we add all our code to manipulate the client-side part of our project.
In the above example, we simply create a list from an array.
It’s probably good practice to not fall into the trap of completing coding tasks client side when they could be done inside a server-side Google script (*.gs) file.
body1.html and body2.html
|
1 |
<div id="body1"><p>Body 1<p></div> |
|
1 |
<div id="body2"><p>Body 2<p></div> |
In our example, we import both the body1.html and body2.html files into the index.html file with our include function.
As you can see by the demo, the CSS file also reads the paragraph <p> tag in these files and changes their colour.
But What if my Google Script File is Getting Unwieldy?
You can easily have multiple Google Script files. They will all talk to each other as if they were one big file. This means you can just call the function from other *.gs files without having to do any fancy business, like importing them.
Just remember not to create functions of the same name – yep, done that.
Issues
Debugging Javascript, CSS and HTML can be a pain in the Google Apps Script Editor. Yes, you can call:
HtmlService.CreateTemplateFromFile(filename).getCodeWithComment()
However, this is still not a great tool to check for errors. What you will find when you get an error is basically the line of code where you have created the template from the file will express a non-specific error (In our example the error will probably be on line 28). From there, you need to dig around Chromes Developer Tools (F12) to hopefully get a lead on where you messed up.
Personally, I recommend debugging Javascript, CSS and HTML in a separate editor before adding it to your project.
Conclusion
Using the HtmlService.createTemplateFromFile(filename) module allows you to add the templating scriptlets to allow HTML files to be added inside other files. Knowing this, you can now create separate Javascript and CSS files under the *.html format. You can also add other HTML files into a HTML main file. Finally, you can have multiple Google Script files and they will all talk to each other happily without having to do anything special.
Need help with Google Workspace development?
My team of experts can help you with all of your needs, from custom app development to integrations and security. We 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.
Hello, thanks for this very interesting post. Is it possible to push variables from my apps script into the javascript file who is included in the main html ?
Absolutely.
This post might help you out there.
How to Get Something from Google Sheets and Display it in the Sidebar in Google Apps Script