Query List Google Spreadsheet

I am trying to make a schedule list where when they pick a date from the date picker it would query my spreadsheet to select only two columns which have the time and event title. It will use a sql query of selecting column C and D when column B matches the date selected. Column B holds the dates of all the events. But whenever I do this, nothing comes up except an error. After selecting the date it will search the data first whether it is available or not if not then it'll show N/A if available it should show the time and event





event sheet:

If you have done this properly:

1 Open the script project
2 Go to Publish
3 Deploy as Web App
4 Project version: - select New from the dropdown
5 Execute the app as: your google account address (email)
6 Who has access to the app: Anyone, even anonymous
7 Press the Update button

You have to do this EVERY TIME you change your script

Then the mostly likely problem is that you are querying for a string date, when you have "number" dates in your spreadsheet ? Easiest fix is to edit the dates in your spreadsheet F2 and put an apostrophe ' at the start of the date, like this: '21/09/2020, this will make it a string.

If you want to work with actual dates, then there will be more work to do when setting up the query:

select C,D where B = date '2020-09-21'

Excellent question asking, many forum users could learn a thing or two from you :+1:

Sorry, I have answered the wrong question!

The query is only returning a two item list (C,D) - ["8:00","Breakfast"]

In your web1.GotText you need to be looking for indexes 1 and 2 from the responseContent.

Ouhh but why index 1 and 2 but not 3 and 4 eventho the columns i want to extract the data from is column 3 and 4?

I tried changing the indexes only but the error still pops up but instead of giving the right data that should be extracted, changing the index to 1 and 2 only shows the time in the error pop up instead of both the time and event like the picture recently shown

your query is "select C,D...."


ahh hold on let me make some changes and i'll get back if this is the solution, i think my query is wrong

Nope, still not working. in my spreadsheet. i declared both my date and time into a plain text which i hope was a string. i tried changing it back to a date format then changing my block to the query "select C, D where B = date ' month block, day block, year block' " and changing the indexes to 1 and 2 but then it just shows the same pop up error but with no extracted data in the error pop up

If you are already "happily" declaring the dates in the spreadsheet as strings, then I suggest you stick with that. Hence your query should be:

select * where B matches '21/09/2020'

Using * will return all the data in the row, and therefore your indexes of 3 and 4 should work :slight_smile:


there is still something wrong, it still shows the same error. maybe it is an error in the script or block? because i see in the query, you typed "N2:Q3" which idk how to put in the script. can you help guide me? im in quite a confusion

Sorry for any confusion, I created a simple test to the right of some other cells in a spreadsheet.

You should be using A,B,C,D (these correspond to my N,O,P,Q).

Can you show your query blocks and a screenshot of your spreadsheet as they are now ?
I am assuming you have not made any changes to the web app script....

If it helps i have moved my little test to A:D:


these are my blocks and my spreadsheet has not changed. i reverted evrything back as a plain text

That all looks OK, although I am not sure why you are trying to set single items to listviews.... ( I would also give your procedure named "procedure" a better name to avoid confusion)

What does responseContent look like if you put it in a label?

I put them into a single listview bc i want them to seem like a table. for instance if the date that the user searches is not in found in one of the data then the list view will not be visible and a text will be placed instead that say N/A.

this is the list, it is currently showing all the data from the spreadsheet

still need to see this....

It seems to be working fine for a label...

Where is the day and the date ? That should be coming back as well ?

oh i specified it

because i wanted just the output for the time and event title

Please do this, so I can see "exactly" what is being returned


i dont exactly know how to get the question mark thing sorry...