102 Apps Script: ObjDb Sheet Register Email PinCode

Introduction

This tutorial demonstrates the user registration and verification via email account.

Successful registration and verification process will return the registered username.

This tutorial uses ObjDb Library to interact with Google Spreadsheet.

Objective

1. Register a new user. Generate and send the PinCode to user email.
2. The user receives the PinCode and click the link to verify the code.

3. Successful verification process returns the correct username.

We use additional javascript utility functions to encode/decode parameter values:

1. baseToBase() - to encode/decode number to/from one base to another.

2. Base64 - to encode/decode string to/from base 64 values.

You can try explore many other encoding techniques.

1. Create Script.

Script name=_102-reguser-pincode.

Copy and paste the following codes:

function setupApp(){

/*script id*/

var SCPID = ScriptApp.getScriptId();

var file = DriveApp.getFileById(SCPID);

var folders = file.getParents();

while (folders.hasNext()){

FOLID=folders.next().getId();

}

FOLDER = DriveApp.getFolderById(FOLID);

var d = new Date();

var t = d.getTime();

var NEWSST = SpreadsheetApp.create("_appdata-102-"+t);

var TEMP = DriveApp.getFileById(NEWSST.getId());

FOLDER.addFile(TEMP);

DriveApp.getRootFolder().removeFile(TEMP);

var scriptProperties = PropertiesService.getScriptProperties();

scriptProperties.setProperties({

scpid:SCPID,

folid:FOLID,

sstid:NEWSST.getId()

});

/*record headers*/

var arrRecordTitles=["admin","user"];

var arrRecordHeaders=[

["tid","timestamp","name","gmail"],

["tid","timestamp","name","gmail","pincode"]

];

for (i in arrRecordTitles){

var Sheet = NEWSST.getSheetByName(arrRecordTitles[i]);

if (Sheet != null) {

NEWSST.setActiveSheet(NEWSST.getSheetByName(arrRecordTitles[i]));

NEWSST.deleteActiveSheet();

}

NewSheet = NEWSST.insertSheet();

NewSheet.setName(arrRecordTitles[i]);

NewSheet.appendRow(arrRecordHeaders[i]);

NewSheet.getRange("A2:A").setNumberFormat('@STRING@');

}

/*https://coderwall.com/p/_g3x9q/how-to-check-if-javascript-object-is-empty*/

function isObjectEmpty(r){for(var n in r)if(r.hasOwnProperty(n))return!1;return!0}

/*http://www.deluge.co/?q=javascript-int-hex-universal-base-converter*/

function baseToBase(fromBase,toBase,value){;if(value=="") value=0;value=parseInt(value,fromBase);return Number(value).toString(toBase).toUpperCase();}

/*http://www.webtoolkit.info/javascript-base64.html#.WapakHcjGL9*/

function initApp(){

SCRIPTPROP = PropertiesService.getScriptProperties();

if (isObjectEmpty(SCRIPTPROP.getProperties())){

setupApp();

SCRIPTPROP = PropertiesService.getScriptProperties();

}

Logger.log(SCRIPTPROP.getProperties());

/* projectkey=MJMF2lqsgWV-I-dlyqJN6OrljYCrJdQKl */

DB1 = objDB.open(SCRIPTPROP.getProperty("sstid"));

}

/* web request listeners */

function doGet(e) {

return handleResponse(e);

}

function doPost(e) {

return handleResponse(e);

}

/* handle action request */

function handleResponse(e) {

var lock = LockService.getPublicLock();

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

try {

var cmd = e.parameter.cmd;

var output = [];

if (cmd == "app") {

output = taskManager("app", e);

} else if (cmd == "getuser") {

output = taskManager("getuser", e);

} else if (cmd == "reguser") {

output = taskManager("reguser", e);

} else if (cmd == "setuser") {

output = taskManager("setuser", e);

} else if (cmd == "deluser") {

output = taskManager("deluser", e);

} else if (cmd == "chkuser") {

output = taskManager("chkuser", e);

}

return ContentService.createTextOutput(JSON.stringify({

"result": "success",

"data": output

})).setMimeType(ContentService.MimeType.JSON);

//return output

} catch (e) { /*if error return this*/

return ContentService.createTextOutput(JSON.stringify({

"result": "error",

"error": e

})).setMimeType(ContentService.MimeType.JSON);

} finally { /*release lock*/

lock.releaseLock();

}

/*taskManager*/

function taskManager(cmd, e) {

initApp();

var output = "";

switch (cmd) {

case "app":

/*test with a call to get rows from admin sheet*/

output=objDB.getRows( DB1, 'admin' );

return output;

break;

case "getuser":

var tid = e.parameter["tid"] || "";

if(tid!=""){output=objDB.getRows(DB1,'user',[],{tid:tid});}

else{output=objDB.getRows( DB1, 'user' );}

return output;

break;

case "reguser":

var objRecord={tid:0,timestamp:"",name:"",gmail:"",pincode:""};

for(var key in objRecord) {objRecord[key]=e.parameter[key];}

var d = new Date();

objRecord.timestamp = d;

objRecord.tid = d.getTime();

objRecord.pincode=baseToBase(10,16,objRecord.tid);

var newrecord=objDB.insertRow( DB1, 'user',objRecord );

Logger.log(newrecord);

/* https://developers.google.com/apps-script/reference/mail */

var link=ScriptApp.getService().getUrl();

MailApp.sendEmail({

name: "App Team",

to: objRecord.gmail,

subject: "App Registration",

htmlBody: "Dear " + objRecord.name + ",<br/> " +

"Your app registration has been successful. <br/> " +

"Your pincode is... <h1>" + objRecord.pincode +"</h1>"+

"<a href='" + link + "?cmd=chkuser&uc="+ Base64.encode(objRecord.name) + "&pc="+ objRecord.pincode + "'>" +

"Click here to test your pincode<br/>" +

"</a>"+

"<br/>"+

"Thank you.<br/>" +

"<br/>"+

"(From App Team)"

});

var g = {parameter:{tid:objRecord.tid}};

output= taskManager("getuser",g);

return output;

break;

case "chkuser":

var objCheck={uc:"",pc:""};

var objRecord={name:"",pincode:""};

for(var key in objCheck) {objCheck[key]=e.parameter[key];}

if(objCheck.uc!="" && objCheck.pc!=""){

objRecord.name=Base64.decode(objCheck.uc);

objRecord.pincode=objCheck.pc;

output=objDB.getRows(DB1,'user',["name"],objRecord);

}

return output;

break;

case "setuser":

var objRecord={tid:0,timestamp:"",name:"",gmail:"",fileid:""};

for(var key in objRecord) {objRecord[key]=e.parameter[key];}

var d = new Date();

objRecord.timestamp = d;

var newrecord=objDB.updateRow( DB1, 'user',objRecord,{tid:objRecord.tid} );

Logger.log(newrecord);

if (newrecord==1){

var f = {parameter:{tid:objRecord.tid}};

output= taskManager("getuser",f);

}

return output;

break;

case "deluser":

var objRecord={tid:0,timestamp:"",name:"",gmail:""};

for(var key in objRecord) {objRecord[key]=e.parameter[key];}

var delrecord=objDB.deleteRow(DB1, 'user', {tid:objRecord.tid} );

Logger.log(delrecord);

if (delrecord==1) {output= [{tid:0}];}

else {output= [];}

return output;

break;

}/*swith*/

}

function test(){

Logger.log(taskManager("app")) ;

}

function testGetUsers() {

var e = {parameter: {cmd: "getuser"}};Logger.log(taskManager("getuser",e));

}

function testGetUser() {

var e = {parameter: {cmd: "getuser",tid:"123"}};Logger.log(taskManager("getuser",e));

}

function testRegUser() {

var e = {parameter: {cmd: "adduser",name: "aba",gmail: "notarazi.com@gmail.com"}};Logger.log(taskManager("reguser",e));

}

function testSetUser() {

var e = {parameter: {cmd: "setuser",tid:123,name: "vivi",gmail: "vovoi@gmail.com"}};Logger.log(taskManager("setuser",e));

}

function testDelUser() {

var e = {parameter: {cmd: "deluser",tid: "789"}};Logger.log(taskManager("deluser",e));

}

2. Test

Publish as Web App.

Run testRegUser()

The script created a new record eg

tid	timestamp	name	gmail	pincode
1504338453958	02/09/2017	aba	notarazi.com@gmail.com	15E418F25C6

The script sent email to the user eg

The link contains the url, cmd, usercode (uc) and pincode(pc) parameters eg

https://script.google.com/macros/s/AKfycbw9oSgyvRmK8mm0Yi48HTNAM9M9-2WnBRftMbd1ZJP2OzV3v_YH/exec?cmd=chkuser&uc=YWJh&pc=15E418F25C6

When the user clicked the link, the script returned

In this example, we use pincode instead of password.

The pincode represents the idea of token.

Token can only be retrieved by the owner of the email account.

Token may have expiry date/time, which means the user would have to check the email box again for new pincode in order to use the service.

Programming Steps

Learn the powerful enterprise adaptable database:

Getting Started With ADABAS & Natural

Saturday, April 22, 2017

102 Apps Script: ObjDb Sheet Register Email PinCode

Introduction

Objective

1. Create Script.

2. Test

No comments:

Post a Comment