Making a Google Site with Google Apps Script
The focus of this tutorial is to make a site with header, footer and content that reads and write data in a Google Spreadsheet. As extra we will add JQuery and Boostrap. You can download the code. The result of this tutorial is showing in the next GIF image.
Google Apps Script
Google Apps Script, in summary is to use Javascript on the client and server side from the cloud. It provides easy ways to automate tasks across different Google products and services.
Google Sites
Google Sites allows that people can work together to add attachments, information of other Google applications (like Google Docs, Google Calendar, YouTube and Picasa), and new content freely. Its main advantage is that you do not compile anymore.
JQuery
jQuery is a JavaScript library that simplifies the way you interact with HTML documents, manipulate the DOM tree, handle events, developing animations and adding interactivity with AJAX to web pages. In our example and conventionally, functions belonging to JQuery are represented at the beginning with “$” .
Bootstrap
In short it’s a set of tools that enable a responsive design to a web page using CSS and Javascript libraries.
Let’s start
Enter to https://sites.google.com and select CREATE option:
Choose Blank Template and set a name to our new site.
Select Blank Slate theme, with this, we’ll be able to make later our own menus with bootstrap plugins as Jasny.
Choose CREATE option. With this, we have already created a site with an ease and fast way.
Let’s go to https://drive.google.com and create a folder named “mysite” selecting NEW (NUEVO).
Inside of “mysite” folder create a Google Apps Script selecting NEW ->More -> Google Apps Script.
Name the project, as example could be Laishidua Site.
Folowing steps similar to previous, make a new SpreadSheet, name it LaishiduaUserPass and re-name the first sheet as: Sheet1.
Openning the Laishidua Site’s Google Apps Script make six files besides the .GS file created by default, (in my case was Code.gs): index.html, contentInit.html, contentForm.html, StyleSheet.html, JS_ContentInit.html, JS_ContentForm.html and JS_Libs_Ext.html.
In Code.gs write:
function doGet() {
return HtmlService.createTemplateFromFile('index')
.evaluate()
.setSandboxMode(HtmlService.SandboxMode.IFRAME);
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.getContent();
}
function searchContent(template, callbackname){
var res = new Array();
res[0] = HtmlService.createTemplateFromFile(template).getRawContent();
if (callbackname != null) {
res[1] = callbackname;
}
return res;
}
function saveToSheet(usr, pwd){
var lock = LockService.getPublicLock();
lock.waitLock(30000); // wait 30 seconds before conceding defeat.
try {
var sheet = SpreadsheetApp.
openByUrl('https://docs.google.com/spreadsheets/d/1i4naI0PKSVUWWlKkGPQ8I9-6TpMWPOb0BnQNRc6nfjI/edit')
.getSheetByName("Sheet1");
var lastRow = sheet.getLastRow();
sheet.appendRow([usr, pwd]);
var lastRowRes = sheet.getLastRow();
var res = "Error!";
if (lastRowRes > lastRow) {
res = "Success!";
}
return res;
} catch(e) {
// if error return this
return "Error!";
} finally { //release lock
lock.releaseLock();
}
}
function sesion_GSUserEmail(){
return Session.getActiveUser().getEmail();
}
Code.gs is the only script that will be executed at server side. Below I am going to explain each function.
- doGet() it’s triggered at the beginning, charging index.html. Setting SandboxMode as IFRAME in summary will allow us yo use JQuery and Bootstrap libraries in our sites without any problem.
- include(<file name to include>) allow us to include more scripts in the main index.
- searchContent(<template>, <client side function name to trigger when the template is loaded>) as each content is separated in welcome and the form in two distinct templates, this function will allow us to load them and then execute the second param name that is client side function.
- saveToSheet(<user>, <password>) save each param to the Spreadsheet we already created with name LaishiduaUserPass in this case as example it has the url: https://docs.google.com/spreadsheets/d/1i4naI0PKSVUWWlKkGPQ8I9-6TpMWPOb0BnQNRc6nfjI/. The function prevent concurrent access to the code.
- sesion_GSUserEmail() get the user email of the current logged user.
In StyleSheet.html write:
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" rel="stylesheet">
StyleSheet.html loads the boostrap CSS that we are going to use, also in this file we can load another CSS of our own.
In JS_Libs_Ext.html write:
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
<script src="https://www.google.com/jsapi?autoload={'modules':[{'name':'visualization','version':'1','packages':['corechart']}]}"></script>
JS_Libs_Ext.html loads external Javascript libraries as Jquery, Boostrap y Google Visualization.
In index.html write:
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>LAISHIDUA SITE</title>
<?!= include('StyleSheet'); ?>
</head>
<body>
<div id="headerContent">
HERE YOU CAN PLACE A BANNER.
</div>
<div id="indexContent" class="container-fluid">
</div>
<div id="footerContent">
HERE YOU CAN PLACE A FOOTER.
</div>
</body>
<?!= include('JS_Libs_Ext'); ?>
<?!= include('JS_ContentInit'); ?>
<?!= include('JS_ContentForm'); ?>
</html>
index.html with include function, loads in the beginning the CSS and at the end javascript files that we have created.
In contentInit.html write:
<h2>Welcome / Bienvenido</h2> <h2 id="userEmail"></h2> To go to the form, press the next button: <button id="goToForm" type="button" class="btn btn-primary">Go</button>
In contentInit.html let’s make contentForm.html to load when Go button is pressed. Prior to this, JS_ContentInit.html must have been loaded.
In JS_ContentInit.html write:
<script>
//contentIndex loading functions
function updateDiv(content){
$("#indexContent").fadeOut(500,function(){
$('#indexContent').html(content[0]);
$("#indexContent").fadeIn(500);
if (content.length > 1) {
window[content[1]].call();
}
})
}
function getContent(content, callbackfname){
google.script.run.withSuccessHandler(updateDiv).searchContent(content, callbackfname);
}
//close contentIndex loading functions
// generic functions
function dataTableToArray(data){
var json = JSON.parse(data.toJSON());
var rows = json.rows;
var resArray = new Array();
for(var i in rows) {
var jsonRowC = rows[i].c;
var arrayRow = new Array();
for(var j in jsonRowC) {
if (jsonRowC[j]) {
var jsonRowV = jsonRowC[j].v;
arrayRow[j] = jsonRowV;
} else {
var jsonRowV = "";
arrayRow[j] = jsonRowV;
}
}
resArray.push(arrayRow);
}
return resArray;
}
//close generic functions
//contentInit functions
function setWelcomeToEmail(data){
$("#userEmail").html(data);
}
function init() {
google.script.run.withSuccessHandler(setWelcomeToEmail).sesion_GSUserEmail();
$("#goToForm").on("click", function(e) {
getContent('contentForm', "initContentForm");
});
}
$( document ).ready(function() {
getContent("contentInit", "init");
});
//close contentInit functions
</script>
- updateDiv(content) updates “div” indexContent from index.html with some html content placed in content param.
- getContent(content, callbackfname) it use updateDiv function after search the html content name, the name of html content has to be placed in content param and after this, the function wich name has settled in callbackframe param from client side is triggered.
- dataTableToArray(data) turns a DataTable object to array, the DataTable object it’s obtained using the library google.visualization.Query.
- setWelcomeToEmail(data) updates “h2” html object with the user email.
- init() When “Go” button is pressed, it updates the html content contentForm.html inside the indexContent “div” from index.html, then it executes the function initContentForm located in JS_ContentForm.html.
- $( document ).ready … it triggers in first place wverything inside of function() after the page has been loaded.
In contentForm.html write:
<form role="form"> <div class="form-group"> <label for="usr">Name:</label> <input type="text" class="form-control" id="usr"> </div> <div class="form-group"> <label for="pwd">Password:</label> <input type="password" class="form-control" id="pwd"> </div> <input type="button" class="btn btn-info" value="Save" id="save"> <input type="button" class="btn btn-info" value="Get Passwords" id="getPwds"> </form> <table class="table table-striped"> <thead> <tr> <th>Name</th> <th>Passwords</th> </tr> </thead> <tbody id="myUsrPwdsTableBody"> </tbody> </table>
contentForm.html is a simple html form that are going to save data to the Spreadsheet when “Save” button is pressed and get the data in a table when “Get Passwords” button is. For this, it depends of JS_ContentForm.html script.
In JS_ContentForm.html write:
<script>
function initContentForm() {
$("#save").on("click", function(e) {
save();
});
$("#getPwds").on("click", function(e) {
getPwds();
});
}
function save() {
var usr = $("#usr").val();
var pwd = $("#pwd").val();
google.script.run.withSuccessHandler(savedMessage).saveToSheet(usr, pwd);
}
function savedMessage(data){
alert(data);
}
function getPwds() {
var query =
new google.visualization.Query(
"https://docs.google.com/spreadsheets/d/1i4naI0PKSVUWWlKkGPQ8I9-6TpMWPOb0BnQNRc6nfjI/gviz/tq?gid=0"
);
query.setQuery("select A, B where A = '" + $("#usr").val() + "'");
query.send(setPwdsInTable);
}
function setPwdsInTable(data) {
if (data.isError()) {
console.log('Error in query: ' + data.getMessage() + ' ' + data.getDetailedMessage());
alert("Error to search data!");
return;
}
var dataResponse = data.getDataTable();
var dataArray = dataTableToArray(dataResponse);
var tableBody = $("#myUsrPwdsTableBody");
tableBody.empty();
if ( dataArray.length > 0 ) {
for(var i=0; i<dataArray.length; i++) {
tableBody.append(
"<tr><td>" + dataArray[i][0] + "</td><td>" + dataArray[i][1] + "</td></tr>");
}
}
}
</script>
- initContentForm() it is executed after contentForm.html is loaded, contains two JQuery functions intended to execute the function save() when “Save” is pressed and when “Get Passwords” button is pressed, it executes getPwds().
- save() save the data in the Spreadsheet, fist executes the function saveToSheet from server side and then savedMessage() from client side.
- savedMessage() shows a message from server response.
- getPwds() get data saved in the Spreadsheet focusing in the data entered in the html input with “id” “usr” using Google Visualization library which avoid blocking when client side searches are made. The answer of the search are placed to the setPwdsInTable(data) function.
- setPwdsInTable(data) shows in a table the answer of the search.
Now we can start to add our project to the site that we have already created. For this in script view let’s go to publish menu-> implement as web application, select “new” version and Update.
When pressing “Update” will show othe window, copy the assigned url and press Accept.
Go to our site via its URL, press the button with pencil icon (Edit Page) from top right.
Go to menu insert -> Apps Script and in the pop-up window place the URL that we have already copied from our Script, press “Select”.
Another window is showing, in this, deselect “include border …” and “include title” options, in Height set it with 3000 and press “Save”. On the way back press “Save” from top right.
We have already added our script to our site, but certain adjustments needed.
Go to setup button which icon is a gear next to “share” button.
Go to “Edit Site Layout”. Remove header and sidebar options.
Get back to setup menu and enter to “Manage Site”. Deselect “
Now our site is cleaner. We can see it from the setup menu “Preview page as viewer”.
<———————————————>
As simple and fast we have created a site with a form with Google technology. When you open the created Spreadsheet you will see the data we entered to the form.
Download the code example-1 to make a Google Site with Google Apps Script.
This post is also available in es_ES.