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

[Allysha_April]

Hello, so I have a schedule list where it will show you what events are being held on a specific date. I used a query function in google sheets web app script whilst also using the syntax "SELECT * WHERE B MATCHES 'date' in the blocks. So here is where the problem begins. These data should be able to be deleted or edited but in order to do that I need their original indexes. I had a project similar to this and tried to replicate it but unfortunately it didn't work. Because I can't exactly get the original index so even the queried data doesn't show up once I select a date to view what the events are held on that date... Please help...

This is my sheet data, all the formats are string:

Here are my blocks:

The webview I placed it into when the screen is initialized

[ABG]

What is the purpose of this filter ?

[Allysha_April]

I tried to understand it but couldn't quite get it, I got the help from a power user called TIM from my previous project. I thought that the part of the code would help to get the original index from the list...

What I think the purpose of the filter is to compare whether the column of the queried data has the same data within the original data... but the column I need is the date column. I know that the column in index 1 is the Day column. I tried so many methods but to no avail...

[ABG]

When you are dealing with something that's starting to get complex, it helps to start a Google Doc explaining how the parts of the app fit together, with code fragments and text interspersed, and headings for the Table of Contents.

I have a collection of long forgotten samples like that.

[Allysha_April]

Thank you for the tip, I will keep note of that for my next project. I just need to try and get this one done because I have a final year project practice run with my supervisor in the next two days and I need to make a demo video of my application. This is the only final part that is not working as I need it to be... In actual fact everything was done but I decided to clean things up. So now instead of it showing the whole list I filter it, the delete function and updates are all ready to go I only need the correct index

Please help, this is the final push before my big presentation

[ABG]

At the very minimum, make a list of the global variables.
For each variable, tell

• what it is supposed to hold
• where it gets its value(s)
• what purpose it serves

That's the first thing I look for when trying to analyze a broken project.

[Allysha_April]

1. Table = Holds all the original data, it gets it's value from the ReadAll function in another script in my google sheet
   

   

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

3. FilteredIndexes = to keep all the filtered indexes after it knows which index the data was extracted from

4.QueryData = The queried data extracted from the sheet filtered by date after a date is selected

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

[ABG]

Also needed for understanding the variables:

• If it is a list or table, how many columns, and what are in the columns?

[Allysha_April]

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)
   

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)
   

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

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

[ABG]

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

[Allysha_April]

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

[ABG]

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.

[ABG]

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

[Allysha_April]

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

[Allysha_April]

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

[ABG]

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.

[ABG]

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?

[Allysha_April]

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

[ABG]

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

[TIMAI2]

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 ?