OTP-GS-GAS-AI2

OneTimePassword, Sheets, AppsScript, AppInventor

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:

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

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

Far less blocks than code and html! Note that I didn't do anything special after sign in, these blocks are a basic demo.

SHEET

The spreadsheet is simple, just three columns

EMAIL

VERIFICATION WEB PAGE

VIDEO

to follow