Monday, February 1, 2016

1004 Apps Scripts HTML Service

How to write Google App Script to provide HTML Service

The HTML service lets you serve web pages that can interact with server-side Apps Script functions. It is particularly useful for building web apps or adding custom user interfaces in Google Docs, Sheets, and Forms. You can even use it to generate the body of an email.

Objective

1. Create user.html page

2. Create guest.html page

3. Create validateUser method

1) Create a spreadsheet

Spreadsheet name=HtmlService

Sheet name=users

Column names= tid,timestamp,gmail

Enter a test gmail.

The tid and timestamp are reserved for future use.

2) Open Code Editor within the spreadsheet

HtmlService

Create two HTML file eg user.html and guest.html

user.html

<!DOCTYPE html>

<html>

<head>

</head>

<body>

</body>

</html>

guest.html

<!DOCTYPE html>

<html>

<head>

</head>

<body>

<h1>guest</h1>

</body>

</html>

Edit code script ie Code.gs

/*global var*/

var SCPID=ScriptApp.getScriptId(); /*script id*/

var SST=SpreadsheetApp.getActiveSpreadsheet(); /*active spreadsheet */

var SSTID=SpreadsheetApp.getActiveSpreadsheet().getId(); /*spreadsheet id*/

var SSTFILE=DriveApp.getFileById(SSTID); /*get file*/

var Folders = SSTFILE.getParents(); /*get parent collection*/

while (Folders.hasNext()) {

var Folder = Folders.next();

Logger.log(Folder.getName());

var FOLID = Folder.getId(); /*get id for first parent item*/

}

/*log the global var*/

function logGlobalVar(){

Logger.log("script id:"+SCPID);

Logger.log("sheet id:"+SSTID);

Logger.log("folder id:"+FOLID);

Logger.log("SST:"+SST.getName());

}

/* web request listeners */

function doGet(e) {return handleResponse(e);}

function doPost(e) {return handleResponse(e);}

/* web request handler */

function handleResponse(e) {

var lock = LockService.getPublicLock();

lock.waitLock(30000); // wait 30 seconds before conceding defeat.

try {

var action = e.parameter.action;

if (action == 'app') {

return getApp(e);

}

catch (e) {/*if error return this*/return ContentService.createTextOutput(JSON.stringify({"result": "error","error": e})).setMimeType(ContentService.MimeType.JSON);}

finally { /*release lock*/ lock.releaseLock();}

}

/* get app page */

function getApp(e){

var CurrentUser=Session.getActiveUser().getEmail();

var sheet = SST.getSheetByName('users');

/*start from row 2 and column3 until last row-1*/

var arruser = sheet.getRange(2,3,sheet.getLastRow()-1).getValues();

var ValidUser=findInArray(arruser,[0],CurrentUser);

//if object is not empty open App, else open NoApp

if (ValidUser.length>0) {

htmlfile='user';

}else {

htmlfile='guest';

}

var output = HtmlService.createHtmlOutputFromFile(htmlfile);

output.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);

return output;

}

/* findInArray(arrdata,arrcol,searchvalue){} */

/* find a string in array row and columns */

/* if arrcol is null then concat all cols */

function findInArray(arrdata,arrcol,searchvalue){

output=[];

for (var rowno in arrdata){

if (arrcol==null){ /*concat cols*/

//Logger.log(arrdata[rowno]);

var strtest=JSON.stringify(arrdata[rowno]);

var n = strtest.search(searchvalue);

if (n>=0){

arrdata[rowno].unshift(rowno);

output.push(arrdata[rowno]);

}

}/*if (arrdata==null)*/

else{

var strtest='';

for(var colno in arrcol){

strtest=strtest.concat((strtest==''?'':','),arrdata[rowno][arrcol[colno]]);

}

//Logger.log(strtest);

var n = strtest.search(searchvalue);

if (n>=0){

arrdata[rowno].unshift(rowno);

output.push(arrdata[rowno]);

}/*if*/

}/*else*/

}/*row*/

return output;

}/*function findInArray*/

function testFindInArray(){

var CurrentUser=Session.getActiveUser().getEmail();

var sheet = SST.getSheetByName('users');

/*start from row 2 and columnx until last row-1*/

var userarray = sheet.getRange(2,3,sheet.getLastRow()-1).getValues();

var ValidUser=findInArray(userarray,[0],CurrentUser);

Logger.log(ValidUser);

Logger.log(ValidUser.length);

}

3) Deploy

Execute the app using your account (because the app needs to access the spreadsheet that is privately accessible by your account only) but allow anyone to access the apps.