Original index from sheet after using Query functions from web app script

Also needed for understanding the variables:

  • If it is a list or table, how many columns, and what are in the columns?
  1. Table = Holds all the original data, it gets it's value from the ReadAll function in another script in my google sheet (Table, 4 Columns)
    image

  2. FullData = Holds tables information without the first row information(the column names), this should be used to compare the values in the queried and original (Table, 4 Columns)
    image

  3. FilteredIndexes = to keep all the filtered indexes after it knows which index the data was extracted from (List) The values in there depends on what data it receives. eg: 2 event on the same day different time, event 1 is in index 3 then event 2 in index 5 so the list should have 2 and 5

4.QueryData = The queried data extracted from the sheet filtered by date after a date is selected (Table, 2 Columns the ones highlighted) the data depends on what date they pick....
image

5.FilteredIndex = This is where I then get the extracted original index to be used to read (because there is an edit page that seperate the data into their respective boxes) update or delete some data

Since you are using @TimAI2 code in your web sheets, he is the guy to check your URLs.
Besides advising a Do it on the return content from your Web Gets, I would only slow you down from here on in.
Assigning to @TIMAI2

that's okay, thank you so much for the tips and advices :relaxed:

While we wait for Tim, I will throw some ideas blindly against the wall ...

  • You are comparing dates for your filter. Is this a new table? Might there be a difference between column formats (String vs Date) that is interfering with your filter?
  • Your procedures have a distressing lack of inputs and outputs. That makes them hard to trace at a glance. Good procedures get their input only from their parameters, and return their values via an output nipple or into a parameter (not as good.) Trying to trace procedure effects through global variables leads to madness.

Another thing to check ...

  • Have you verified that your global variables hold what you think they should hold, using the Blocks Editor Do It facility?
    FAQ Section: Debugging

The formats are all in string in my spreadsheet. I can actually get the filtered output that I want after selecting the date to show on the listview. Just the output in the edit page is not what as I desired because after it filters when I select it, it will read as index 1 so it will extract the first row of data in the spreadsheet.

I know, i'm actually still new to this but because everything was already basically done I didn't want to tamper with the blocks too much. So when I decided to filter it using query, I knew what was needed but when I tried it, it didn't work out so now here we are hahaha :sweat_smile:

Yes, I have tried to use the Do It function, for most but I was not quite sure how to use do it on the comparing part of where it will help to try and extract the original indexes...

Here are good places to apply Do It after you have tried to run a query and the result has arrived and been safely stashed somewhere ...

  • The JOIN that built the URL, to make sure the URL made sense. Some URLs break if you include special characters, so you have to use one of the Web blocks (URI Encode/Decode?)to turn the special characters into %nnn mush. Dates have slashes in them, right?
  • The global variable that you stashed the responseContent into when it arrived, to check for error messages and other surprises.

Here are some ways to go wrong with indexes ...

  • Are you dealing with a column value like =rowid() or a relative vertical position ?
  • How does that index change after another row might have been deleted? Is it unchanged, like shelf numbers, or does it collapse to fill gaps, like a stack of plates after you yank out a plate from the middle?
  • You have a copy of a table with its header row removed. How does that affect index calculation and use going back to the original table?

should fill in the gap once it is removed, once header is removed the first row after the header becomes the first row index. I actually always get confused on whether it is reading as a row id row columns because using select item from list, index. doesn't really specify whether it is a column or row. so I stuggle but I try to slowly understand it. As of right now i'm trying to compare what I did for my previous project to this one to see whether I can I dentify where I went wrong....

I like Divide and Conquer as a debugging technique.
Try to isolate the parts that work from the parts that don't work, then
take the parts that don't work and try to break them further down into parts that work and don't work. (Rinse and repeat).

If i understand things correctly, if you want to do a date search on the data in your table, and return the first two columns of the data AND know which index it has in the 'list', then you can do this with a google apps script function, much in the same way as you have done with other elements of your work.

Using this google sheet table

image

The index 1 will be row 2, index 2 will be row 3, and so on.

If we assume that the google apps script web app is bound to the spreadsheet, that the sheet name is Sheet1, and that we can use a doGet(e) request in the web app, a function like this should return one or more entries (index & column 1 & 2 only) as a stringified JSON list:

function doGet(e) {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('Sheet1');
  var rg = sh.getDataRange().getValues();
  var rows = [];
  var date = e.parameter.date;

  for ( var row = 1; row<rg.length;row++ ) {
    if ( rg[row][1] == date ) {
    rows.push([parseInt(row),rg[row][0],rg[row][1]]);
    }
  }
  return ContentService.createTextOutput(JSON.stringify(rows));
}

You blocks would need to be

blocks (8)

and, as in the blocks, if the date parameter = 17/10/2020, then the responseContent will be:

[[6,"Saturday","17/10/2020"]]

with "6" being the index of the the item requested.

If there were to be more matches for the date, then a longer JSON list would be returned, for example:

[[6,"Saturday","17/10/2020"],[9,"Saturday","17/10/2020"],[12,"Saturday","17/10/2020"]]

Does this help ?

alright, I'll try this first, hopefully no errors :crossed_fingers: i'll get back to you once i've test run everything

If you already have a doGet(e) in your web app for that spreadsheet (if I remember correctly, this data was in a separate spreadsheet), you can include an if/if/else statement and add a defining parameter for each function. for example:

doGet(e)



if (e.parameter.func == getDate ) {

//run function
getRowsFromDates(e.parameter.date);

} else if (e.parameter.func == getName ) {

// run function
getRowsFromNames(e.parameter.name);
}

blocks (9)

nope, my get name is in doPost instead of doGet so I think i'm good maybe? unless an error comes up

That is what you said yesterday :slight_smile: and it doesn't make sense. You are either making a GET request or a POST request.

Some errors showed up...
image

Here is the block:

I added some data in my spreadshit with the exact same date to see how it handles more than just one data with the same date. It does not show it though? I'm not sure how to sperate them. I tried to put it as a list then use for each item in the list thing but it doesnt work, just errors

The web app is returning a list of lists, you will have to iterate over each item in the list of lists to return all the data (using my example data):

returns

image

I swear I tried this earlier but maybe not im not even sure anymore, so many sleepless nights :joy:

I have another question. So I have one part where for example we edit a resident's name from the elderly's sheet, I want it to also update the names in both the medicine sheet and appointment sheet.

I have a rough idea of how it should be but I just wanted to confirm whether the code in the app script would be right for it, here is my script as for now for both the medicine and appointment sheets:

Medicine:

function doGet(e) {
  return ManageSheet(e);
}
function doPost(e){
  return ManageSheet(e);
}

function ManageSheet(e){
  //Read All Records
  if( e.parameter.func == "ReadAll"){
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    var rg = sh.getDataRange().getValues();
    var outString = '';
    for(var row=0; row<rg.length; ++row){
      outString += rg[row].join(',')+'\n';
    } 
    return ContentService.createTextOutput(outString).setMimeType(ContentService.MimeType.TEXT);
  }
 
  
  //Delete Single
  else if (e.parameter.func=="Delete"){
    var record = e.parameter.id;
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    sh.deleteRow(parseInt(record)+1);
    return ContentService.createTextOutput("Success, requested action completed");
  }
  
  //Read Single Record
  else if(e.parameter.func == "ReadRecord"){
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    var rg = sh.getDataRange().getValues();
    var outString = '';
    outString += rg[parseInt(e.parameter.id)].join(',');
    return ContentService.createTextOutput(outString).setMimeType(ContentService.MimeType.TEXT);
  }
  
  //Update Single Record
  else if(e.parameter.func == "Update"){
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    var data = [[e.parameter.medicine, e.parameter.time]];
    sh.getRange("B"+(parseInt(e.parameter.id)+1)+":C"+(parseInt(e.parameter.id)+1)).setValues(data);
    return ContentService.createTextOutput("Success");
  }
  
  //Create new record
  if(e.parameter.func == "Create"){
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    var data = [e.parameter.name,e.parameter.medicine, e.parameter.time];
    sh.appendRow(data);
    return ContentService.createTextOutput("Success");
  }
}

Appointment:

function doGet(e) {
  return ManageSheet(e);
}
function doPost(e){
  return ManageSheet(e);
}

function ManageSheet(e){
  //Read All Records
  if( e.parameter.func == "ReadAll"){
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    var rg = sh.getDataRange().getValues();
    var outString = '';
    for(var row=0; row<rg.length; ++row){
      outString += rg[row].join(',')+'\n';
    } 
    return ContentService.createTextOutput(outString).setMimeType(ContentService.MimeType.TEXT);
  }
  
  //Delete Single
  else if (e.parameter.func=="Delete"){
    var record = e.parameter.id;
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    sh.deleteRow(parseInt(record)+1);
    return ContentService.createTextOutput("Success, requested action completed");
  } 
  
  //Read Single Record
  else if(e.parameter.func == "ReadRecord"){
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    var rg = sh.getDataRange().getValues();
    var outString = '';
    outString += rg[parseInt(e.parameter.id)].join(',');
    return ContentService.createTextOutput(outString).setMimeType(ContentService.MimeType.TEXT);
  }
  
  //Update Single Record
  else if(e.parameter.func == "Update"){
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    var data = [[e.parameter.name,e.parameter.date, e.parameter.time, e.parameter.hospital]];
    sh.getRange("A"+(parseInt(e.parameter.id)+1)+":D"+(parseInt(e.parameter.id)+1)).setValues(data);
    return ContentService.createTextOutput("Success");
  }
  
  //Create new record
  if(e.parameter.func == "Create"){
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    var data = [e.parameter.name,e.parameter.date, e.parameter.time, e.parameter.hospital];
    sh.appendRow(data);
    return ContentService.createTextOutput("Success");
  }
}

Well both are actually identical only some parameters are different.

So lets say I want to update the names do I change the update part to ??:

else if(e.parameter.func == "UpdateName"){
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    var data = [[e.parameter.name]];
    sh.getRange("A"+(parseInt(e.parameter.id1)+1)).setValues(data);
    return ContentService.createTextOutput("Success");
  }

Buuuutt then thinking about it, that wouldn't work because it needs the indexes of both the medicine and appointment sheet of where the resident's name is and their names are repeated multiple times in those lists to update the name. Maybe a query function is a better approch then but how to update it using script again? can you help please, I believe it'll be more or less just like the delete query you helped me with before but for update