Login & Register with Google Sheets and Google Apps Script (HK)

MIT App Inventor Help
, , ,
5

OK, there were quite a few little things wrong with the script, and the blocks and the script did not match up with what was in the video, but eventually got them to work together :slight_smile: I used virtual screens instead of real screens.

Herewith a demo app project. You will need to add your own script url and sheet id.

LARGSGAS_blank.aia (6.9 KB)

and also the google apps script for the web app:

SCRIPT 
function doGet(e) {
return Authentication(e);
}
function doPost(e) {
return Authentication(e);
}



function Authentication(e) {

if (e.parameter.func == 'Create') {

var ss = SpreadsheetApp.openById(e.parameter.ID);
var sh = ss.getSheetByName(e.parameter.SH);

var rg = sh.getName() + "!" + sh.getDataRange().getA1Notation();
var email = e.parameter.email;
var sql = '"Select B where B=\'' + email + '\'"';
var qry = '=IFERROR(query(' + rg + ',' + sql + '),"")';
var ts = ss.insertSheet();
  const sheets = SpreadsheetApp.getActive().getSheets();
  sheets[sheets.length-1].setName(`Sheet${sheets.length}`);
var setQuery = ts.getRange(1,1).setFormula(qry);
var getResult = ts.getDataRange().getValues();
ss.deleteSheet(ts);


if (getResult == "") {
var data = [e.parameter.fullname, e.parameter.email, e.parameter.password, e.parameter.phone];
sh.appendRow(data);

return ContentService.createTextOutput(getResult);
}

}

if (e.parameter.func == 'Login') {
var msg = '';
var ss = SpreadsheetApp.openById(e.parameter.ID);
var sh = ss.getSheetByName(e.parameter.SH);

var email = e.parameter.email;
var password = e.parameter.password;

var rg = sh.getName() + "!" + sh.getDataRange().getA1Notation();
var sql = '"Select A,B,C where B=\'' + email + '\'"';
var qry = '=IFERROR(query(' + rg + ',' + sql + '),"")';

var ts = ss.insertSheet();
var setQuery = ts.getRange(1,1).setFormula(qry);
var getName = ts.getRange(1,1).getValue();
var getPWD = ts.getRange(1,3).getValue();
ss.deleteSheet(ts);

if (getName != "") {
if (getPWD != password) {
msg = "ERPWD";
return ContentService.createTextOutput("ERPWD");
}
else {
msg = getName;
}
}
return ContentService.createTextOutput(msg);
}
}

This is done using a google sheet with an en locale

Also need to give credit where credit is due: Much of the script was originally created by me, in a project long since forgotten / mislaid. HK, used it, but added some of the error checking functionality into the script and the blocks, now we have turned full circle and I have edited what HK has done to make it work !!

1 Like