Create a room availability dashboard for Workspace with Apps Script and onleetable

Stéphane Giron
4 min readSep 8, 2022

--

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”

Click + to find 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.

Get Google Calendar ID

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.

Open 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

Room availability Dashboard for Google Workspace

Live example : click here

Variation

With the ‘Card View’ available in the section ‘Design’ you can move to a Card design.

Room availability, card view design

Make your own Room Availability table

Install onleetable : Click Here

Make a copy of the sheets : Click Here

--

--