Disable gallery item when event already exists in google calendar

[Dutchmann]

Hi all!

I am trying to make a callendar app through which a person can select a date, see all the timeslots where the unavailable timeslots are greyed out. Just being able to show the available timeslots would already help tremendously.

I have tried to check if the time in the gallery (filled by making use of a table created within powerapps onvisible of the screen) exists in the listevent of google calendar. When that would be the case, I want the displaymode to be disabled. I've tried different filter and lookup variations, but this is all very new to me and I have exhausted my own ideas.

I have the feeling the solution is staring me in the face, so that is why I turned to you guys. If any clarification is needed, please let me know. Try to be as clear as you can, because the world of powerapps is vast and still very new to me.

Thanks in advance!

[v-yutliu-msft · Accepted solution]

Hi @Dutchmann ,

Do you want to display all the timeslots and make the unavailable timeslots grey or only display available timeslots?

Could you tell me how do you want to display timeslots? per half an hour or per hour?

I assume that you cut time per hour to display.

I've made a similar test for your reference:

1)create a collection with timeslots value (cut time per hour)

ClearCollect(timeslots,{starttime:Time(9,0,0),endtime:Time(10,0,0)},{starttime:Time(10,0,0),endtime:Time(11,0,0)},{starttime:Time(11,0,0),endtime:Time(12,0,0)},{starttime:Time(12,0,0),endtime:Time(13,0,0)},{starttime:Time(13,0,0),endtime:Time(14,0,0)},{starttime:Time(14,0,0),endtime:Time(15,0,0)},{starttime:Time(15,0,0),endtime:Time(16,0,0)},{starttime:Time(16,0,0),endtime:Time(17,0,0)},{starttime:Time(17,0,0),endtime:Time(18,0,0)})

2)create a collection with the lists of events:

ClearCollect(a,GoogleCalendar.ListEvents("phoebe...@gmail.com").items)

//please replace with your calendar id

3)insert a datepicker to select date

set the datepicker's OnChange:

ClearCollect(selectedevent,Filter(a,DateValue(Text(start))=DatePicker1.SelectedDate))

//filter events based on the selected date

4)since you can not set the gallery's background based on the items value, I suggest you set the arrow button's color based on available time

set the arrow button's color:

If(IsEmpty(Filter(selectedevent,(ThisItem.starttime>=TimeValue(Text(start,DateTimeFormat.ShortTime))&&ThisItem.starttime<TimeValue(Text(end,DateTimeFormat.ShortTime)))||(ThisItem.endtime>TimeValue(Text(start,DateTimeFormat.ShortTime))&&ThisItem.endtime<=TimeValue(Text(end,DateTimeFormat.ShortTime))))),Green,Red)

//arrow button will be green in the items that time is available 

arrow button will be red in the items that time is unavailable 

Best regards,

[Dutchmann]

That is an amazing and neat solution! I am going to spit through your code and try to understand it all, but for now it works like a charm. I had it working on my own way by applying multiple filters and storing the data in multiple gallerys. Collection are a bit daunting due to them being invisble so I hadn't touched them yet. But my way, albeit working, adds a delay of almost a second whereas your solution updates everything instantaneously. It is brilliant and neat. 

Now I am going to change the datepicker for a full calendar view and make it look like this:

Resulting in tiles through which people can let me know their preferred availability which automatically creates an agenda item and emails me and the participant the relevant information.

One last question though, do you know how I can change the used language of Calendar.WeekdayShort()? Now it is in Dutch, but I would like to have it in English. Thanks a lot @v-yutliu-msft !