Making a Google Site with Google Apps Script

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.

Making a Google Site with Google Apps Script

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:

site1

Choose Blank Template and set a name to our new site.

site2

Select Blank Slate theme, with this, we’ll be able to make later our own menus with bootstrap plugins as Jasny.

site3

Choose CREATE option. With this, we have already created a site with an ease and fast way.

site4

Let’s go to https://drive.google.com and create a folder named “mysite” selecting NEW (NUEVO).

site5

Inside of “mysite” folder create a Google Apps Script selecting NEW ->More -> Google Apps Script.

Name the project, as example could be Laishidua Site.

site6

 

Folowing steps similar to previous, make a new SpreadSheet, name it LaishiduaUserPass and re-name the first sheet as: Sheet1.

site8

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.

site7

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.

site11

When pressing “Update” will show othe window, copy the assigned url and press Accept.

site12

Go to our site via its URL, press the button with pencil icon (Edit Page) from top right.

site10

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

site13

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.

site14

We have already added our script to our site, but certain adjustments needed.

site9

Go to setup button which icon is a gear next to “share” button.

site10

 

Go to “Edit Site Layout”. Remove header and sidebar options.

site15

Get back to setup menu and enter to “Manage Site”. Deselect “

site16

Now our site is cleaner. We can see it from the setup menu “Preview page as viewer”.

site17

<———————————————>

site18

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.

site19

Download the code example-1 to make a Google Site with Google Apps Script.

This post is also available in es_ES.

Leave a Reply

Your email address will not be published. Required fields are marked *