Just starting out on an app idea for a local business. Have spent most of the past 2 days on the forums trying to find some inspiration.
They want to have booking slots available for people to book their service. I am happy with the data logic behind booking out the slots, dealing with the data privacy, admin rights etc.
I am struggling with the concept of creating those slots in the first place. Or at least in a fairly straightforward way.
Initially their service is only running a couple of days a week, and maybe 8 slots per day. So manually creating each slot isn’t really a problem. If this was going to go 5 + days per week, this would get rather laborious.
They would prefer to have a calendar style view to book from (which is causing me the headache) rather than a slot booking similar to @Robert_Petitto meetwithme app (which is awesome btw) Also usual thanks to @Jeff_Hager for the “Concepts” app.
I have considered using a Google Sheets Google Calendar link, but with recurring appointments (which would be the obvious way to do it) the spreadsheet is going to get unmanageable in the long run. Without recurring appointments then it isn’t much different to creating each one manually.
I’m still mulling over some ideas in my head. I haven’t had a chance to sit down and work through them, but I have some conceptual ideas to use sheets/tables as a sort of template that can take values in a row and auto populate the entire sheet dynamically based on the user and what they want to view. I have the basics of that in my Calendar Concept app, where the calendar sheet is prebuilt, but data in it dynamically changes based on which month a user wants to view.
I also have it set up that a user can create an event and have it reoccur over several days. After playing with date manipulation for the past few weeks, I would like to rework the events sheet with better and more robust re-occuring event scheduling.
I don’t have a quick answer other than taking a look at the app below. I think the idea of a sheet as a template could be huge as far a logic. So in your case, you would have one sheet that lists the number of booking slots for one day. The number of rows in that sheet would never change and would remain small. Then you dynamically show the correct day and link each time slot to a sheet of already booked slots, so you can determine if a slot is taken or not. The idea is not to create a bunch rows to indicate slots, but to dynamically show what isn’t booked for a particular day with a very small sheet. I hope that kind of makes sense.
I also have a calendar style layout, which is probably something that you are looking for, but to be honest, it’s gross behind the scenes.
Take a look and see if you get any ideas. I hope to re-work some of it soon and start throwing in all of the date calculation examples that I’ve been coming up with lately.
Hey @Mishta_P, I’m working now in a project i was struggling a lot to build all the logic behind the booking logic. But, finally, I found a way to do it!
Soon I’ll post here how I did using my new method Trebuchet (I haven’t posted yet too, I know - life’s busy)
Came up with this idea to generate a list of bookable slots from an easy interface. The time to generate the list seemed slow in GSheets but in the app it doesn’t appear to be too bad.
It currently does exactly what I want it to (apart from the American Date issue (not sure how to solve that as my brain is fried from the rest of this).
You can have 5,10,15,20 minute intervals as well, however they can break the arrayformula limit in GSheets so have taken it out for now.
In theory you can have any interval you want, however at the moment it calculates from 00:00 of “Today” so will break the following days if it isn’t a factor of 60, so you don’t get the option at the moment
I am thinking that from a slot you can then “book” data into another table. From there I will (somehow) apply some booking logic back to the list to filter out the booked slots. Or show them as booked.
/edit I have a simple version of the booking done, just have to work out how to block out the future appointments if someone books a double (or more) session.
Well, none as yet. I had a new thought about how to produce booking slots more efficiently than in my test app above.
If I booked an appointment today, for the April 2nd 11am, that would be a specific point in time (maybe with Epoch). Once we arrive at that moment with the clock change would that not register as 10am.
Or do I ignore Epoch and try with dates and times and that will just work?
I can’t speak for epoch or specifically the ins and outs of it. My guess is that it has no knowledge of daylight savings. I spent some time a couple weeks ago converting a date to epoch to do some further calculations, but ran into some unrelated roadblocks because I was trying to convert year/month/day to epoch, when I really should have been doing year/ordinal day. The entire formula I was attemtping to come up with was just too involved, so I went a simpler route.
I’d say that if you have a date and time set for April 2nd @ 11am, any device regardless of timezone or daylight savings will see that date and time as April 2nd 11am and any filtering or conditions comparing that date/time to today or now, will be based on the date/time of the local user’s device. i think trying to convert to epoch would unnecessarily complicate things.
Not sure how exactly your are handling your booking slots in your app, but I think you could break everything down into one simple glide table. First you would have to figure out the maximum timeslots in one day. Then you can dynamically figure the length of each timeslot based on prior inputs. You could have a user first select a server provider, then select a day, then based on those inputs you can figure out how to display the booking slots for only that selected day.
This is much like what I’m doing in my calendar app. I have the MonthLayout table, which has only 42 rows and will never have any more or any less. It will default to the current month, but a user can navigate to any month in any year and it will dynamically figure out how to build out those 42 rows based on the selected month/year. Then I have each date relating to an events (in your case bookings) sheet to see what matches for that day and marks on the calendar that there are events on that day. That one table of 42 rows can dynamically display an infinite number months. The MonthLayout table is really just a placeholder template that dynamically changes based on user input.
I’m thinking you could do something similar. Just picking a random number, you could have a table with 30 rows. In that table you could have a couple of columns that would accept user input, such as service provider and date. The would cross reference to a service provider sheet to figure out what their availability is and then through various math, you could dynamically figure out each of those timeslots for that day and show that to the user. Once they select a timeslot and book it, then you can have additional relations to the bookings sheet and use that to determine if you want to show or hide that timeslot.
All of this could be done it a glide table without any google sheets.
That was pretty much my thought. For me the key was to have the booking slots viewable to book, rather than trying to book and it showing as unavailable.
I am thinking of having a day laid out in 5 or 15 minute chunks, using the date picker, and then looking up what slots are available that day to display.
My main issue is working out a way of having someone book a client in for a regular slot, although I think I might be able to do that with a simple Boolean if my idea pans out.
I guess with what I mentioned above, you would be limited to looking at only one day at a time, but it would be easy to flip through days and have the template table update on the fly. With what I was thinking, you could conditionally show only the open slots as an inline list. So really, the way I pictured it, is a user would select a provider and a day, then a list of all available slots for that provider/day would be shown. Then they could click on a slot and continue to book that slot. It’s not so much a matter of showing if a slot is available or not…it’s more so about only showing what’s available to be booked. Comparing to my calendar app, it would be like showing all of the days for a month that could be booked. Once a user selects a day and books it, then that day could be removed from view. The only difference between my example with months and days, is that yours would be with days and slots.
So considering 15 minute slots for an entire 24 hours, you would need a sheet with only 96 rows. It would be 288 rows for 5 minute slots. If you wanted to show multiple days, then obviously you would need to multiply those rows by the number of days you want to show at once. If you start trimming out certain timeslots that would never be populated, then you could bring that row count down a little bit. Those dynamic timeslots would just calculate on the fly based on the chosen provider and day by the user.
Hey! I’m rebuilding this app in Glide Tables and hope to get it submitted as a Template in a couple weeks. I think it does what you’re looking for — basically, I take the start and end time and figure out what times are before the start and after the end — those times are still available as choices, while the start, end, and times between them are not. My logic and use case uses 15 minute increments as times.
Yup. At the moment I have a sheet sitting there with 288 rows, thinking 15 minutes are probably ok, although I guess there logic would work with both. I was considering Epoch as I had come up with a way of blocking out multiple slots from 1 submission, but had a discussion and came to the conclusion it would cause daylight saving issues, so abandoned it.m, hence the previous post in the hope I could go back to it.
I think I can do the same with date/time maths having seen your sheet and a few others.
The real key is having that visual representation of available slots as I feel it is something people are used to, and is a quick way to see the structure of a day you are not familiar with.
@kyleheney I’ll have another look at your app, initially it seemed quite a way from what I was trying to achieve, but the background logic might be asking the same lines.
Here is version 2. Thank you to @Robert_Petitto for his video on opening hours maths. Got me out of a frustrating hole. This app could really do with a time picker, or at least a restriction on the date/time picker to default to time view.
Now time for the fun part with the booking logic, and preventing overlaps etc.
Any advice about how to deal with privacy of bookings whilst also having data available to search for overlaps?
/edit Also tried adding a button pair to increase and decrease picked date by 1, but it ends up in 1970. I thought adding 1 to a date, advances it by a day.
I think your best bet would be to have a secondary “Add Row” action that adds the date and times to a separate sheet (without the identifying information of who submitted it). You could then use those values as the one to filter out times that are already taken, while still being able to use Row Owners to protect the actual submissions.
I’m curious to see how you get on with this, as I have a similar challenge with a leave management app.
In my case, I need to prevent staff from double-booking leave.
I haven’t thought too hard about it yet, but I expect this will be quite difficult to manage with just Glide computed columns. Especially as you may have to check against multiple rows.
If all else fails, I know I can do it using an onChange() trigger (when the only tool you have is a hammer), but would like to avoid that if I can.
I am wondering whether having the member RowID in the wild is too compromising. My current thought is having the booking with just the memberID, date/time start, duration. No one is the row owner. This will be restricted to view by MemberID, so they can then delete/edit their own bookings etc.
I will then have another table which references that for an admin view with member details.
I like the idea of @kyleheney idea, but can’t see how I will undo a booking once made with data in multiple tables. Unless each booking can be stamped it a unique ID and then linked? I am now thinking out loud.
Yes, removing or editing a booking that exists in multiple sheets/places may present an issue, I agree.
I do the method you mentioned quite a bit (where I pull the RowID and other non-identifying columns of data into another sheet). The way I look at it is that as long as there is nothing that ties that ID column to other personal info, then it is secure. The only thing I can think of that may make it identifiable would be if someone found a pattern of bookings for one RowID and could use that pattern to figure out who booked those items.
