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 ?
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);
}
}
}
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);
}
}
1 Like
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!
Did my aia work for you ?
1 Like
TIMA2, I apreciate your patience and help.
Unfortunately NO. Please read my previous answer.
Can you please change your spreadsheet to edit access for anyone for testing?
Also, please show how you are deploying your web app....
1 Like
What, even without making any changes, and just running it as it is (with getActive()) ??
I have changed the permision to edit for all who have the link to the spreadsheet.
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.
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 !
Let me see your aia project, perhaps something there...
and it works for me (it is using my script url)
You need to create your own script aligned with your spreadsheet
Then send over the full script and aia project
Again, ensure that your script is correctly deployed and you use the correct script url.
function doPost(e) {
return authenticate(e);
}
function authenticate(e) {
if (e.parameter.func == "Login") {
//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");
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);
}
}
queryEmailPass (2).aia (2.2 KB)
Can you confirm that you have the escape \ characters in your script on this line:
var sql = '\"Select A,B,C where B =\'' + email + '\'\"';
(they could have been lost when you posted your script on to the topic)
Ah, I have edited your post, I see them now 
I too, am at a loss as to why this is not working for you.
Go onto your spreadsheet
Create a new Sheet
in cell A1 put the following:
=query(Sheet1!A1:D5,"Select A where B = 'piotrpio@gmail.com'")
It should return:
FullName
Piowar96
this is what the script should be doing....
Could you also please confirm that you have followed all the steps here:
and that your script is set to be accessed by Anyone and not Anyone with a google account
U mnie zwraca #ERROR!
Yes .I confirm