In short
Many revenue teams work with Excel, and that's fine. The first step away from a single file is Google Sheets: working together, version history, a link instead of an attachment. The second is Apps Script: saving daily snapshots, calculating pickup, flagging anomalies by email. This article shows both with sample code and names the limits. A platform only pays off once upkeep, permissions and reliability cost more time than the analysis itself.
Excel is not the problem
Let's start with a defense. Excel is fast, everyone can use it, and almost everything a revenue team needs day to day can be built with it. A good spreadsheet is often smarter than some software sold to replace it. If it works for you, keep it.
Problems don't come from Excel itself, but from the way files move and grow.
- The current version sits in an email attachment, and nobody knows if it's the latest one.
- The file lives on a single computer, and the colleague who maintains it is on vacation.
- A macro does what it is supposed to, but only one person understands it.
- Numbers get pasted in by hand from a report every morning, and some days someone forgets.
Step 1: Google Sheets, so everyone sees the same file
The first step is unspectacular and still the most powerful. The file moves to Google Sheets. From then on there is only one version, with a link instead of an attachment, with version history and with permissions you can grant and revoke. Several people work in the same spreadsheet at once.
It's worth a quick look at the pitfalls:
- Formulas usually carry over on import, but not every Excel function exists in Sheets.
- Macros and Power Query do not come along, they need to be rebuilt.
- The separator in formulas depends on the spreadsheet's language setting; in English it is usually a comma.
- Anyone pulling data from other sheets has to grant access explicitly once.
Step 2: Apps Script, so no one has to paste numbers in anymore
Google Sheets has a built-in scripting language, Apps Script. It is based on JavaScript and runs right inside the spreadsheet. One use case is especially rewarding for a revenue team: the daily snapshot. Anyone who records the current on-the-books figure by arrival date every day can calculate the next day's pickup, meaning how much got added, without anyone typing anything in.
The "Import" tab holds the current snapshot, say from a report you paste or load in there. The "History" tab gets a dated copy of it every day. The script below does exactly that. It is a starting point, not a finished product: please adapt it to your columns and test it on a copy first.
const SOURCE = 'Import';
const TARGET = 'History';
function saveDailySnapshot() {
const file = SpreadsheetApp.getActiveSpreadsheet();
const source = file.getSheetByName(SOURCE);
const target = file.getSheetByName(TARGET);
const asOf = Utilities.formatDate(new Date(), file.getSpreadsheetTimeZone(), 'yyyy-MM-dd');
// Avoid duplicate runs: are there already rows for today?
const last = target.getLastRow();
const soFar = last > 1 ? target.getRange(2, 1, last - 1, 1).getDisplayValues().flat() : [];
if (soFar.includes(asOf)) return;
// Skip the header row, skip empty rows.
const rows = source.getDataRange().getValues().slice(1).filter(r => r[0] !== '');
if (rows.length === 0) return;
const withAsOf = rows.map(r => [asOf, ...r]);
const start = last + 1;
target.getRange(start, 1, withAsOf.length, 1).setNumberFormat('@'); // keep the date as text
target.getRange(start, 1, withAsOf.length, withAsOf[0].length).setValues(withAsOf);
}
// Run once: sets up the daily run at 06:00.
function setUpDailyRun() {
ScriptApp.newTrigger('saveDailySnapshot').timeBased().everyDays(1).atHour(6).create();
}Here is what the history looks like afterward. Each row belongs to an as-of date and an arrival date. Pickup can be derived from two consecutive as-of dates.
| Column | Content | Note |
|---|---|---|
| A | As-of date | Set as text by the script |
| B | Arrival date | From column A of the "Import" tab |
| C | Rooms on the books | Snapshot as of that date |
| D | Capacity | Sellable rooms on the arrival date |
Pickup by formula
The pickup for an arrival date is today's figure minus yesterday's. The "Log" tab has a simple formula for that. Example for row 2, with the arrival date in B2 and the as-of date as text in yyyy-MM-dd format:
=SUMIFS(History!C:C, History!A:A, TEXT(TODAY(), "yyyy-MM-dd"), History!B:B, B2)The same formula with TEXT(TODAY()-1, "yyyy-MM-dd") gives you the day before. The difference is the pickup. If your spreadsheet uses a German language setting, the function is called SUMMEWENNS and the separator is a semicolon.
This idea is where the template for this article came from: a pickup log with thresholds that flags conspicuous arrival dates. You will find it below this article.
Flagging anomalies by email
Anyone who does not want to open the spreadsheet every morning can have the conspicuous dates sent to them. The script builds on the template: the "Log" tab has "yes" in column J when a date crosses the threshold. This script is also a starting point.
function reportAnomalies() {
const file = SpreadsheetApp.getActiveSpreadsheet();
const timeZone = file.getSpreadsheetTimeZone();
const today = Utilities.formatDate(new Date(), timeZone, 'yyyy-MM-dd');
const tab = file.getSheetByName('Log');
const hits = tab.getDataRange().getValues().slice(1).filter(r =>
r[0] instanceof Date &&
Utilities.formatDate(r[0], timeZone, 'yyyy-MM-dd') === today &&
r[9] === 'yes'
);
if (hits.length === 0) return;
const text = hits.map(r =>
Utilities.formatDate(r[1], timeZone, 'MM/dd/yy') + ': ' + r[5] + ' rooms pickup, ' +
Math.round(r[7] * 100) + '% of capacity'
).join('\n');
MailApp.sendEmail('revenue@sample-hotel.example', 'Pickup log: anomalies', text);
}Where Sheets and Apps Script hit their limits
So far, so practical. Honesty requires naming what this path does not do. We have hit these limits ourselves:
- A script belongs to one person. If they leave the company, triggers and permissions may or may not keep running.
- Execution time per run is limited. Large volumes of data have to be processed in batches.
- There are no automated tests. If the report’s format changes, the script keeps running and writes wrong values.
- Errors easily go unnoticed if no one has set up notifications for failed runs.
- The more tabs, scripts and dependencies, the more upkeep. At some point you are maintaining the solution instead of the numbers.
- Data protection and access need to be managed deliberately, because sharing a spreadsheet is also sharing the data.
Step 3: the platform, once upkeep becomes the job
A platform is not a goal for everyone. If your log runs reliably in Sheets and nobody suffers because of it, stick with it. The step starts to make sense once the questions change: no longer "how do I calculate the pickup?" but "who maintains this, who is allowed to see what, and how do I know the numbers are right?"
| Level | Strength | Limit | Fits when |
|---|---|---|---|
| Excel | Fast, familiar, flexible | Versions and single machines | One person works alone and the analysis stays manageable |
| Google Sheets | Shared, version history, permissions | No macros, little structure | Several people need the same numbers |
| Sheets with Apps Script | Automatic, custom-built | Upkeep, no tests, owned by one person | Tasks repeat daily and someone can maintain the script |
| Platform | Reliable, with permissions and an audit trail | Rollout and ongoing cost | Upkeep and reliability cost more time than the analysis |
You carry this forward at every level: the questions, the structure of the data, and the knowledge of which anomaly matters. Anyone who has run a clean pickup log in Sheets knows exactly what they need from a platform by the end. That is the real payoff of this detour.
At RD030 we are building the platform for the third step. But we deliberately start with what teams are already doing, and we will tell you when Sheets is entirely enough for your hotel.