How I automated a short-term rental business
with Google Sheets, Google Apps Scripts, Apple HomeKit and Shortcuts
Besides of my full-time job as a Product Management consultant at Modus Create, I run an Airbnb rental - a holiday house I build some years ago in a remote mountain area in Romania, mainly for my family and my kids. Since they left the nest, now living and studying abroad, that house didn’t make too much sense only for me. So I decided to short-term rent it through Airbnb (mainly) and Booking.com (secondary).
As a Product Manager advising companies in best practices around product strategy and product development processes, I feel quite normal to taste my own dog food.
So, let’s summarise:
Product - short-term rental house
Challenge - hard, and time-consuming to be managed remotely. Each new or changed reservation request should be manually processed by informing the house-keeping lady, turning on / off the ambiental lights and heating
Solution - automate as much as possible the following activities:
send to house-keeping a list of reservations for every month
send a reminder for the checkin and checkout dates - be sure the house is prepared on time
send an updated list of reservations for the current month when a new reservation is made, when a reservation is canceled or changed
keep the ambiental lights on during a stay
keep the ambiental lights off when the property is not occupied
if cold, tun on the heating system, only when are guests inside
if not need (not cold), or nobody’s in, keep the heating off
My “user problem” was the time I spend with the property management tasks. And as any manual solution, it’s prone to errors (not once happened to forget to turn on the heat on time, and when the guests arrived there wasn’t warm enough —> low score, or a negative review).
So, I sketched the solution above with two simple goals:
reduce my workload, by automating some tasks
reduce the guest handling errors, by better informing the person who takes care about the house
Before creating a “complete” technical solution I was looking first for a fast prototype, easy to be implemented. The key metric was for me how much time I can save vs how much time I invest in building the solution.
Everything described in this article was developed in less than 10 hours of work, spread across 4-5 days, including testing.
Step 0, prerequisites
Worth to be said: I already had a HomeKit compatible automation system in place. It runs in the Apple’s ecosystem and has the following elements:
Bosch smart thermostats installed on each radiator (before the current solution I was manually enabling the heating scenarios every time a new guest checks in, and disabling it when checks out)
Wifi controlled smart switch to power on/off the outside ambiental lights
Twilight analog switch/sensor for turning off the ambiental lights during the day
Apple TV - it acts as a smart home hub
Also, for my internal reporting needs I was maintaining a simple Google Sheet with reservation details. Here is its structure, and some sample data:
Step 1, drafting the user and app flows
With those things already in place, I decided to go for the simplest solution possible.
a Google sheet to keep the data (the same I used before, so nothing new to be created)
Google Apps Script (GAS) to run the app logic. The GAS is deployed as a web app
Apple Shortcuts as the easiest way to control smart home appliances, and send WhatsApp messages to the person who does the housekeeping.
Here is the block diagram for the app logic:
So, every time a new reservation arrives in Airbnb or booking.com I manually update the Google sheet. The same when something changes, like number of guests, checkin date, or when a reservation is canceled (yes, it happens).
Then I have some shortcuts and automations being set in the Shortcuts app on my iPhone (NB: Shortcuts is also available for macOS). Those shortcuts do a GET request to the web app deployed under GAS, and depending on the response they handle part of the logic and turn the lights on and off, check the weather and if the outside temperature forecast is below 16C turns the heating system on, etc. Also those shortcuts fire up some notifications on my phone, and send customised WhatsApp messages to the person who handles the checkin/checkout activities.
Step 2, Google Apps Script and the web app
The next step was to setup a GAS environment. This is easy, and super straightforward - Apps Script environment is directly accessible from Google Sheets (top menu > Extensions > Apps Script) and can be configured in just few clicks.
function doGet(e) {
var operation = e.parameter.operation;
if (operation === 'monthlyReservations') {
return getMonthlyReservations();
} else if (operation === 'dailyCheck') {
return getDailyCheck();
} else if (operation === 'changedReservations') {
return checkForReservationChanges();
} else {
return ContentService.createTextOutput('Invalid operation');
}
}
The starting point there is to create a doGet
function that is invoked on any GET request. A parameter is sent in the URL, and based on its value the appropriate function is called:
getMonthlyReservations
dailyCheck
checkForReservationChanges
You can find the full script here on Github.
Step 3, Apple Shortcuts & Automations
I created four shortcuts in Apple Shortcuts. The shortcuts can be created either on iOS or macOS devices.
Checkin/out Lights & Heating - enable/disable the ambiental light and heating
Checkin/out Message - sends a WhatsApp message to cleaning lady in the days when is a guest arrives or leaves
Reservation Change Message - sends an updated reservation list for the current month every time when there is a new/changed/canceled reservation
Monthly Message - sends a message with the reservations for the upcoming month
As an example, here is the more complex shortcut, the one that handles the control of ambiental lights and the heating depending on the forecasted temperature:
The next step is to create the automations which will run those shortcuts. The automations should be created on the iPhone’s Shortcuts app.
For my propose, I’ve created two automations.
One runs daily and groups the first three shortcuts listed above (lights/heating automation, and the messages sent to my cleaning lady (on the checkin/checkout dates, or whenever is a change in reservation list).
The 2nd one runs monthly
So that’s my little project. I tested it in the past couple of weeks, and everything goes well. Lights stay on when needed, the heating turns on and off at the right moment, the person who handles the housekeeping and guest activities is updated with all changes.
For sure there could be much more to be improved, many things to be further automated. Also, at this moment the entire communication with my guests is handled through the reservation platforms (Airbnb and booking.com), so I’m not sure if to move out of it and handle on my own system or not.
At the end of the day, why to build a feature if there no any problem to solve, right? But this is a question I can easily respond as Product Manager consultant, but it’s far much harder as a system owner 🤡