---
title: "Three checks before a PMS export goes out · Vivified Tech"
description: "Check a PMS export for missing dates, total rows and rooms sold above rooms available before it reaches the revenue tool. Free, about 25 minutes."
url: "https://vivifiedtech.com/guides/pms-export-checks-before-sending/"
---

The three checks in 30 seconds

  [Download the video (MP4)](https://vivifiedtech.com/assets/video/pms-export-checks-before-sending/three-checks.mp4)

The checks from this guide, 30 seconds, no sound. Example data only.

### Transcript of the video

1.  0:00

    CHECKLIST FOR HOTELS

    Three checks before you send

    Daily PMS export to the revenue tool, checked by eye

2.  0:02

    1

    Every stay date is there

    In order, with no gaps. A gap is a missing date.

    Stay date | Rooms sold | Rooms available

    12 Nov | 18 | 40

    13 Nov (missing) | flagged: row absent

    14 Nov | 21 | 40

3.  0:08

    2

    No total rows left in

    A total row reads as an extra day.

    Stay date | Rooms sold | Rooms available

    12 Nov | 18 | 40

    13 Nov | 20 | 40

    Total | 38 | 80

4.  0:13

    3

    Rooms sold never exceed available

    If they do, the export is wrong.

    Stay date | Rooms sold | Rooms available

    12 Nov | 18 | 40

    13 Nov | 46 | 40

5.  0:18

    If a check fails, send nothing and say why.

    Fix the export, then run all three checks again.

6.  0:23

    We built these checks into a pipeline.

    A person only looks when something is flagged.

    Daily manual job across properties? Vivified Tech builds the software that takes over repetitive hotel admin. Free assessment.

    vivifiedtech.com/case-studies

    Link in the post.

## Step 1: Set up the two tabs

1.  Create a Google Sheet. Rename the first tab **Export** and add a second tab called **Checks**.
2.  Each day, in the **Export** tab, open **File**, then **Import**, choose the PMS file (CSV or Excel), select **Replace current sheet** and click **Import**. Google says this replaces the current sheet's contents, and its [import page](https://support.google.com/docs/answer/40608?hl=en) lists the file types and options.
3.  After every import, look at the tab name. The formulas read a tab called **Export**, so if it has another name, rename it back. Until you do, A6 on the **Checks** tab says **Send nothing**.
4.  Google notes that not every file type offers every import option. If yours has no **Replace current sheet**, select all cells in the **Export** tab, press Delete, select cell A1 and choose **Replace data starting at selected cell**.
5.  Row 1 of the **Export** tab must hold the column names and the data must start in row 2. If your export has title lines above the column names, delete them.
6.  Find the three columns the checks need: the stay date, rooms sold and rooms available. The formulas below read them from columns A, B and C. If yours are elsewhere, see "Column names differ between PMS exports" at the end.

## Step 2: Add the formulas on the Checks tab

Type each formula into the cell named in the label on its block. Where the label says fill down, copy the cell, select the cells below it down to row 1000 and paste. Columns F to K work row by row, so each row number matches the row in the **Export** tab.

Copy the three columns into the **Checks** tab. Put this in F2, copy it into G2 and H2 so they read the next two columns, then fill F2 to H2 down to row 1000:

```
=IF(Export!A2="","",Export!A2)
```

Mark the days that do not follow the day before. Put this in I3 and fill down to row 1000:

```
=IF(NOT(ISNUMBER(F3)),"",IFERROR(IF(F3-F2=1,"ok","check"),"check"))
```

Mark rows that are not a stay date, such as a total row or a footer. Put this in J2 and fill down:

```
=IF(COUNTA(Export!2:2)=0,"",IF(ISNUMBER(F2),"ok","check"))
```

Mark days where rooms sold are above rooms available, or either is not a number. It reads column J, so it skips rows that are not a stay date. Put this in K2 and fill down:

```
=IF(J2<>"ok","",IF(AND(ISNUMBER(G2),ISNUMBER(H2),G2<=H2),"ok","check"))
```

Now count the problems. Type the check names in A2, A3 and A4 (for example "1. Every stay date, in order, no gaps"), then put one formula in each of B2, B3 and B4:

```
=COUNTIF(I3:I1000,"check")+IF(COUNT(F2:F1000)<1,1,0)
```

```
=COUNTIF(J2:J1000,"check")+COUNTIF(Export!A2:Z1000,"*total*")
```

```
=COUNTIF(K2:K1000,"check")
```

Last, the verdict in A6. If the **Export** tab is missing or renamed, B2 to B4 show errors and A6 still says **Send nothing**:

```
=IFERROR(IF(SUM(B2:B4),"Send nothing.","All passed. Send."),"Send nothing.")
```

Each count shows 0 when its check passes. A6 says **All passed. Send.** only when all three are 0, and **Send nothing.** otherwise. In columns I, J and K the word `check` marks the offending row, and the row number is the same as in the **Export** tab.

Try it before trusting it. Type these illustrative rows into the **Export** tab under the column names. Check 1 finds the missing 13 Nov, check 2 finds the total row and check 3 finds the oversold night, so B2 to B4 should show 1, 2 and 1:

```
Stay date,Rooms sold,Rooms available
2026-11-12,18,40
2026-11-14,46,40
Total,64,80
```

## Step 3: Optional, name each problem row with a script

The formulas give a count. A short Apps Script names each problem row. It reads the open sheet, so it needs neither the tab name nor the formulas.

1.  In the spreadsheet, open **Extensions**, then **Apps Script**. Delete the sample code and paste this:

```js
const COLUMNS = {
  date: 'Stay date',
  sold: 'Rooms sold',
  available: 'Rooms available'
};
const HEADER_ROW = 1;
const TOTAL_WORDS = ['total'];
const DAY_MS = 24 * 60 * 60 * 1000;
const SHOW = 5;

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Export checks')
    .addItem('Run all three checks', 'runChecks')
    .addToUi();
}

function runChecks() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const timeZone = sheet.getParent().getSpreadsheetTimeZone();
  const format = (date) =>
    Utilities.formatDate(date, timeZone, 'd MMM yyyy');
  const values = sheet.getDataRange().getValues();
  SpreadsheetApp.getUi().alert(checkExport(values, format));
}

function isDate(cell) {
  return cell instanceof Date && !isNaN(cell.getTime());
}

function at(row, text) {
  return 'row ' + row.number + ': ' + text;
}

function findColumns(values) {
  const header = (values[HEADER_ROW - 1] || []).map((cell) =>
    String(cell).trim()
  );
  const columns = {};
  for (const key in COLUMNS) {
    columns[key] = header.indexOf(COLUMNS[key]);
    if (columns[key] === -1) {
      throw new Error('Column not found: ' + COLUMNS[key]);
    }
  }
  return columns;
}

function findGaps(rows, col, format) {
  const dated = rows.filter((row) => isDate(row.cells[col.date]));
  if (dated.length === 0) return ['no stay dates found'];
  const problems = [];
  for (let i = 1; i < dated.length; i++) {
    const before = dated[i - 1].cells[col.date];
    const day = dated[i].cells[col.date];
    const step = Math.round((day - before) / DAY_MS);
    if (step === 1) continue;
    let why = 'out of order';
    if (step === 0) why = 'duplicate';
    if (step > 1) why = step - 1 + ' missing';
    problems.push(
      at(
        dated[i],
        format(day) + ' follows ' + format(before) + ', ' + why
      )
    );
  }
  return problems;
}

function findTotals(rows, col) {
  const problems = [];
  rows.forEach((row) => {
    const text = row.cells.join(' ').toLowerCase();
    const named = TOTAL_WORDS.some((word) => text.includes(word));
    if (isDate(row.cells[col.date]) && !named) return;
    const label = row.cells.filter((cell) => cell !== '')[0];
    problems.push(at(row, String(label).slice(0, 30)));
  });
  return problems;
}

function findOversold(rows, col) {
  const problems = [];
  rows
    .filter((row) => isDate(row.cells[col.date]))
    .forEach((row) => {
      const sold = row.cells[col.sold];
      const available = row.cells[col.available];
      if (typeof sold !== 'number' || typeof available !== 'number') {
        problems.push(at(row, 'a room count is not a number'));
      } else if (sold > available) {
        problems.push(
          at(row, sold + ' sold, ' + available + ' available')
        );
      }
    });
  return problems;
}

function checkExport(values, format) {
  let col;
  try {
    col = findColumns(values);
  } catch (error) {
    return error.message;
  }
  const rows = [];
  values.slice(HEADER_ROW).forEach((cells, index) => {
    if (cells.every((cell) => cell === '')) return;
    rows.push({ number: HEADER_ROW + 1 + index, cells: cells });
  });
  const checks = [
    [
      '1. Every stay date, in order, no gaps',
      findGaps(rows, col, format)
    ],
    ['2. No total rows or rows without a date', findTotals(rows, col)],
    ['3. Rooms sold never exceed available', findOversold(rows, col)]
  ];
  const lines = [];
  checks.forEach(([name, problems]) => {
    lines.push(name + ': ' + (problems.length === 0 ? 'PASS' : 'FAIL'));
    problems
      .slice(0, SHOW)
      .forEach((problem) => lines.push('   ' + problem));
    if (problems.length > SHOW) {
      lines.push('   and ' + (problems.length - SHOW) + ' more');
    }
  });
  const failed = checks.some(([, problems]) => problems.length > 0);
  lines.push('');
  lines.push(
    failed
      ? 'Send nothing. Fix the export, then run all three checks again.'
      : 'All three checks passed.'
  );
  return lines.join('\n');
}
```

2.  Change the three names in `COLUMNS` to the exact column names in your export, and `HEADER_ROW` if the names are not in row 1.
3.  Click **Save**, then reload the spreadsheet. A menu called **Export checks** appears, because the script has an `onOpen` function. [Google's menu guide](https://developers.google.com/apps-script/guides/menus) says only scripts bound to the file, as this one is, can add menus.
4.  Open the **Export** tab, then choose **Export checks**, then **Run all three checks**. The first run asks for permission. Google warns that the app is unverified because you wrote it. Choose **Advanced**, then go to the project. [Google describes the authorization flow](https://developers.google.com/apps-script/guides/services/authorization).

A failing export shows a message like this, with your own rows:

```
1. Every stay date, in order, no gaps: FAIL
   row 3: 14 Nov 2026 follows 12 Nov 2026, 1 missing
2. No total rows or rows without a date: FAIL
   row 4: Total
3. Rooms sold never exceed available: FAIL
   row 3: 46 sold, 40 available

Send nothing. Fix the export, then run all three checks again.
```

## Step 4: Use it every day

1.  Import the day's PMS file into the **Export** tab with **Replace current sheet** and check the tab is still called **Export**.
2.  Open **Checks** and read A6, or run **Export checks** if you use the script.
3.  If all three pass, send the file.
4.  If one fails, send nothing. Tell whoever is waiting for the file which check failed and why, for example: "Today's file is not sent. Check 3 failed: 13 Nov shows 46 rooms sold with 40 available. I am re-exporting and will send it when all three checks pass."
5.  Fix the export, import it again and run all three checks again, not only the one that failed. A new export can break a different check.

## Details and limits

Checked on 2026-10-21. We ran the formulas on example sheets with each failure and the script on example data with each failure, using fake dates and numbers. Try both on a copy of your own export before you rely on them.

### What it does

A missing date leaves a hole in the data. A total row left in can be read as an extra day. Rooms sold above rooms available means the export, or the column you mapped, is wrong. The checks test these three rules on every row and judge nothing else.

### What it can't do

-   It does not know which dates you meant to export. Check 1 proves the dates run one day at a time, not that they start today or reach as far ahead as the revenue tool expects. Compare the first and last date with the range you set in the PMS.
-   Check 3 assumes the column holds the number of rooms you can sell that night. If your export shows rooms still unsold instead, sold and available cannot be compared directly.
-   A number that is wrong but possible, such as 20 rooms sold instead of 21, passes every check.
-   The total-row check flags any row in A2 to Z1000 of the **Export** tab that contains "total" in any letter case, and any row without a stay date. If your PMS labels totals in another language or with another word, add it: in the formula in B3 for example `COUNTIF(Export!A2:Z1000,"*sum*")`, and in `TOTAL_WORDS` in the script. The `*` is a wildcard, as [Google's COUNTIF page](https://support.google.com/docs/answer/3093480?hl=en) explains.

### Column names differ between PMS exports

There is no standard layout. One export may call the first column "Date", another "Stay date" or "Night". Rooms sold may be "Occupied", "Rooms sold" or "Rooms occ". Open your export and find the three columns by what they hold: the night, how many rooms were sold that night and how many rooms could be sold. In the formulas, change the column letter inside F2, G2 and H2 (for example `Export!D2` instead of `Export!A2`) and fill F2 right again. In the script, change the names in `COLUMNS`. Nothing else needs to change.

### Limits

-   The formulas read down to row 1000. A forward export of about a year of dates is far below that. For a longer export, change 1000 and fill down further.
-   The dates must be real dates. If the import leaves them as text, the checks fail, which is the safe result. Format the column as a date. If the cells stay text, change the date format in the PMS export settings.
-   The formulas use only basic functions that Google Sheets and Excel both have, but we have not written Excel steps. The script needs Google Sheets.
-   The script runs inside your Google account, and the PMS export goes into your own Google Drive, so follow your own rules on where stay data may be stored.

### When this stops being enough

When there are many properties, someone still opens each PMS, exports, imports, checks and sends, every day. We built these three checks into a pipeline that pulls the data, converts it to the revenue tool's format, runs the checks and emails the file, so a person only looks when something is flagged. We automate this kind of work. If yours is not a fit, we will say so.

See this in practice in the [hotel data pipeline case study](https://vivifiedtech.com/case-studies/hotel-data-pipeline/), or read [how the no-build-fee model works](https://vivifiedtech.com/how-it-works/).

## Sources

1.  [Import data sets & spreadsheets (Google Docs Editors Help)](https://support.google.com/docs/answer/40608?hl=en)
2.  [COUNTIF (Google Docs Editors Help)](https://support.google.com/docs/answer/3093480?hl=en)
3.  [Custom Menus in Google Workspace (Apps Script)](https://developers.google.com/apps-script/guides/menus)
4.  [Authorization for Google Services (Apps Script)](https://developers.google.com/apps-script/guides/services/authorization)

## Related guides

-   [Shift handover form with a morning email](https://vivifiedtech.com/guides/shift-handover-form-morning-email/)
-   [Auto-acknowledge hotel enquiries in Gmail](https://vivifiedtech.com/guides/gmail-enquiry-inbox-for-hotels/)

## Have a task like this?

Tell us the repetitive task and we'll say honestly whether we can automate it.

[Get a free assessment](https://vivifiedtech.com/contact/)