Register and Login using Google sheets

[Antonis_Zairopoulos]

Hi there, i wanted to make a login and register screen where the data is stored on google sheets. I followed a tutorial but I have a problem. I can Login only if the password contains only numbers. I want it to be able to contain etters as well. I cant find where that restriction is… Please help

I will attach the google sheets code and the blocks

1. List item

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 username = e.parameter.username;

var sql = ‘"Select B where B=’’+username+’’"’;

var qry = ‘=IFERROR(query(’ + rg + ‘,’ + sql + ‘),"")’;

var ts = ss.insertSheet();

var setQuery = ts.getRange(1,1).setFormula(qry);

var getResult = ts.getDataRange().getValues();

ss.deleteSheet(ts);

if (getResult==""){

var data =[e.parameter.name, e.parameter.username, e.parameter.password];

sh.appendRow(data);

return ContentService.createTextOutput(getResult);}

}

//------------------------------------------------------

if (e.parameter.func == “Login”) {

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

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

var username = e.parameter.username;

var password = e.parameter.password;

var rg = sh.getName() + “!” + sh.getDataRange().getA1Notation();

var sql = ‘"Select A,B,C where B=’’+username+’’"’;

var qry = ‘=IFERROR(query(’ + rg + ‘,’ + sql + ‘),"")’;

var ts = ss.insertSheet();

var setQuery = ts.getRange(1,1).setFormula(qry);

var getResult = ts.getDataRange().getValues();

var getPWD = ts.getRange(1,3).getValues();

var getFullName = ts.getRange(1,1).getValues();

ss.deleteSheet(ts);

if (getResult!=""){

if (getPWD==password){return ContentService.createTextOutput(getFullName);}

else { return ContentService.createTextOutput("ERPWD");}

}

else{return ContentService.createTextOutput(getResult);}

}

}

blocks:

[Peter (Moderator)]

Did you follow the advise @TimAi2 gave you?

Login page using google sheets

[Antonis_Zairopoulos]

Im still trying to find what is wrong with my code because its easier for me to implement another future I wanted to with that code. If I don’t find it I will try this design, its just that it seems too complicated for me. Thank you

[TimAi2 (metricrat)]

You are trying to “squeeze a quart into a pint pot”. You have repurposed my CRUD script to handle a register/login script which is probably not the best method to use for what you want.

I had a look at your script and your blocks, there was nothing immediate that I could see that was preventing the use of letters in your password, but I could be missing something.

Try with the method i provided, and see if that helps…

[Antonis_Zairopoulos]

ok so im following your instructions but I’m a little confused, I have 3 screens, one for the registration, one for the login and one for the logout. Which blocks go where? Im so sorry if this is obvious I’m really new to this. Thank you in advance

[Antonis_Zairopoulos]

so i guess im out of luck, but to the drawing board (or ctrl c,v) I suppose

[Antonis_Zairopoulos]

on second thought ill just have the report section be the already logged in section so i just need help with the other part

[Antonis_Zairopoulos]

excuse my inability to understand these things, I now get it. I put them all in one screen. However Heres what I’m trying to do. I want in the register section to be a box where you type in your name and when you log in it displays smth like Welcome Tim and I also don’t want the report section. Something I also want to do after I get that up and running is have a counter that when you register sets to 0 and then when you scan a qr code it goes up by one (I’ve already done that) and it being auto updated (sent to the sheet) when you scan the code. I have no idea how to set all that up, I had hope but I don’t understand a big portion of your coding. Any thoughts? Thank you for taking time out of your life to help me out

[Antonis_Zairopoulos]

nothing is working, this is the most frustrating thing I’ve ever done

[Antonis_Zairopoulos]

is there any way i could achieve letters on the last one? I think thats my only real option here

[TimAi2 (metricrat)]

I have reworked my example to hopefully make it easier. Try this:

function doGet(e) {
 
  var ss = SpreadsheetApp.openById('<sheetID>');
  var sheet = ss.getSheetByName("Logins"); 
  
  if ( e.parameter.func == "register" ) {
    var user = e.parameter.user ; 
    var passwd = e.parameter.passwd ;
    sheet.appendRow([user,passwd]);
    msg = "New User Added";
  } 
  
  else if ( e.parameter.func == "login" ) {
    var msg = "Incorrect Login";
    var user = e.parameter.user;
    var passwd = e.parameter.passwd;
    var loginData = sheet.getDataRange().getValues();
    for ( var i = 0; i < loginData.length; ++i ) {
      if ( user === loginData[i][0]  && passwd === loginData[i][1] ) {
        msg = "Logged In";
    }
  }
  }
  return ContentService.createTextOutput(msg);
}

[72erdelyi (3dmixer)]

Oh buddy. This is such a bad idea. It will be extremely slow when you reach 2-3k users. Why don’t you go down mysql route? It is faster, you can make all sorts of queries and way faster.

[GuzJadab (Zaid Ramadhan)]

Try to change your format data on google sheet by selecting your range data, then select Format menu -> Number -> Plain Text

[Antonis_Zairopoulos]

its set to automatic, like the username and the name. The other two work just fine with text but the password accepts only numbers for some strange reason. If I set it to plain text wont it accept only text and not numbers? Ill definitely try it, Thanks for the suggestion

[Antonis_Zairopoulos]

Ill first try the suggestion below and if that doesnt work ill try yours, Thank you so much for re-writing the whole thin you guys are truly the best

[dora_paz (Dora)]

Did you by any chance set input type - Number Password in text box’s properties ?

[Antonis_Zairopoulos]

no its normal, the guy above solved my problem but thank you for your suggestion

[Antonis_Zairopoulos]

So i have it up and running but I have yet another problem. the login system works fine, but I don’t know how to update a cell when its already created. I thought that its would work fine but it didn’t. Any thoughts?

[Still-learning (1-2-3-4...)]

If you dont mind, Can you share your script code ?

[Antonis_Zairopoulos]

sure, here you go

if (e.parameter.func == “Counter2”) {

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

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

var username = e.parameter.username;

 var counter = e.parameter.counter;

let term = username;

let data = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1, 2, 2000).getValues();

let row = data.findIndex(users => {return users[0] == term});

var num = row+1; //because Logger.log() takes too long

sh.getRange(num, 4).setValue(counter);

return ContentService.createTextOutput("SUCCESS");

}

//}

}