Jeszcze raz logowanie przy użyciu google sheet

[Piowar]

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]

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

[TIMAI2]

Please show your full responseContent

How to show raw responseContent from your app when debugging

Many developers often get stuck when trying to handle the response content from an online server after making a GET request using the web component. Those trying to help often ask to see the raw responseContent being returned. When using companion this can be copied and pasted to the community for those trying to help to use with debugging. This is how you might return that information / data to a label. BLOCKS [image] DATA Do It Result: ""Name","Date of Birth","Age" "Adah","06/04/1999","2…

See here:

• Query Any Google Sheet with a Web App

I suggest you get this method working then modify it.

[Piowar]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

Did my aia work for you ?

[Piowar]

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

[TIMAI2]

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

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

• Correctly Deploy a Google Apps Script Web App for AI2

• New Versions for New Google Script Editor

[TIMAI2]

@TIMAI2

Did my aia work for you ?

@Piowar

Unfortunately NO.

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

[Piowar]

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

[TIMAI2]

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]

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 !