Incorrectly Linked Spreadsheet

Click Here to see the Google Sheet

This spreadsheet was built to help track down clips that relink incorrectly after consolidation. On the HETV show I discussed in my article 28 Bugs Later, the on-set sound team had no naming convention for the first couple of days. They just threw the show’s title in there and called it a day. If this had been Harry Potter, everything would have been labelled “HP.” Quick, simple, and guaranteed to cause chaos later. When multiple clips share the same tape name, Avid can sometimes pair them with the wrong partner. Metadata has to be unique, or clips can wander off and attach themselves to the wrong media.

The post-sound team had a fair enough request: send turnovers where clips aren’t linked to the wrong media. Easier said than done though. With picture, you can make a mixdown, superimpose, and fast-forward until something looks off. With sound, spotting errors with relinking is nowhere near that simple. We could have tried manually changing the tape names, but there was no guarantee it would have worked, and by that point, everything else was already going sideways. I Googled what might happen and saw it could be risky, so I committed to the long way instead of potentially disturbing the editors. That’s when I started building this spreadsheet to do the detective work for us.

All you need to do is paste an EDL of the sequence before relinking, and then paste another EDL of the sequence after relinking. The spreadsheet compares the two and adds any differences to a Comparison tab. These differences show the clips that did not relink correctly. If you ever need to use this spreadsheet for your own project, which I hope to God you don’t, because it must mean you have had to walk the same path I was writing about in 28 Bugs Later, all you need to do is update the AppScripts by replacing anytime it says HP or Z021 with the tape names you need it to focus on in your project.

/**
 * compareEDLsHPandZ021
 * ----------------------
 * Compares Before and After for reels HP and Z021, flags only real mismatches,
 * and writes all results to the "Comparison" sheet in one go.
 */
function compareEDLsHPandZ021() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const beforeSheet = ss.getSheetByName("Before");
  const afterSheet = ss.getSheetByName("After");

  if (!beforeSheet || !afterSheet) {
    SpreadsheetApp.getUi().alert("Missing 'Before' or 'After' sheet.");
    return;
  }

  let comp = ss.getSheetByName("Comparison");
  if (!comp) comp = ss.insertSheet("Comparison");
  else comp.clear();

  // Header row
  comp.getRange(1,1,1,7).setValues([["Num","Reel","Name Before","Name After","Src In","Src Out","Dst In"]]);

  const beforeData = beforeSheet.getDataRange().getValues();
  const afterData = afterSheet.getDataRange().getValues();
  const output = [];

  // --- Helpers ---
  function normalizeName(name) {
    let s = String(name||"").toUpperCase();
    s = s.replace(/\.NEW(\.\d+)?$/i, "");
    s = s.replace(/\.(WAV|MP3|AIF|MXF|MOV|MP4|AUDIO|VIDEO|AUDIO\..*)$/i, "");
    s = s.replace(/[^A-Z0-9]/g, "");
    return s;
  }

  function extractYCore(name) {
    const s = String(name||"").toUpperCase();
    const m = s.match(/Y\s*(\d{1,3})\D*(\d{1,2})/);
    if (m) return "Y" + m[1] + "-" + m[2].padStart(2,"0");
    return "";
  }

  const reelsToCheck = ["HP","Z021"];

  // Compare row by row
  const rowCount = Math.min(beforeData.length, afterData.length);
  for (let i = 1; i < rowCount; i++) {
    const bRow = beforeData[i];
    const aRow = afterData[i];
    const reel = String(bRow[1]||"").trim();

    if (!reelsToCheck.includes(reel)) continue;

    const bName = String(bRow[9]||"").trim();
    const aName = String(aRow[9]||"").trim();

    const bNorm = normalizeName(bName);
    const aNorm = normalizeName(aName);
    const bCore = extractYCore(bName);
    const aCore = extractYCore(aName);

    let isMismatch = false;

    if (reel === "HP") {
      // For HP: flag if BOTH normalized name AND Y-core differ
      isMismatch = (bNorm !== aNorm && bCore !== aCore);
    } else if (reel === "Z021") {
      // For Z021: flag if normalized name OR Y-core differ
      isMismatch = (bNorm !== aNorm || bCore !== aCore);
    }

    if (isMismatch) {
      output.push([
        bRow[0],
        reel,
        bName,
        aName,
        bRow[6],
        bRow[7],
        bRow[8]
      ]);
    }
  }

  if (output.length > 0) {
    comp.getRange(2,1,output.length,7).setValues(output);
  } else {
    comp.getRange(2,1,1,1).setValue("No mismatches found.");
  }
}

/**
 * Add menu
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Comparison Tools")
    .addItem("Compare HP + Z021", "compareEDLsHPandZ021")
    .addToUi();
}