Validate an Avails Sheet Against IFTA + EMA Rules

Shipping an avails sheet to a platform with even one invalid territory code means the platform’s ingest rejects the whole file and you waste a day on the round-trip. Same for an End date before a Start date, an Exclusive field that reads “Y” instead of “Yes”, or a Right column with a term the platform doesn’t recognize.

The fix is pre-ingest validation. Run your sheet through a validator before you send it. This tutorial walks through the openrights-avails-validator.gs Apps Script — paste it in, run one function, get back a Validation Errors sheet with every problem flagged.

Step 1: Structure Your Avails Sheet

The validator expects the OpenRights 7-column generic avails schema:

A: Title | B: Territory | C: Right | D: Start | E: End | F: Exclusive | G: Notes

Data starts on row 2. If your source file has more columns (platform-specific extras), put them to the right of column G — the validator ignores them.

Step 2: Paste the Validator Script

Open Extensions > Apps Script. Paste in the openrights-avails-validator.gs file. The core function structure:

function validateAvails() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Paste Generic Avails Here");
  const data = sheet.getRange(2, 1, sheet.getLastRow()-1, 7).getValues();
  const errors = [];

  data.forEach((row, i) => {
    const [title, territory, right, start, end, exclusive] = row;
    const rowNum = i + 2;

    if (!title) errors.push([rowNum, "Title", "BLANK"]);
    if (!VALID_TERRITORIES.has(territory)) errors.push([rowNum, "Territory", `Invalid: ${territory}`]);
    if (!VALID_RIGHTS.has(right)) errors.push([rowNum, "Right", `Invalid: ${right}`]);
    if (start && end && new Date(end) < new Date(start))
      errors.push([rowNum, "Dates", "End before Start"]);
    if (!["Yes","No",""].includes(exclusive))
      errors.push([rowNum, "Exclusive", `Must be Yes/No, got: ${exclusive}`]);
  });

  writeErrors(errors);
}

VALID_TERRITORIES is a Set of IFTA/ISO α-2 codes. VALID_RIGHTS is the EMA-recognized right taxonomy (SVOD, AVOD, TVOD, Linear, Theatrical, etc.).

Step 3: Run and Review

Run validateAvails from the editor. The first run will ask for permission to access your spreadsheet — grant it. The function writes results to a new “Validation Errors” sheet. Each row: source row number, field name, error description.

Sort by field. Territory errors cluster together — if you see 40 of them all saying Invalid: UK, that’s one find-and-replace away from being fixed (IFTA uses GB).

Step 4: Distinguish Hard vs. Soft Errors

• Hard errors — invalid territory, invalid right, End-before-Start. The platform will reject these.
• Soft errors — blank Title, blank Exclusive. Some platforms accept blanks and default; others reject. Check the platform spec.

The script treats everything as an error; you decide which matter. Pro tip: add a “Severity” column manually to your Validation Errors sheet and filter on it.

Step 5: Re-Run After Fixes

Every fix on the source sheet, re-run the validator. You’re done when Validation Errors is empty (or only contains soft errors you’ve decided to accept).

Common Issues

• Ambiguous territory codes. UK vs GB, XX for “worldwide”, region codes like EMEA that aren’t single territories. The validator flags anything not in its IFTA/ISO set — if you need region codes, extend VALID_TERRITORIES or expand them first via the Normalize Territory Codes workflow.
• Date format drift. The validator uses new Date() — 2026-04-15 and 4/15/2026 both parse, but 15-Apr-26 may not. Normalize to ISO before validating.
• EMA vocabulary updates. The right taxonomy changes. If a platform adds a new right (“FAST”), the validator will flag it as invalid until you add it to VALID_RIGHTS.

Download the Template + Script

Run the Avails Data Quality Scorecard for the formula-based checks. Layer the openrights-avails-validator.gs script on top for full territory/right taxonomy validation.