I’m new to Glide and loving it!
I’m creating an app where students can borrow cameras and other equipment.
They’re allowed to borrow things for up to two days.
I’ve made it so that they can book only two weeks in advance.
Here are the problems:
How do I show the dates that a certain item is booked?
When I use the calendar list view, it only shows the booking on the start date. So if the item is booked from Tuesday to Thursday, it only shows up as booked on Tuesday. How do I display it in the calendar list under all the days?
When a user wants to check availability, they enter the start date, and how many days the booking is for: one or two. The end date is calculated in the sheet. How do I look up availability for all those days? So if they want to book from next Tuesday to Thursday, and the item is already booked Monday to Tuesday, I want it to say that the item is not available for that booking (and ideally make the “book now” button disappear).
I’ve seen solutions using filters that assume that there is only one booking in the system and check if the date is before or after that date, but in this case, the item could be booked many times in that same period, so I need to compare the range they want with all the booked dates. How do I do this? I thought I could create an array that lists all the days that the item is booked, compile them into a comma-separated list, and then do the lookup there, but that didn’t work.
Any suggestions for either of these problems would be great!!!
I assume you’re using Apps?
I’m not sure if you add an end date that is not on the same day as the original date then that would show up as multiple days on the calendar view, but make sure you add both a start date and an end date to the calendar.
Regarding the second question, what I would do, albeit a bit complex is:
For every booking, convert the start date and end date to a numerical value. This can be a date difference between that date and something like 1st January, 1970, so you have a continuous set of numerical values for the next set. Let’s call those “start diff” and “end diff”.
When users want to create a new booking, you create a custom form, create a relation from the item ID that they chose to the list of IDs that have already been booked, return a joined list of “booked list” values, comma-delimited.
Allow users to choose their start date and end date. Repeat the “start diff”, “end diff”, and “booked list” column for these two values.
Now, you have two comma-delimited lists, join them using a template column, split them into an array using a split text column, and use the “Duplicated Items” column to check if there are any duplicated values between the two arrays. If there’s at least one, don’t allow them to book.
Just off the top of my head, there would be better ways to do it.
Here’s what I’m trying:
When someone checks to see the dates an item is booked, they enter the item, start date and number of days they want to book for, one or two. I have a few things going in Google Sheets that help me to alter their input to our specifications (you can’t book Thursday to Monday, or Friday to Tuesday) and it spits out the end date. A unique transaction ID is also added to their user profile.
Then I have three columns in the booking responses sheet that calculate day 1, day two, and, if necessary, day 3, as separate dates.
When they make the booking, it takes the start date, number of days, item, and transactionID from their user profile, drops them into the booking responses sheet, and those other things are calculated. This takes a few seconds, so I wasn’t able to do it all in one button, so they get a confirmation screen, in which day 1, 2 and 3 are listed, and when they confirm it adds 1 or two rows to another sheet which contains each booking as an individual day. So if they booked for one day, there are two entries with the same item and transactionID, one for day one and one for day two. If they booked three days, there are three entries.
Then there’s a relation column under each item that lists all the days booked for that item from that sheet. This is what I’m hoping to use as a lookup to see if any of the calculated dates that they want to book are in that list, and that will determine whether the “Book Now” button will show up.
Wish me luck!
I’m already having problems with date formats… I’ll see what happens.
I would move whatever I can to Glide so that the performance can be better.
So saying I book Thursday, then I can only book 1 day, not 2, right?
I will definitely do that next time. I put a lot of functionalty in Arrays in Google Sheets, then added some lookups and relations in Glide. It works great but it’s sooo slow! You live and learn!
We present our project tomorrow, and we’ve built something we’re really proud of!
google sheets and scripts are much faster than Glide
Congratulations! If you can, please do a showcase here so we can learn from you.