Problem with listing records from google sheet after login

[NEWBIE]

Hello I'm a complete newbie about coding I just copy the codes and script from google or youtube then consolidate it to make the app. I want to build a simple platform where certain list of people can update their own vaccination record from google sheet.

I manage to create a login option and generated the list from google sheet but I completely don't know how to make it so that only the user record will be filtered out by the app.

I also having trouble in clearing the tinydb the cache so the when they close the app without using the button they will be redirected to login page again I used the taifuntool but it got stock from the login page.

and also I cant delete one of the screen I keep getting an error message then redirected to report page

here's the aia file and the spreadsheet
Vacupdate.aia (266.8 KB)

Authentication

I apologize for the messy code and blocks. Thank you.

//------------------------------------------------------------------------------------------------------------------------------------
function doGet(e) {

  return Authetication(e);

}

function doPost(e) {

  return Authetication(e);

}

function Authetication(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 Fullname = e.parameter.Fullname;

      var sql = '"Select A where A=\''+Fullname+'\'"';

      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.Phone, 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 Fullname = e.parameter.Fullname;

var Password = e.parameter.Password;

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

var sql = '"Select A,B,C,D where A=\''+Fullname+'\'"';

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,4).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);}

}

}

//----------------------Query----------------------------------------

function doGet(e) {

  

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

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

  var fn = e.parameter.FN;

  var rg = sh.getDataRange().getValues();

  

  // Create/Add new record, using comma separated values

  if ( fn == 'CREATE' ) {

    var data = e.parameter.DATA.split(',');

    sh.appendRow(data);

    return ContentService.createTextOutput("New record created");

  }

  

  // Reads/Returns all data as a stringified JSON List

  else if ( fn == 'READ' ) {

    return ContentService.createTextOutput(JSON.stringify(rg));     

  }

  

  // Edit/Update existing record, requires index/row and current col1 to match

  else if ( fn == 'UPDATE' ) {

    var index = e.parameter.INDEX;  //index in list

    var col1 = e.parameter.COL1;  // current/existing value of col1 - it could be replaced...

    var data = e.parameter.DATA.split(','); //new data

    var range = sh.getRange((parseInt(index)+1),1,1,data.length);

    for (var i = 0; i < rg.length; i++ ) {

      if ( index != undefined && i == index && col1 == rg[i][0] ) {

        range.setValues([data]);

      } 

    }

    return ContentService.createTextOutput("Record updated");    

    }

  

  // deletes a single record (and its row) from sheet. Requires row index and col1 to match

  else if ( fn == 'DELETE' ) {

    var index = e.parameter.INDEX;  //index in list

    var col1 = e.parameter.COL1;  // current/existing value of col1 - it could be replaced...

    for (var i = 0; i < rg.length; i++ ) {

      if ( index != undefined && i == index && col1 == rg[i][0] ) {

        sh.deleteRow(parseInt(index)+1);

      } 

    }

    return ContentService.createTextOutput("Existing record deleted");    

   }

    

  // outputs results from SQL query of all data  

  else if ( fn == 'QUERY' ) {

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

    var sql = e.parameter.SQL;

    var qry = '=query(' + rgq + ';\"' + sql + '\";1)';

    var ts = ss.insertSheet();

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

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

    ss.deleteSheet(ts); 

    return ContentService.createTextOutput(JSON.stringify(getResult));

  }

 

}

[TIMAI2]

Possibly trying to do too much all at once

Could I suggest that you start again, breaking your app into two separate apps, one for login/registration and one for a CRUDII activity.

Learn how to successfully build both of these, and get them working how you want them to. Then you can work on combining the two. It may be that you can maintain the registration / login just on the device, and use the settings there to run queries on the data, just for that user.

[NEWBIE]

Thanks for immediate reply TIM, almost all the answer from post I came across came from you and I read a lot of your suggestion. Actually I manage to make the login and the CRUDII works on individual project and now trying to merge them. But since CRUDII is the most important part I'll focus on it for now.

Can you give me a suggestion regarding how I can make the the CURDII returns only the specific row with the same tag when the app initialized instead of all the records?

I was looking for the answer in google since yesterday but cant find an answer

// Reads/Returns all data as a stringified JSON List
else if ( fn == 'READ' ) {
return ContentService.createTextOutput(JSON.stringify(rg));
}

I was thinking if possible to edit this script so the query can be done before the app fetch the records.
that way I don't have to edit the blocks because I'm trying it now but I have no idea how it works. I don't have much knowledge in programing and only following tutorial from post or youtube and now I'm stuck.

Thanks a lot in advance.

[TIMAI2]

You should use the query function (fn == 'QUERY') to SELECT the values in the columns you want to return. For example, if you want to return all the records for the Household Id: 1-03718552 then your select query would be:

SELECT * WHERE B = '1-0378552'

[NEWBIE]

function doGet(e) {

var ss = SpreadsheetApp.openById(e.parameter.ID);
var sh = ss.getSheetByName(e.parameter.SH);
var fn = e.parameter.FN;
var rg = sh.getDataRange().getValues();

if ( fn == 'QUERY' ) {

var rq = sh.getName() + "!" + sh.getDataRange().getA1Notation();
var sql = '"SELECT * WHERE B = '1-0378552'"' 
var qry = '=query(' + rgq + ';\"' + sql + '\";1)';
var ts = ss.insertSheet();
var setQuery = ts.getRange(1,1).setFormula(qry);
var getResult = ts.getDataRange().getValues();
ss.deleteSheet(ts); 

return ContentService.createTextOutput(JSON.stringify(getResult));

}

is this how you write it? I have know idea if its correct. Thank you.

[TIMAI2]

You have a typo:

should be:

if ( fn == 'QUERY' ) {
    var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation();
    var sql = e.parameter.SQL;
    var qry = '=query(' + rgq + ';\"' + sql + '\";1)';
    var ts = ss.insertSheet();
    var setQuery = ts.getRange(1,1).setFormula(qry);
    var getResult = ts.getDataRange().getValues();
    ss.deleteSheet(ts); 
    return ContentService.createTextOutput(JSON.stringify(getResult));
  }

[NEWBIE]

Thanks a lot. I will try it now.

[NEWBIE]

Tried doing the query in the original CRUDII
and it sends something like this does that mean its working since its sent out a result? but only the headers appear.

and which part of blocks should I change, when I replace the block that refers to READ in view all it doesn't work.

[TIMAI2]

Try

SELECT * WHERE B CONTAINS '1-0378552'

[NEWBIE]

nothing sent out I try to figure it out again tomorrow thanks for the reply.

[TIMAI2]

Is it an underscore and not a hyphen ?

SELECT * WHERE B = '1_0378552'

I am testing with the crudqII aia from my site, the select query is working fine there.

[NEWBIE]

Ok I'll try that line.

[NEWBIE]

May be I miss something out, for now I'll revisit all the blocks and script. thank you TIM.

[NEWBIE]

Hello TIM Thanks a lot for the HELP you pointed me in the right direction and I manage to figure it out using the code from may login project how it fetch and the user and password string so I done the same.

Script

else if ( fn == 'READ' ) {
var SQL = e.parameter.SQL
var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation();
var sql = 'Select * Where B=''+SQL+''';
var qry = '=query(' + rgq + ';"' + sql + '";1)';
var ts = ss.insertSheet();
var setQuery = ts.getRange(1,1).setFormula(qry);
var getResult = ts.getDataRange().getValues();
ss.deleteSheet(ts);
return ContentService.createTextOutput(JSON.stringify(getResult));
}

Blocks

I just assigned the value of the box in the SQL.

And the query problems is because of the initialization of screen, Before the user even inputted the text the crud automatically run which make the SQL value to Null so I move it after login.

and the bug screen that I'm unable to delete is because of the customweb extension that didn't get included when I merge the two project all did is to reupload it.

And finally regarding the login problem when the user close the application incorrectly which makes them bypass the login screen. I just copy how the crud enable and disable the screen I built the Login on top of the CRUD.

So Thanks a lot....

Now I'm thinking how to put encryption in the password is it possible?

[TIMAI2]

See here for encrypted (encoded) login/registration for google sheets

• Google Sheets - Register & Login

[NEWBIE]

Ok sir. Thanks alot.

[NEWBIE]

Good day Sir I just noticed a problem regarding the script can you give a suggestion?

When I tested it with large records the rows get overwritten.

//-----------------------------------------------------------------------
else if ( fn == 'READ' ) {
var SQL = e.parameter.SQL
var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation();
var sql = 'Select * Where B=''+SQL+''';
var qry = '=query(' + rgq + ';"' + sql + '";1)';
var ts = ss.insertSheet();
var setQuery = ts.getRange(1,1).setFormula(qry);
var getResult = ts.getDataRange().getValues();
ss.deleteSheet(ts);
return ContentService.createTextOutput(JSON.stringify(getResult));
}

// Edit/Update existing record, requires index/row and current col1 to match

else if ( fn == 'UPDATE' ) {
var index = e.parameter.INDEX; //index in list
var col1 = e.parameter.COL1; // current/existing value of col1 - it could be replaced...
var data = e.parameter.DATA.split(','); //new data
var range = sh.getRange((parseInt(index)+1),1,1,data.length);
for (var i = 0; i < rg.length; i++ ) {
if ( index != undefined && i == index && col1 == rg[i][0] ) {
range.setValues([data]);
}
}
return ContentService.createTextOutput("Record updated");
}
//---------------------------------------------------------------------------------------------------

When Query result have 5 rows then I updated the record, The 5TH row gets over written.

I know I have to change the script in UPDATE and assign value in the e.parameter.INDEX and e.parameter.COL1 but how to assign it.

I tried doing this but its not working.

[TIMAI2]

This is because you are using the index of the query output, not the index of the record. You need the row number for that, or an index in your data.

[NEWBIE]

can you tell me how to do that?

what I'm trying now is to use the original CRUD II Script which is this.

// Reads/Returns all data as a stringified JSON List
else if ( fn == 'READ' ) {
return ContentService.createTextOutput(JSON.stringify(rg));
}

then filter out the list by adding block like this

the result is this one

But the problem still appearing in the update portion it returns different name

only setLVShow is being code so which part update the records?

[NEWBIE]