Scripting.One: Google Apps Script Web App Sheets as Database

Building your own web app or website takes amount of your money. Although there some low cost web hosting platform out there, it isn't cheap either. You can build the page with default templates. The next thing you need to consider is the database.

Database don't come cheap either and it will cost your per usage. Or, you could choose from various package. But, then again, you need to anticipate your space usage over time.

Google Sheets as database

Sheets already has the basic feature of a database. Which is,structured tables and can be accessed through script. We are going to use Google Apps Script for this and let me show you an overall process on how will this work.

The 3 components

Login page - this is a web page. For demo we are using this as to where user will provide login credentials. 3 parts for this. When you create a new Apps Script, you will need to have Code.gs for Google Apps Scripts, HTML for the login page and SCRIPT file for the javascript. I'm showing below a working example.

Login Page Scripts:

	/**
	LOGIN PAGE .GS COMPONENT
	*/
	function doGet(request) {
	return HtmlService
	.createTemplateFromFile('HTML')
	.evaluate()
	.setSandboxMode(HtmlService.SandboxMode.IFRAME)
	.setTitle('SUNNYSIDEUP');
	}

	function include(filename) {
	return HtmlService.createHtmlOutputFromFile(filename)
	.setSandboxMode(HtmlService.SandboxMode.IFRAME)
	.getContent();
	}

	function LogInAccepted(username,password){
	var ss = SpreadsheetApp.openById("1U7sWHbli6npx4RyuwYpoNa1kZS_rzpY8AsDVVYB3av8");//<--SPREADSHEET ID
	var ws = ss.getSheetByName("Users");
	var ws_log = ss.getSheetByName("Login History");
	var tbl = ws.getDataRange().getValues();
	var user = ArrayLib.filterByValue(tbl, 4, username);//<--INSTALL ArrayLib library. app id is MOHgh9lncF2UxY-NXF58v3eVJ5jnXUK_T
	if(user.length === 0){
	return {ACCEPTED:false,ERROR_MSG:"User not recognized"};
	}else{
	var email = user[0][2];
	var isAdmin = user[0][3];
	var pass = user[0][5];
	var activated = user[0][6];
	var avatar_link = user[0][7];
	var userdata = {USERNAME:username,EMAIL:email,IMAGELINK:avatar_link,ISADMIN:isAdmin};
	if(activated){
	if(pass === password){
	ws_log.appendRow([new Date,username,true,"Log-in Accepted"]);
	return {ACCEPTED:true,ERROR_MSG:"Log-in Accepted",USERDATA:userdata};
	}else{
	ws_log.appendRow([new Date,username,false,"Incorrect Password"]);
	return {ACCEPTED:false,ERROR_MSG:"Incorrect Password",USERDATA:userdata};
	}
	}else{
	ws_log.appendRow([new Date,username,false,"Deactivated account"]);
	return {ACCEPTED:false,ERROR_MSG:"Deactivated account",USERDATA:userdata};
	}
	}
	}

	function LogSession(){

	}

view raw Code_gs.js hosted with ❤ by GitHub

	<!--LOGIN PAGE HTML-->
	<!DOCTYPE html>
	<?!= include("SCRIPT"); ?>
	<html>
	<head>
	<base target="_top">
	</head>
	<body>
	<div class="container">
	<label for="uname"><b>Username</b></label>
	<input type="text" placeholder="Enter Username" name="uname" id="uname" required>
	<label for="psw"><b>Password</b></label>
	<input type="password" placeholder="Enter Password" name="psw" id="psw" required>
	<button onclick="submit_credential()">Login</button>
	</div>
	</body>
	</html>

view raw HTML.html hosted with ❤ by GitHub

	<!--LOGIN PAGE HTML SCRIPT COMPONENT-->
	<script>
	var appLink = {DEV:"https://script.google.com/macros/s/AKfycbyLjLk-HFsgDAvpxoJQXXaJWmvfKg9XfZAoHt8D4A5-/dev",
	EXEC:"https://script.google.com/macros/s/AKfycbw9H6Ca0qYkcSZncPjWPMlJ_Ik1rhK4tRiLrGlxA4wVZWioRkpk/exec"};

	function submit_credential(){
	var uname = document.getElementById("uname").value;
	var psw = document.getElementById("psw").value;
	google.script.run.withSuccessHandler(credential_submisstion).LogInAccepted(uname,psw);
	}

	function credential_submisstion(result){
	var post_user_data = "?username=" + result.USERDATA.USERNAME + "&email=" + result.USERDATA.EMAIL + "&imgUrl=" + result.USERDATA.IMAGELINK;
	var appUrl;
	if(result.ACCEPTED){
	if(result.USERDATA.ISADMIN){
	appUrl = appLink.EXEC + post_user_data;
	}else{
	appUrl = appLink.DEV_USER + post_user_data;
	}
	window.open(appUrl, '_top');
	}else{
	alert(result.ERROR_MSG);
	}
	}
	</script>

view raw SCRIPT.html hosted with ❤ by GitHub

User Credential Database - this is the repository of the user credentials. It could also be some other database needed to validate user id/passwords or user sessions. Here's how the spreadsheet was structured for application. You can use the credentials shown to preview the demo web application. Click LINK for the login page

Web app - this could be the main web page and where the services are in a successful login

The Script:

	/**
	Landing Page Code.gs
	.gs component
	*/
	function doGet(request) {

	var params = JSON.parse(JSON.stringify(request));
	var uname = params.parameter.username;
	var email = params.parameter.email;
	var imgUrl = params.parameter.imgUrl;
	var default_imgUrl = "https://www.pngfind.com/pngs/m/381-3819326_default-avatar-svg-png-icon-free-download-avatar.png";
	var html = HtmlService.createTemplateFromFile('HTML').evaluate().getContent();
	html = html.replace(default_imgUrl, imgUrl);
	html = html.replace('<a id="user_name">User Name</a>','<a id="user_name">' + uname + '</a>');
	html = html.replace('<a id="email">email</a>','<a id="email">' + email + '</a>');
	return HtmlService.createHtmlOutput(html)
	.setTitle('LANDING PAGE \| ' + uname);

	}

	function include(filename) {
	return HtmlService.createHtmlOutputFromFile(filename)
	.setSandboxMode(HtmlService.SandboxMode.IFRAME)
	.getContent();
	}

	function LOGOUT(uname){
	var ss = SpreadsheetApp.openById("1U7sWHbli6npx4RyuwYpoNa1kZS_rzpY8AsDVVYB3av8");
	var ws_log = ss.getSheetByName("Login History");
	ws_log.appendRow([new Date(),uname,false,"Logged out"]);
	var login_urls = {DEV:"https://script.google.com/macros/s/AKfycbzRFrHJYuRmvwbb5dvHQZR9_MYGbCJFCPyXsvfvVMIR/dev",
	EXEC:"https://script.google.com/macros/s/AKfycbw4Aal12GLsTNUqKldrbKqUKV_QCWMakTVniBr80OCsPNqkmtqk/exec"};
	return login_urls.EXEC;
	}

view raw lp_CODE_gs.js hosted with ❤ by GitHub

	<!--THIS WILL BE YOUR HTML COMPONENT ON YOU APPS SCRIPT-->
	<!DOCTYPE html>
	<?!= include("SCRIPT"); ?>
	<html>
	<head>
	<base target="_top">
	</head>
	<body>
	<div style="margin:auto;">
	<p>
	<img src="https://www.pngfind.com/pngs/m/381-3819326_default-avatar-svg-png-icon-free-download-avatar.png" class="w3-circle" alt="avatar" style="width:50px;height:50px">
	</p>
	<p><a id="user_name">User Name</a></p>
	<p><a id="email">email</a></p>
	<p><a href='javascript:' onclick='logout()'>logout</a></p>
	</div>
	</body>
	</html>

view raw lp_HTML.html hosted with ❤ by GitHub

	/**
	THIS WILL BE THE HTML SCRIPT COMPONENT
	*/
	<script>
	function logout(){
	var uname = document.getElementById("user_name").innerText;
	google.script.run.withFailureHandler(not_logged).withSuccessHandler(show_login).LOGOUT(uname);
	}

	function show_login(url){
	window.open(url, '_top');
	}

	function not_logged(){
	alert("test");
	}
	</script>

view raw lp_SCRIPT.html hosted with ❤ by GitHub

This is a very quick setup or scripting session for this demo. This is enough to develop your application with same approach. This is foundational you may tweak a little to some degree. To learn more about Google Apps Script, click Buy Now button below. It's a good reference for your study.

Scripting.One

Pages

Sunday, July 19, 2020

Google Apps Script Web App Sheets as Database

1 comment: