5 Google Sheets AppScript Automations Every Catalog Manager Needs

Google Sheets is where most catalog management starts, and for good reason. It is free, it runs in the browser, everyone on the team can access it simultaneously, and it requires zero IT setup. But a raw spreadsheet still leaves you doing repetitive manual work: checking territory codes by eye, scanning for duplicates, hunting for overlapping rights windows, and remembering to check what is about to expire.

Apps Script changes that. It is JavaScript that runs directly inside your Google Sheet, with no installation, no external tools, and no cost. You open a code editor, paste a function, and suddenly your spreadsheet can validate data, send emails, and reorganize itself on a schedule.

This tutorial gives you five copy-paste-ready automations. Each one solves a specific problem that catalog managers deal with weekly or daily. Your sheet should have these columns in this order: Title (A), Territory (B), Window Type (C), Start Date (D), End Date (E), Exclusivity (F), and Status (G).

How to Install

Before pasting any code, open the Apps Script editor:

1. Open your Google Sheet
2. Go to Extensions > Apps Script
3. Delete any placeholder code in the editor
4. Paste all five functions below into the same script file
5. Add the custom menu function (shown at the end) so you can run everything from a menu bar
6. Click the floppy disk icon to save
7. Close the Apps Script editor and refresh your sheet — you will see a new “Catalog Tools” menu

Automation 1: Territory Code Validator

This function checks every territory code in column B against a list of valid ISO 3166-1 alpha-2 codes. Invalid codes get highlighted in red so you can spot and fix them immediately.

function validateTerritoryCodes() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return;

  var validCodes = [
    "AF","AL","DZ","AS","AD","AO","AG","AR","AM","AU","AT","AZ","BS","BH","BD",
    "BB","BY","BE","BZ","BJ","BT","BO","BA","BW","BR","BN","BG","BF","BI","CV",
    "KH","CM","CA","CF","TD","CL","CN","CO","KM","CG","CD","CR","CI","HR","CU",
    "CY","CZ","DK","DJ","DM","DO","EC","EG","SV","GQ","ER","EE","SZ","ET","FJ",
    "FI","FR","GA","GM","GE","DE","GH","GR","GD","GT","GN","GW","GY","HT","HN",
    "HU","IS","IN","ID","IR","IQ","IE","IL","IT","JM","JP","JO","KZ","KE","KI",
    "KP","KR","KW","KG","LA","LV","LB","LS","LR","LY","LI","LT","LU","MG","MW",
    "MY","MV","ML","MT","MH","MR","MU","MX","FM","MD","MC","MN","ME","MA","MZ",
    "MM","NA","NR","NP","NL","NZ","NI","NE","NG","NO","OM","PK","PW","PA","PG",
    "PY","PE","PH","PL","PT","QA","RO","RU","RW","KN","LC","VC","WS","SM","ST",
    "SA","SN","RS","SC","SL","SG","SK","SI","SB","SO","ZA","SS","ES","LK","SD",
    "SR","SE","CH","SY","TW","TJ","TZ","TH","TL","TG","TO","TT","TN","TR","TM",
    "TV","UG","UA","AE","GB","US","UY","UZ","VU","VE","VN","YE","ZM","ZW"
  ];

  var range = sheet.getRange(2, 2, lastRow - 1, 1);
  var values = range.getValues();
  var backgrounds = [];
  var errorCount = 0;

  for (var i = 0; i < values.length; i++) {
    var code = String(values[i][0]).trim().toUpperCase();
    if (code === "") {
      backgrounds.push([null]);
    } else if (validCodes.indexOf(code) === -1) {
      backgrounds.push(["#ffcccc"]);
      errorCount++;
    } else {
      backgrounds.push([null]);
      values[i][0] = code; // Normalize to uppercase
    }
  }

  range.setBackgrounds(backgrounds);
  range.setValues(values);

  SpreadsheetApp.getUi().alert(
    errorCount > 0
      ? errorCount + " invalid territory code(s) highlighted in red."
      : "All territory codes are valid."
  );
}

Automation 2: Duplicate Detector

Duplicate title-territory-window combinations cause confusion and conflicting avails data. This function scans for rows where the Title, Territory, and Window Type are identical and highlights them in orange.

function findDuplicates() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return;

  var data = sheet.getRange(2, 1, lastRow - 1, 3).getValues();
  var seen = {};
  var duplicateRows = [];

  // Build a key from Title + Territory + Window and track row indices
  for (var i = 0; i < data.length; i++) {
    var key = String(data[i][0]).trim().toLowerCase() + "|" +
              String(data[i][1]).trim().toUpperCase() + "|" +
              String(data[i][2]).trim().toLowerCase();

    if (seen.hasOwnProperty(key)) {
      // Mark both the original and this duplicate
      if (duplicateRows.indexOf(seen[key]) === -1) {
        duplicateRows.push(seen[key]);
      }
      duplicateRows.push(i);
    } else {
      seen[key] = i;
    }
  }

  // Reset backgrounds on columns A-C
  var fullRange = sheet.getRange(2, 1, lastRow - 1, 3);
  var backgrounds = [];
  for (var i = 0; i < lastRow - 1; i++) {
    if (duplicateRows.indexOf(i) !== -1) {
      backgrounds.push(["#ffcc80", "#ffcc80", "#ffcc80"]);
    } else {
      backgrounds.push([null, null, null]);
    }
  }
  fullRange.setBackgrounds(backgrounds);

  SpreadsheetApp.getUi().alert(
    duplicateRows.length > 0
      ? duplicateRows.length + " duplicate row(s) highlighted in orange."
      : "No duplicates found."
  );
}

Automation 3: Rights Window Overlap Checker

This is the one that catches expensive mistakes. If you have granted exclusive SVOD rights to Title X in France from Jan 1 to Dec 31, you cannot also grant exclusive SVOD rights to another buyer for France during the same period. This function finds those conflicts.

function checkWindowOverlaps() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  if (lastRow < 3) return;

  // Read Title (A), Territory (B), Window (C), Start (D), End (E), Exclusivity (F)
  var data = sheet.getRange(2, 1, lastRow - 1, 6).getValues();
  var conflicts = [];

  for (var i = 0; i < data.length; i++) {
    if (String(data[i][5]).trim().toLowerCase() !== "exclusive") continue;
    var startA = new Date(data[i][3]);
    var endA = new Date(data[i][4]);
    if (isNaN(startA) || isNaN(endA)) continue;

    var keyA = String(data[i][0]).trim().toLowerCase() + "|" +
               String(data[i][1]).trim().toUpperCase() + "|" +
               String(data[i][2]).trim().toLowerCase();

    for (var j = i + 1; j < data.length; j++) {
      if (String(data[j][5]).trim().toLowerCase() !== "exclusive") continue;

      var keyB = String(data[j][0]).trim().toLowerCase() + "|" +
                 String(data[j][1]).trim().toUpperCase() + "|" +
                 String(data[j][2]).trim().toLowerCase();

      if (keyA !== keyB) continue;

      var startB = new Date(data[j][3]);
      var endB = new Date(data[j][4]);
      if (isNaN(startB) || isNaN(endB)) continue;

      // Check for date overlap: A starts before B ends AND B starts before A ends
      if (startA <= endB && startB <= endA) {
        conflicts.push(i);
        conflicts.push(j);
      }
    }
  }

  // Highlight conflicts in red on columns D and E
  var bgColors = [];
  for (var i = 0; i < data.length; i++) {
    if (conflicts.indexOf(i) !== -1) {
      bgColors.push(["#ff9999", "#ff9999"]);
    } else {
      bgColors.push([null, null]);
    }
  }
  sheet.getRange(2, 4, lastRow - 1, 2).setBackgrounds(bgColors);

  SpreadsheetApp.getUi().alert(
    conflicts.length > 0
      ? (conflicts.length / 2) + " overlapping exclusive window pair(s) found and highlighted."
      : "No overlapping exclusive windows detected."
  );
}

Automation 4: Expiry Alert Emailer

This function checks for rights windows expiring within the next 30 days and sends you a summary email. Set it up on a daily trigger so you never miss an expiry deadline.

function sendExpiryAlerts() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return;

  var data = sheet.getRange(2, 1, lastRow - 1, 5).getValues();
  var today = new Date();
  today.setHours(0, 0, 0, 0);
  var threshold = new Date(today.getTime() + 30 * 24 * 60 * 60 * 1000);
  var expiringItems = [];

  for (var i = 0; i < data.length; i++) {
    var endDate = new Date(data[i][4]);
    if (isNaN(endDate)) continue;
    endDate.setHours(0, 0, 0, 0);

    if (endDate >= today && endDate <= threshold) {
      var daysLeft = Math.ceil((endDate - today) / (24 * 60 * 60 * 1000));
      expiringItems.push({
        title: data[i][0],
        territory: data[i][1],
        window: data[i][2],
        endDate: endDate.toLocaleDateString(),
        daysLeft: daysLeft
      });
    }
  }

  if (expiringItems.length === 0) return;

  // Sort by days remaining (most urgent first)
  expiringItems.sort(function(a, b) { return a.daysLeft - b.daysLeft; });

  var body = "The following rights windows expire within 30 days:\n\n";
  for (var i = 0; i < expiringItems.length; i++) {
    var item = expiringItems[i];
    body += "- " + item.title + " | " + item.territory + " | " + item.window +
            " | Expires: " + item.endDate + " (" + item.daysLeft + " days)\n";
  }

  body += "\nReview your catalog: " + SpreadsheetApp.getActiveSpreadsheet().getUrl();

  MailApp.sendEmail({
    to: Session.getActiveUser().getEmail(),
    subject: "Rights Expiry Alert: " + expiringItems.length + " window(s) expiring soon",
    body: body
  });
}

To set up the daily trigger: in the Apps Script editor, click the clock icon (Triggers) in the left sidebar, then click “Add Trigger.” Select sendExpiryAlerts as the function, “Time-driven” as the event source, “Day timer” as the type, and pick a time window like 8:00-9:00 AM.

Automation 5: Auto-Sort by Expiry

A simple but surprisingly useful function. It sorts the entire sheet by the End Date column so that the most urgent expiries bubble to the top.

function sortByExpiry() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return;

  var range = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn());
  range.sort({ column: 5, ascending: true });

  SpreadsheetApp.getUi().alert("Sheet sorted by expiry date (earliest first).");
}

Adding the Custom Menu

Paste this function into the same script file. It creates a “Catalog Tools” menu in your sheet’s menu bar that gives you one-click access to all five automations:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Catalog Tools")
    .addItem("Validate Territory Codes", "validateTerritoryCodes")
    .addItem("Find Duplicates", "findDuplicates")
    .addItem("Check Window Overlaps", "checkWindowOverlaps")
    .addItem("Send Expiry Alerts", "sendExpiryAlerts")
    .addItem("Sort by Expiry Date", "sortByExpiry")
    .addToUi();
}

After saving and refreshing the sheet, you will see “Catalog Tools” appear in the menu bar next to Help. The first time you run any function, Google will ask you to authorize the script — this is normal. Grant permission, and from that point on every automation is a single click away.

These five automations cover the most common pain points in spreadsheet-based catalog management. They catch errors before they reach buyers, surface expiring rights before you miss deadlines, and eliminate the manual scanning that eats into your actual deal-making time. When your catalog grows beyond what a spreadsheet can comfortably handle — thousands of titles, multiple team members editing simultaneously, complex holdback chains — that is the point to evaluate dedicated rights management platforms. Until then, these scripts keep your sheet working hard for you.