Jeszcze raz logowanie przy użyciu google sheet

Piowar · December 16, 2021, 11:01am

Oto mój skrypt do logowania. W arkuszu googla kolejność kolumn user(full name), email, hasło
po podaniu poprawnych danych logowania tj email i hasła. Respose type daje 200. Zapytanie nie zwraca błedu oraz fullName var getFullName =ts.getRange(1,1).getValue();

function Authentication(e)

{

if (e.parameter.func == "Login")

{

var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1-WJLtFfREfV-8N6PC4bBSBX-3-7iFZV95nzToWwHEHQ/edit#gid=0");

var sh = ss.getSheetByName("Sheet1");

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 getResult = ts.getDataRange().getValues();

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

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

ss.deleteSheet(ts);

if (getResult!="")

{

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

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

}

 else {return ContentService.createTextOutput(getResult);}

}

Piowar · December 16, 2021, 11:02am

Bardzo proszę o pomoc , co robię żle.

TIMAI2 · December 16, 2021, 1:19pm

Please show your full responseContent

See here:

Query Any Google Sheet with a Web App

I suggest you get this method working then modify it.

Piowar · December 16, 2021, 10:49pm

W google sheet są 3 rekordy
w kolumnie B pierwszy wiersz piotrpio@gmail.com
drugi wiersz hay@gmail.com
trzeci wiersz p.warczyk.upcpoczta.pl
W przeglądarce
https://script.google.com/macros/s/AKfycby-TRCnxIn8-PZ1KdCRR2MS5FuFFeQdQ4JQQ-gC-QEAAnPStoZpS4g2qbLsq6kzGZGA/exec?ID=1-WJLtFfREfV-8N6PC4bBSBX-3-7iFZV95nzToWwHEHQ&SH=Sheet1&SQL=select * where B ='p.warczyk@upcpoczta.pl'

zwraca dwa wiersze a nie jeden !

Piowar96,piotrpio@gmail.com,jola456,607612428
piterek,p.warczyk@upcpoczta.pl,kropka123,607612428

TIMAI2 · December 16, 2021, 11:45pm

You omitted the header row, therefore the first row Piowar96,piotrpio@gmail.com,jola456,607612428 is being returned as the header.

Insert a row at the top and add: FullName,Email,Password,Number (or the equivalents in Polish)

Piowar · December 17, 2021, 4:44pm

Dziękuję bardzo za dotychczasową pomoc. Po dopisaniu nagłówków kolumn w google sheet teraz zwraca dwa wiersze w tym pierwszy to nazwy kolumn ale moja aplikacja dalej nie działa . W szczególności Web1.gottext blok nie sygnalizuje błędnego hasła i nie zwraca FullName wywołując następny ekran Aplikacja

Nie ma już żadnego pomysłu co może być żle

TIMAI2 · December 17, 2021, 4:51pm

The message returned is:

The script was executed but it did not return any value

Which means that the system is happy with the syntax of your script, it can execute it, but that for whatever reason it does not return a value.

I guess I will have to create my own version and work out what the script is doing. I am a little concerned about the two return lines, it is usually best to set a variable in the if/else conditions and just have one return at the end.

Are you using the same spreadsheet content to test your own script ?

Piowar · December 17, 2021, 5:10pm

Używam stale tego samego google sheet.Co ciekawe registration part of script ze sprawdzaniem czy istnieje już wprowadzany email działa poprawnie.Będę bardzo wdzięczny za wszelką pomoc.

TIMAI2 · December 17, 2021, 6:03pm

Try using this script, just replace as you see it from this point, if (e.parameter.func == "Login") to the end :

if (e.parameter.func == "Login")

{

var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1-WJLtFfREfV-8N6PC4bBSBX-3-7iFZV95nzToWwHEHQ/edit#gid=0");
var sh = ss.getSheetByName("Sheet1");
var email = e.parameter.email;
var password = e.parameter.password;
var message = "";

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 getResult = ts.getDataRange().getValues();
var getPWD = ts.getRange(2,3).getValue();
var getFullName =ts.getRange(2,1).getValue();
ss.deleteSheet(ts);

if ( getResult != "" )  {
 if ( getPWD == password ) {
   message = getFullName;
 }
 else  {
   message = "ERPWD";
 }
}
 else {
   message = getResult;
 }
return ContentService.createTextOutput(message);
}
}

I am guessing you got the original script from HayKel on youtube, who copies my scripts but doesn't really understand what is going on, so they generally do not work, and then I end up having to fix his mistakes

I have had to escape some of the quotes in the sql and change the range values from 1 to 2 (because of the header row). I also edited the conditional section using a variable message, with just the one return right at the end. This should now work for you as expected.

Piowar · December 19, 2021, 5:31pm

Very sorry to inform you TIMA2 but the script you gave me seems to not work. The block web1.gotext ends with .. script was executed but it did not return any value the same like before. May be something is wrong with this block

. Any idea ?

Piowar · December 19, 2021, 5:36pm

Below please find the whole script after changes
function doGet(e)

{

if (e.parameter.func == "Create")

{

var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1-WJLtFfREfV-8N6PC4bBSBX-3-7iFZV95nzToWwHEHQ/edit#gid=0");

var sh = ss.getSheetByName("Sheet1");

var email = e.parameter.email;

var msg='';

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

var sql ='"Select B where B =''+ email +''"';

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.fullname,e.parameter.email,e.parameter.password,e.parameter.phone];

sh.appendRow(data);

return ContentService.createTextOutput(getResult);

} // if (e.parameter.func == "Create")

if (e.parameter.func == "Login")

{

var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1-WJLtFfREfV-8N6PC4bBSBX-3-7iFZV95nzToWwHEHQ/edit#gid=0");

var sh = ss.getSheetByName("Sheet1");

var email = e.parameter.email;

var password = e.parameter.password;

var message = "";

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 getResult = ts.getDataRange().getValues();

var getPWD = ts.getRange(2,3).getValue();

var getFullName =ts.getRange(2,1).getValue();

ss.deleteSheet(ts);

if ( getResult != "" ) {

if ( getPWD == password ) {

message = getFullName;

}

else {

message = "ERPWD";

}

else {

message = getResult;

}

return ContentService.createTextOutput(message);

}

TIMAI2 · December 19, 2021, 9:08pm

I am sorry to hear you could not get it working. Here is an example aia project, based upon a script and spreadsheet with your sample data that works for me. The script is bound to the spreadsheet. Try it.

queryEmailPass.aia (2.2 KB)

function doPost(e) {
return authenticate(e);
}

function authenticate(e) {

if (e.parameter.func == "Login") {

var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName("Sheet1");
var email = e.parameter.email;
var password = e.parameter.password;
var message = "";

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 getResult = ts.getDataRange().getValues();
var getPWD = ts.getRange(2,3).getValue();
var getFullName =ts.getRange(2,1).getValue();
ss.deleteSheet(ts);

if ( getResult != "" )  {
 if ( getPWD == password ) {
   message = getFullName;
 }
 else  {
   message = "ERPWD";
 }
}
 else {
   message = getResult;
 }
return ContentService.createTextOutput(message);
}
}

Piowar · December 19, 2021, 11:42pm

Sorry, I have got the ERPWD

The only change in the script is:
//var ss = SpreadsheetApp.getActive();

//var sh = ss.getSheetByName("Sheet1");

var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1-WJLtFfREfV-8N6PC4bBSBX-3-7iFZV95nzToWwHEHQ/edit#gid=0");

var sh = ss.getSheetByName("Sheet1");
Please help. It seems as easy as it is but still does not work!

TIMAI2 · December 20, 2021, 12:01am

Did my aia work for you ?

Piowar · December 20, 2021, 8:47am

TIMA2, I apreciate your patience and help.
Unfortunately NO. Please read my previous answer.

TIMAI2 · December 20, 2021, 9:20am

Can you please change your spreadsheet to edit access for anyone for testing?

Also, please show how you are deploying your web app....

TIMAI2 · December 20, 2021, 10:22am

What, even without making any changes, and just running it as it is (with getActive()) ??

Piowar · December 20, 2021, 12:59pm

I have changed the permision to edit for all who have the link to the spreadsheet.

TIMAI2 · December 20, 2021, 1:09pm

I have tested my aia project and with an altered script to point to your spreadsheet.
This works OK and returned the full name for the email/password.
Nothing wrong with the app, nothing wrong with the script, nothing wrong with the spreadsheet.

Can you please try again at your end, and report on progress. Be sure you have deployed your script correctly and that you are using the correct script url.

Piowar · December 20, 2021, 2:21pm

Deployment is OK for 100 percent. I made a quite new script file. Aplicacation returns ERPWD for each record of the spreadsheet ( I added a new record to the googlesheet) . I tried to use Unicode for edit2,text but without success. I cant send you apk file. Additionaly I used your application quryanygooglesheet and select * returns the whole content of googlesheet OK. I have no slightest idea what is wrong !