Skip to content

<!-- Kommentarzeile -->

script

From Excel to Google Sheets and Apps Script to a Platform

<!-- by Robert Nagel, Managing Director, Revard Digital · Published on · 6 min read -->

Auf Deutsch lesen

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.

Apps Script: write the daily snapshot to history
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();
}
View of the trigger settings in Apps Script (recreated, no real data).

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.

Structure of the "History" tab (sample columns, please adapt)
ColumnContentNote
AAs-of dateSet as text by the script
BArrival dateFrom column A of the "Import" tab
CRooms on the booksSnapshot as of that date
DCapacitySellable 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:

Formula: look up the figure as of a given date
=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.

Apps Script: email conspicuous dates
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?"

Which level is enough, and when?
LevelStrengthLimitFits when
ExcelFast, familiar, flexibleVersions and single machinesOne person works alone and the analysis stays manageable
Google SheetsShared, version history, permissionsNo macros, little structureSeveral people need the same numbers
Sheets with Apps ScriptAutomatic, custom-builtUpkeep, no tests, owned by one personTasks repeat daily and someone can maintain the script
PlatformReliable, with permissions and an audit trailRollout and ongoing costUpkeep 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.

by Robert Nagel, Managing Director, Revard Digital

Questions about your hotel? Book a call about the platform: Go to pricing and booking

All articles

Get access

Direct access, cancel monthly.

Contact

Revard Digital 030 GmbH

c/o betahaus GmbH, Rudi-Dutschke-Str. 23

10969 Berlin, Germany

Phone: +49 30 60989327

Email: info@rd030.de

We reply within two working days.