Problem with listing records from google sheet after login

[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]

[TIMAI2]

You will probably need to identify the unique field in your record in order to check for this when you update

[NEWBIE]

I'm using the original CRUD II sheet , can you give example how I can update only those from state of Florida.

I manage to figure out how to filter out the list view but I cant find the specific blocks that update the record.

I think its something to do with these two blocks but how to arrange the blocks if I use the column 6 as unique identifier?

[TIMAI2]

As before, you need to know in which row in the spreadsheet data the record is.

[NEWBIE]

Hello I need help how to edit a script to match the row and column of the data sent from my app

I made an app using CRUDII script and edited a part of script so the app will fetch only the specific records.

which become the origin of the problem when the record gets updated

I used this script to query the records.

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 == '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));
}
//---------------------------------------------------------------------

Now when I updated the record using my app it reads the query output of my READ so when the Sheet have 20 rows and my query output got 4rows , it over writes the 1st-4th rows from my sheet instead of the original records.

So what I want to do is to edit this script

//---------------------------------------------------------------------
// 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");
}

I hope you can help me. Thank you in advance.

[TIMAI2]

The CRUDIIQ example was designed to return all the data from the spreadsheet, so that the user could work on the data in the app. in this way it is possible to identify the correct index/row of the record in the spreadsheet from the index of the record in the AI2 list.

The update script uses the record index and the value of the item in the current Column A (which should be unique) to ensure that the correct row is updated.

If you return a query from the spreadsheet for a single or multiple records, you will create a new list with indexing that does not match the spreadsheet indexing.

To do what you want to do, you will probably need to modify your spreadsheet database to include an index (Column A) that is unique, then you can use this value in a "query update" script to identify the correct record.

[NEWBIE]

Thank you for the explanation sir. I tried going back from the scratch and follow your advised to put the unique identifier at column A and used the original code and now I'm trying to figured out how to filter the data from within the blocks.

But still I'm having a problem in getting the right index. You see, what I wanted to accomplish is when someone login the app it will filter out all the records of his or her family members and each one of family members have a unique identifier.

But when I filtered the list from the listview, the index also change from the index of the listviewselection so when I update the record it doesn't match the original index from the datalist.

Is this a wrong way of filtering the listview records?

If there are similar topic or guide for this I hope you can link it here so I can study it.

Thanks a lot for your patience and advise.

[TIMAI2]

When you add list items to the lvShow list, make sure you include the index of the item from the original list: list=data, index= index of item in data. This way you can reference back to the original list.