Hotel Morning Report in Google Sheets: Build It Right, Then Automate It
Google Sheets is where most hotel morning reports actually live. Not in the PMS, not in the RMS dashboard, not in any of the purpose-built reporting tools — in a shared Google Sheet that someone updates by hand every morning. That's fine. The sheet is often the right destination. The part that isn't fine is the manual rebuild that happens to populate it every single day.
Why Google Sheets is the right destination (and the wrong process)
There's a persistent tendency in hotel tech to treat the spreadsheet as the problem. The pitch from every reporting software vendor is some version of: "stop living in spreadsheets and start using our platform." I understand why they say it — spreadsheets are hard to sell against — but the framing is usually wrong.
Google Sheets is a reasonable morning report destination for a lot of hotel teams. It's familiar to everyone in the organization. It's flexible enough to format exactly the way the team wants to see the data. It can be shared instantly. It has a permissions model that keeps the right people in the right sections. And unlike most purpose-built hotel dashboards, the team can actually modify it when something needs to change.
The problem isn't the sheet. The problem is the human assembly process that populates it every morning — the 30–45 minutes of opening exports, copying figures, pasting them into the right cells, fixing formula errors, updating the date range, and distributing the result. That process is worth automating. The sheet itself is often worth keeping.
Before rebuilding your morning report in a new tool, ask whether the tool solves the assembly problem or just moves it somewhere different. If you'd still be manually pushing data into the new tool, you haven't fixed anything — you've just changed where the work happens.
How to structure the sheet correctly
A well-structured hotel morning report sheet isn't complicated, but there are a few design decisions that matter a lot — especially if you're planning to automate the population step later. Here's how I think about it.
Portfolio or property summary — what gets read first
One row per date (next 30 days), with the key metrics across columns: occupancy, ADR, RevPAR, pickup last 7 days, rate vs. comp set, and a status column. This is what leadership opens. It should be readable in under two minutes without scrolling sideways.
One tab per source report — raw import landing zone
Keep the PMS export, pickup data, rate shop export, and forecast in separate tabs. Don't mix them. The summary tab references these source tabs via formulas — when you automate, the automation writes to the source tabs and the summary updates automatically.
Thresholds, dates, and settings — one place to change everything
Property name, budget figures, comp set names, exception thresholds (what occupancy level triggers a flag, what rate gap triggers an alert). Keeping these in a dedicated tab means you can adjust them without hunting through formulas.
Rolling archive — same-period prior year in one place
Daily figures from the prior year, appended monthly. The summary tab references this for YoY comparisons. Keeps the source tabs clean and makes trend analysis available without pulling separate reports.
Sheet design — what to do and what to avoid
Do
- Lock the summary tab formulas — data entry only in source tabs
- Use consistent date formats across all tabs (YYYY-MM-DD is automation-friendly)
- Name ranges for key data series — makes formulas readable and automation easier
- Freeze header rows so large date ranges stay navigable
- Use conditional formatting for exception flags — visual scan is faster than reading every number
- Keep the summary tab to a manageable column count (8–12 columns max)
Don't
- Merge cells anywhere — it breaks automation and makes referencing painful
- Hardcode figures in the summary tab — they won't update when sources change
- Use different column orders across source tabs — normalization becomes a manual step
- Let the sheet accumulate years of data in one tab — performance degrades
- Mix data and notes in the same cells — use a dedicated notes column
- Put important context in cell comments — they disappear when data is replaced
The automation path from a well-structured sheet
Once the sheet is structured the way I described above — summary tab pulling from source tabs, no mixed data, consistent formats — the automation step becomes straightforward. The automation layer doesn't touch the summary tab at all. It writes to the source tabs. The formulas handle the rest.
What the automation does
On a schedule (typically 5:30–6:00am), an automation layer collects the scheduled exports from each source system. The PMS daily close lands in a designated folder or inbox. The rate shop export arrives on its schedule. The pickup data is pulled from wherever the PMS produces it. Each export gets parsed, normalized to match the column structure in its corresponding source tab, and written to that tab. The summary tab recalculates. The sheet is ready before anyone sits down.
A minimal Google Apps Script approach
For teams running a Google Workspace environment, a lightweight Google Apps Script can handle the last-mile population step — reading a processed CSV from Google Drive and writing it to the appropriate source tab. This isn't full automation, but it's a meaningful step: it removes the copy-paste work while keeping everything inside the tools the team already uses.
// Reads a processed PMS export from Drive and writes to the PMS source tab
function updatePmsTab() {
const sheet = SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName('PMS Export');
const folder = DriveApp.getFolderById('YOUR_FOLDER_ID');
const files = folder.getFilesByName('pms_daily_close.csv');
if (!files.hasNext()) return;
const csv = files.next().getBlob().getDataAsString();
const rows = Utilities.parseCsv(csv);
sheet.clearContents();
sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}
This is a starting point, not a complete solution. A production implementation needs error handling, logging, a trigger to run on schedule, and a notification if the source file doesn't arrive. But the structure is right: the automation writes to the source tab, the sheet does the rest.
The full automation layer
For management companies with multiple properties, or for teams that want the entire process — export collection, parsing, normalization, and write-back — automated without manual steps, the automation layer sits outside the sheet itself. It handles the export collection from each source system, normalizes formats (especially important if properties use different PMS platforms), and writes the finished data to the appropriate source tabs across one or more property sheets. The sheet still lives in Google Drive. The team still reads it the same way. The assembly work is gone.
Questions about hotel morning reports in Google Sheets
How do hotels use Google Sheets for morning reports?
Most hotel teams use Google Sheets as the assembly point for daily morning reports — pasting in figures from the PMS, rate shop, and forecast, then distributing the finished sheet by email or shared link. The sheet is the destination; the manual work is the data entry.
What should a hotel morning report Google Sheet include?
A well-structured hotel morning report sheet typically includes: occupancy and ADR by date for the next 30 days, pickup vs. prior 7 days, rate position vs. comp set, variance to budget and prior year, and an exceptions column for dates requiring attention.
Can you automate a hotel morning report in Google Sheets?
Yes. The most practical approach uses Google Apps Script or an external automation layer to pull scheduled PMS exports and populate the sheet automatically. The sheet stays the same — the manual data entry step is removed.
What's the difference between a hotel morning report sheet and a dashboard?
A morning report sheet is designed for a specific daily workflow — it's read linearly, distributed, and discussed in the morning standup. A dashboard is a persistent view that stays live throughout the day. Both are valid, but they serve different purposes and require different design approaches.
Already using Google Sheets for your morning report?
The morning report template shows the target structure — and the workflow audit helps identify which parts of your current assembly process can be automated without replacing the sheet.