Create a room availability dashboard for Workspace with Apps Script and onleetable
Finding a room available can sometimes be complex in enterprise and be a challenge. Workspace users have a great tool with Google Calendar to create events and book rooms but sometimes you need just a more global view of what room is free and what room is booked.
For the past few weeks, I’ve been working on onleetable a Google Workspace add-on that allows you to create a table from your sheets and embed it in any web site. In this article I share a script that will generate the room availability data and detail how you can embed it with onleetable within a second to your intranet or simply share the link.
Setup Apps Script to get the availability data
Make a copy of the template sheets : click here
Get Calendar ID
This is the longest part of the setup 😊 In the Sheets enter the Calendar Id of the resources you want to track in the column A.
To find the Calendar ID, you need first to add resources calendar to your Google Calendar. For that go to the “+” next to “Others Calendar” and select “Browse resources”
Add the resources needed to your calendar, just click the checkbox. Check for all the rooms :
Then go to the resource calendar, click the 3 dots and click Settings, then ‘Integrate Calendar’
Copy/paste the Google Calendar ID to the column A of the sheets.
Repeat for all the room calendars.
Finalise Setup of sheets
Once you added the Calendar Id to the column A, duplicate formula for the column H and I.
Run the Script
Go to the menu ‘Room Availability > Manual Launch’
Click Manual Launch, the first time, you will have to validate the scope, click again after this validation to get the data. Next time no need to click 2 times.
Data is added to the sheets.
Update data automatically
In the menu ‘Room availability’ select ‘Create trigger’. The vailability of each rooms will be updated each 5 minutes.
Make the data available and embed it in your intranet
For that we will use onleetable (install from here).
Open the addon from the sidebar, first time validate access to the Sheets for onleetable.
Now we will setup onleetable.
Define ranges
Select Columns B to H in the sheets and click Refresh Selection in onleetable.
Define Design option
Select ‘Row hover’ only.
Define Filters
Add a ‘Select’ filter to the Free/Busy column.
Define Show/Hide
Hide the column Free/Busy.
Define options
Add a search input and allow ordering of column.
Create onleetable
Click ‘Create’ button, simple 😁
Now the onleetable is created and you can click the link generated at the top.
The result
Live example : click here
Variation
With the ‘Card View’ available in the section ‘Design’ you can move to a Card design.
Make your own Room Availability table
Install onleetable : Click Here
Make a copy of the sheets : Click Here