Google Sheets

Register & Login

INTRO

Having covered this several times on the forum, I thought it was about time I wrote up a proper guide on how to use google sheets as a register and login machine for your app. The simple premise is a sheet that holds the username, the encrypted password (more on which later), and the uid for the user (more on which later too!). On the app demo, a user will register with their username and compliant password, then login with these same credentials. At no point does the app save the "real" password, or send it to the google apps script or google sheet. The aim was again to do this without needing to turn to extensions, and to provide industry level security / password protection.

SETUP

You need:

A google sheet, private, with columns for UID, User and Encrypted Password
A google apps script web app, bound to the sheet, with the code in the SCRIPT section below
An App Inventor app, that provides for the registration and login of the user, and the encryption/decryption facilities, using javascript and SimpleCrypto.

SECURITY

This, I guess, is in two parts:

General internet/Data security
Protecting the user from the developer, and protecting the developer from themselves!

I looked at the various options available for password security, and decided that a straightforward hash - e.g. HMACSha256 would not be of any use, because the developer/owner of the script/sheet could use this to compare, without the need of the actual password. There is no real "back-end" server side area easily accessible within google apps script (that is no available to either the user or the developer, like you get in Firebase), apart from userProperties, but then the user needs to be connected to the web app with their google account, which starts to make things complicated (GD Connector).

I decided therefore to use encryption, provided by the javascript SimpleCrypto, which is based upon crypto.js. This then provides for a method to encrypt the password to a one time hash/encode which can be stored on the google sheet without undue concern. When the user wants to login, this hash/encode is called into the app from the sheet, and the "real" password is supplied in order to decode it. The developer/owner of the sheet/script is not involved in any way in this process (unless they write blocks/script to capture the real password in another way.....but they could do this with any app based login system - I suppose the next step would be to move the textboxes into an online html, but then there are probably keyloggers....). You have to trust someone sometime ?

SimpleCrypto generates a different hash/encode, for the same parameters, each time you run it....

Anyway, the encryption uses two html files to handle the work, and these are on the app in the assets. I did cheat a bit and use the real password for the secret Key and the text to be encrypted, so that the user only had to enter one password, but I feel the password verification helps to make up for this.

On that subject, apparently, requiring an 8 character password, which must include from A-Z,a-z,0-9, and a bunch of special characters produces @ 350 billion combinations. ( I couldn't use all the special characters (33), as some, like the comma and semi-colon, failed in the verification. Check out the passwordVerifier blocks.

UID

You may well have noticed that I have included a uid (just like what Firebase does!) I even used their javascript code (slightly modified) to generate the uid. The uid can be considered as the standard "anonymising" user identifier, and also allows for changes to the username and password/hash/encode, if the uid is used as the marker to record any data.

SHEET

SCRIPT

Fairly standard fare:

register - generates the uid, appends this and the user and encrypted password to the sheet, confirms success back to the app
getusers - returns a list of usernames back to the app for a verification check, to avoid duplicate usernames
login - returns the encrypted password/hash to the app based upon the username, for login verification

function doGet(e) {

var ss = SpreadsheetApp.openById('YOUR SHEET ID HERE');

var sheet = ss.getSheetByName("YOUR SHEET/GRID NAME HERE");

if ( e.parameter.func == "register" ) {

var uid = getUID();

var user = e.parameter.user ;

var passwd = e.parameter.passwd;

sheet.appendRow([uid,user,passwd]);

msg = "New User Added";

}

else if (e.parameter.func == "getusers" ) {

var loginData = sheet.getDataRange().getValues();

var users = "";

for ( var i = 1; i < loginData.length; ++i ) {

users += loginData[i][1] + ",";

msg = users;

}

else if ( e.parameter.func == "login" ) {

var uid;

var user = e.parameter.user;

var passwd;

var loginData = sheet.getDataRange().getValues();

for ( var i = 0; i < loginData.length; ++i ) {

if ( user === loginData[i][1]) {

uid = loginData[i][0];

passwd = loginData[i][2];

msg = "gotPass, "+ uid + "," + user + "," + passwd;

}

return ContentService.createTextOutput(msg);

}

function getUID() {

for(var newID,success=!1;!success;)20==(newID=generatePushID()).length&&(newID,success=!0);

return newID;

}

and uid generator (thank you Firebase Developers):

generatePushID=function(){

var r="0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz",e=0,t=[];

return function(){var o=(new Date).getTime(),n=o===e;e=o;for(var a=new Array(8),h=7;h>=0;h--)a[h]=r.charAt(o%64),o=Math.floor(o/64);

if(0!==o)throw new Error("convert entire timestamp");

var f=a.join("");if(n){for(h=11;h>=0&&63===t[h];h--)t[h]=0;t[h]++}else for(h=0;h<12;h++)t[h]=Math.floor(64*Math.random());

for(h=0;h<12;h++)f+=r.charAt(t[h]);

return f}}();

BLOCKS

HTML

encrypt

<!DOCTYPE html>

<html>

<head>

<title>Encrypt</title>

</head>

<body>

var secretKey = window.AppInventor.getWebViewString();

var simpleCrypto = new SimpleCrypto(secretKey);

var plainText = window.AppInventor.getWebViewString();

var cipherText = simpleCrypto.encrypt(plainText);

window.AppInventor.setWebViewString(cipherText);

</script>

</body>

</html>

decrypt

<!DOCTYPE html>

<html>

<head>

<title>Decrypt</title>

</head>

<body>

var secretKey = window.AppInventor.getWebViewString().split(',')[0];

var simpleCrypto = new SimpleCrypto(secretKey);

var cipherText = window.AppInventor.getWebViewString().split(',')[1];

try {

var decipherText = simpleCrypto.decrypt(cipherText);

window.AppInventor.setWebViewString("PASS");

}

catch (ex) {

window.AppInventor.setWebViewString("FAIL");

}

</script>

</body>

</html>

AIA and FILES

Demo AIA