Also needed for understanding the variables:
- If it is a list or table, how many columns, and what are in the columns?
Also needed for understanding the variables:
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)
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)
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....
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
While we wait for Tim, I will throw some ideas blindly against the wall ...
Another thing to check ...
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
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 ...
Here are some ways to go wrong with indexes ...
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
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
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 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);
}
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 and it doesn't make sense. You are either making a GET request or a POST request.
Some errors showed up...
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
I swear I tried this earlier but maybe not im not even sure anymore, so many sleepless nights
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