I would like to know how the update works when the application is connected to Google Sheets.
I’m developing a scheduling application and much of the logic is in Google Sheets, the part that bothers me the most is when the user records the start date/time and end date/time, after which Sheets does the calculation to show the cars available, however, this process takes around 30 seconds and this is unacceptable. The application doesn’t have many records or a grid database, it’s just 3 cars and a simple calculation, used =COUNTIFS in Google Sheets.
The app is connected to Glide’s backend copy of your google database. The backend will then sync to your google sheet. Glide may request updates from Google periodically, and sometimes Google will send data to Glide. None of it is meant to be instantaneous. You would be better off moving your logic into Glide. A CountIF is nothing more than a Relation or Query followed by a Rollup. Pretty simple actually. Moving the logic into Glide allows those computations to happen directly on the user’s device, so the results are instant. Otherwise data has to sync from the app to Glide, then sync to Google, calculate, then sync back to Glide, and sync back to the app. Let the user’s device do the work instead of passing data back and forth just to get results.
That could very easily be done with all of the logic in Glide and no formulas in the Excel sheet. In addition to the slowness, you are also restricting yourself to only one user using your app at a time by having the logic in your Excel sheet.
All you really need is to have a checkout table with two user specific date columns. You can use a date time picker to write dates and times into those user specific columns.
In your Cars table, add two single value columns to bring both of those dates into the Cars table. Then create a Query column that points to the Agenda table, with a filter that matches where Car is Row → Car, Start Date is before Row → End Date, and End Date is after Row → Start Date.
Then you can set you cars collection to only show if the query column is empty.
I would like to thank you for spending your time helping me, it worked exactly as you said, I spent countless hours to find a solution, and only yours worked. I am eternally grateful.
Actually the problem was not solved, I tried just using IS BEFORE and IS AFTER, but it wasn’t.
I used IS ON OR BEFORE and IS ON OR AFTER but it worked for a while and then stopped.
I don’t use the DATE & TIME component, just DATE.
I use a CHOICE with a specific table and the times are every 30 minutes, example:
12:00, 12:30, 13:00, 14:00…
This makes it easier for the user and provides greater security if the user chooses a very specific time (example 14:54)
ON OR BEFORE/AFTER only accounts for Dates. It does not look at time. IS BEFORE/AFTER includes time. It’s important to use the correct one.
That’s the source of your problem and why I specified to use the Date Time component, because it includes a time chosen by the user. If you only use the Date component, then you only get a date at midnight by default.
If you are going to go that route, then you will have to first separate the year month day from the date column, use a template column to merge the chosen time, and then use a Text to Date column to convert it back into a date. Only then can you use the logic I share previously.
The Date & Time component leaves a lot of room for the user to make mistakes, for example: I can’t block a timetable. If the user selects a schedule to start at 15:00, they can select the time 09:00 to return, and I can’t block it like that, much less block times that are past the current time. That’s why I used CHOICE with a time table, there I can do the logic.
On the first day everything worked correctly, I don’t know what happened and it stopped working.
It probably worked because you were using ‘ON OR BEFORE/AFTER’ which looks at the whole day without looking at time.
If you look at the post I shared, there is math to convert your date into YYYYMMDD. Make sure you select no decimals and no group separators. Start with that.
Then use a template column to join the math column and your chosen time together. It should look something like this: 2024042513:00. This is the value you will pass into the Text to Date column.
Configure your Text to Date column something like this. You may have to play around with your timezone, but based on your local time compared to mine, I think I have it set up correctly:
I recorded a quick video to show you how the tables and the application are, if you can see it I would really appreciate it. You are helping me understand Glide.
The biggest problem is that you haven’t created the logic to join the date and time into one date value. That’s the part I tried to explain in my previous post.
What you have right now is 4 date columns instead of 2. So your query is looking at all 4 as individual dates and times.
Let say you make a booking for April 30th from 13:00 to 14:00. Your date only columns will have a date of April 30, but the underlying time is 00:00. Your time only column will have a date of April 26 with a time of 13:00 or 14:00. Each date column on it’s own does not contain all of the date and time information you need. Your query is comparing 4 different sets of dates and times. That’s why it doesn’t work.
You need to join the separate date and time together into one single date time value. The math column will convert your your date into a simple and predictable format of Year Month Day (YYYYMMDD). Then you join that with the time using a template column. And finally you use the Text To Date column to convert that text into an actual date time that you can then use in your query column.
There is another method that should work a lot better and be more reliable. (This only works because you already have your time columns set as Date type columns, which is good) What you would do is set up a math column like below. Date is your date column and Time is your time column. This math will add the selected time to the date, and will give you a single date column that contains both the correct date and time. (This works because the date columns are defaulting to midnight, so it’s easy to add the correct number of hours and minutes to that date.) Do this for your Start and End dates. Then change your query to only use those two math columns instead of the separate date and time columns.
