Problem with listing records from google sheet after login

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