OTP-GS-GAS-AI2

INTRO

The other day, I was perusing Stack Overflow, and came across a topic where someone was working up an OTP solution using google forms. I thought I might have a go at something similar using googles sheets and google apps script, which could be used for Appinventor, or more generally. I managed to achieve this using one web app, broken up into three sections: submission of email & otp request, verification check, and verfication completion.

The workflow:

• App user enters their email address

• The web app records their email address to the google sheet, generates their otp, and registers their verification as FALSE.

• The web app sends an email to the user's account with the otp and a link to verify

• The user goes to their email account, notes their otp, and presses the VERIFY link

• The web app will check the otp against the google sheets database to ensure it is valid, and where it has been verified, reporting its findings to the user.

• If the otp is correct, and the email address has not yet been verified, the web app presents a web page where the user can enter their otp

• If the otp is correct, and is on the database, and has not yet been registered, then it completes registration (changing FALSE to TRUE) and reports the outcome to the user.

• Failure to enter the correct otp will generate a message to try again, if the email/otp is already registered, then the user is advised that they are registered.

• The user returns to their app, where a sign in will be successful, given their email has been correctly registered

Note: This could be reworked so that the user notes their otp in the email received, and returns to their app to complete verification in a webview 

HTML/JAVASCRIPT/APPS SCRIPT

I used five files in my apps script project, three html pages, a javascript page, and the code.gs, which contains the doGet(e). The doGet(e) differentiates the workflow depending on the parameter provided to it.

SETUP

• Create a new spreadsheet, and set it up as shown

• Create a bound apps script project (select Apps Script from the Extensions menu in the spreadsheet)

• Apply the five files, with the content below

• Deploy your web app and note the url

• Create a new aia project in AppInventor (or use the example blank project provided here)

• Test

Note: because I use doGet(e) in the web app, it is possible to test the otp processes using your computer browser

CODE, SCRIPTS, & HTML

Code.gs

You will see that I used a different approach to "finding" values in the spreadsheet to the usual get a range and iterate over it. I did fetch ranges for each column, flattened them from a list of lists to a plain list, then used the indexOf to "find" the correct value from the parameter provided. The returned index (-1 = not found) can then be used to return the corresponding values in other columns, and action as necessary.

For the variable "surl", in the Email Response section of the doGet(e), replace the script url with your own, be sure to include the "?HTMLOTP=" at the end...

function doGet(e) {

/* @Email Response */

if (e.parameters.EMAIL) {

 var ss = SpreadsheetApp.getActive();

 var sh = ss.getSheets()[0];

 var lr = sh.getLastRow();

 var otp = Math.floor(10000000 + Math.random() * 90000000);

 var userEmail = e.parameters.EMAIL[0];

 var emailAdds = sh.getRange('A2:A'+lr).getValues();

 var verifs = sh.getRange('C2:C'+lr).getValues();

 var emlIdx = emailAdds.flat().indexOf(userEmail);

 var msg = "";

 if (emlIdx == -1) {

       sh.appendRow([userEmail, otp, false]);

       var surl = 'https://script.google.com/macros/s/AKfycbzLUHW33LCscript-url-editednuLD9Za9aDYL8nfS4VEb0W/exec?HTMLOTP=' + otp;

       var htmlbody = '<p>Here is your OTP code: '+ otp+'</p><br><a href="'+surl+'">VERIFY</a>';

       GmailApp.sendEmail(userEmail,"OTP Verification for your AppInventor App",'',{htmlBody:htmlbody});

       msg="An email has been sent to your provided email account with the OTP";

 }

 else {

   if (verifs.flat()[emlIdx]==true) {

     msg="Email already verified, no action required";

   }

   else {

     msg="Email present but not verified, find the verification email and complete your verification";

   }

 }

return ContentService.createTextOutput(msg);

}

/* @SignIn Responses */

else if (e.parameters.SIGNIN) {

 var ss = SpreadsheetApp.getActive();

 var sh = ss.getSheets()[0];

 var lr = sh.getLastRow();

 var userEmail = e.parameters.SIGNIN[0];

 var emailAdds = sh.getRange('A2:A'+lr).getValues();

 var verifs = sh.getRange('C2:C'+lr).getValues();

 var emlIdx = emailAdds.flat().indexOf(userEmail);

 var msg = "";

   if (emlIdx == -1) {

       msg="No such email available for sign in, you need to verify by OTP first";

 }

 else {

   if (verifs.flat()[emlIdx]==true) {

     msg="You are signed in";

   }

   else {

     msg="Email present but not verified, find the verification email and complete your verification to be able to sign in";

   }

 }

return ContentService.createTextOutput(msg);

}

/* @Activation - otherwise show message */

else if (e.parameters.HTMLOTP) {

 var ss = SpreadsheetApp.getActive();

 var sh = ss.getSheets()[0];

 var lr = sh.getLastRow();

 var otp = parseInt(e.parameters.HTMLOTP[0]);

 var otpList = sh.getRange('B2:B'+lr).getValues();

 var verifs = sh.getRange('C2:C'+lr).getValues();

 var otpIdx = otpList.flat().indexOf(otp);

 if (otpIdx == -1) {

   return HtmlService.createHtmlOutputFromFile('invalid');   

 }

 else if (verifs.flat()[otpIdx] == false){

   return HtmlService.createTemplateFromFile('activate').evaluate();

 }

 else if (verifs.flat()[otpIdx] == true){

   return HtmlService.createHtmlOutputFromFile('verified');

 }

}

}

/* @Include JavaScript and CSS Files */

function include(filename) {

 return HtmlService.createHtmlOutputFromFile(filename).getContent();

}

/* @Process Form */

function processForm(fmObjVal) {

  var ss = SpreadsheetApp.getActive();

 var sh = ss.getSheets()[0];

 var lr = sh.getLastRow();

 var fotp = parseInt(fmObjVal);

 var fotpList = sh.getRange('B2:B'+lr).getValues();

 var fverifs = sh.getRange('C2:C'+lr).getValues();

 var fotpIdx = fotpList.flat().indexOf(fotp);

 if (fotpIdx == -1) {

   return 'invalid';  

 }

 else if (fverifs.flat()[fotpIdx] == false){

   var row = fotpIdx + 2;

   sh.getRange("C"+ row).setValue(true);

   SpreadsheetApp.flush();

   return 'verified';

 }

 else if (fverifs.flat()[fotpIdx] == true){

   return 'alreadyVerified';

 }

}

JavaScript.html

<script>

 // Prevent forms from submitting.

 function preventFormSubmit() {

   var forms = document.querySelectorAll('form');

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

     forms[i].addEventListener('submit', function(event) {

     event.preventDefault();

     });

   }

 }

 window.addEventListener('load', preventFormSubmit);   

  function onSuccess(type) {

    if (type == 'invalid') {

      document.getElementById('invalid').style.display='block';

    }

    else if (type == 'verified') {

      document.getElementById('verified').style.display='block';

    }

    else if (type == 'alreadyVerified') {

      document.getElementById('alreadyVerified').style.display='block';

    }

  }

 function handleFormSubmit() {

   document.getElementById('invalid').style.display='none';

   document.getElementById('verified').style.display='none';

   document.getElementById('alreadyVerified').style.display='none';

   var fov = document.forms["myForm"]["otpVal"].value;

   google.script.run.withSuccessHandler(onSuccess).processForm(fov);

   document.getElementById("myForm").reset();

 }

</script>

activate.html

<!DOCTYPE html>

<html>

 <head>

   <base target="_top">

   <?!= include('JavaScript'); ?>

 </head>

 <body>

   <form id="myForm" onsubmit="handleFormSubmit()">

   <p>Enter your OTP code and press the button to verify your email</p>

   <input id="otpVal" name="otpVal" type="text"/><br><br>

   <input type="submit" value="Verify"/>

   </form>

   <div id="invalid" style="display:none">

     <h2>Your access is unauthorised, there is no otp recorded for the number you provided.</h2>

     <h2>You can try again, or close this page</h2>

   </div>

   <div id="verified" style="display:none">

     <h2>Your email address is now verified!</h2>

     <h2>You can now close this page, and return to your app to sign in.</h2>

   </div>

   <div id="alreadyVerified" style="display:none">

     <h2>Your email address is already verified</h2>

     <h2>You can now close this page, and return to your app to sign in.</h2>

   </div>

 </body>

</html>

invalid.html

<!DOCTYPE html>

<html>

 <head>

   <base target="_top">

 </head>

 <body>

   <h2>Your access is unauthorised.</h2>

   <h2>There is no otp recorded for the number you provided.</h2>

 </body>

</html>

verified.html

<!DOCTYPE html>

<html>

 <head>

   <base target="_top">

 </head>

 <body>

   <h2>You are now verified</h2>

 </body>

</html>

BLOCKS

SHEET

EMAIL

VERIFICATION WEB PAGE

VIDEO

to follow

AIA and FILES

otpEmailVerification_blank.aia

RESOURCES

Apps Script HTML Service

bpwebs - data entry form HTML Service