Skip to content

copyFilteredDataForAllTiers

copyFilteredDataForAllTiers copies filtered player data from tier-specific Data Import sheets into their corresponding tier sheets, keeping only players whose contract status is Free Agent or Draft Eligible.

It's an optimized batch operation: rather than hitting the Contract Import sheet once per tier, it reads it a single time up front and builds an in-memory RSC ID → Contract Status lookup map. Then for each of the nine tiers, it reads the tier's Data Import sheet, filters rows by looking up each player's status in that map, clears any previously written data from the tier sheet (starting at row 9), and writes the filtered results in one batch operation.

The end result is that each tier sheet's player pool (starting at row 9) reflects only the Free Agents and Draft Eligible players for that tier, refreshed cleanly on every run.

Sheet Location

How to Use

  1. Open the Google Sheet
  2. Go to Extensions > Apps Script
  3. Paste the code below into the editor
  4. Save and run

Code

/**
 * Copies filtered player data from tier-specific Data Import sheets to tier sheets
 * Optimized version - reads Contract Import once, batches all operations
 * Only includes players with "Free Agent" or "Draft Eligible" contract status
 */
function copyFilteredDataForAllTiers() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tiers = ["Premier", "Master", "Elite", "Veteran", "Rival", "Challenger", "Prospect", "Contender", "Amateur"];

  // STEP 1: Build contract map ONCE for all tiers (major performance gain)
  const contractSheet = ss.getSheetByName("Contract Import");
  if (!contractSheet) {
    Logger.log("Contract Import sheet not found");
    return;
  }

  const contractHeaders = contractSheet.getRange(1, 1, 1, contractSheet.getLastColumn()).getValues()[0];
  const rscIdIndex = contractHeaders.indexOf("RSC ID");
  const statusIndex = contractHeaders.indexOf("Contract Status");

  if (rscIdIndex === -1 || statusIndex === -1) {
    Logger.log("Required columns not found in Contract Import.");
    return;
  }

  const contractData = contractSheet.getRange(2, 1, contractSheet.getLastRow() - 1, contractHeaders.length).getValues();
  const contractMap = {};
  contractData.forEach(row => {
    const rscId = String(row[rscIdIndex]).trim();
    const status = String(row[statusIndex]).trim();
    if (rscId) contractMap[rscId] = status;
  });

  Logger.log("Built contract map with %s entries", Object.keys(contractMap).length);

  // STEP 2: Process all tiers in batch
  tiers.forEach(tier => {
    const sourceSheet = ss.getSheetByName(tier + " Data Import");
    const targetSheet = ss.getSheetByName(tier);

    if (!sourceSheet || !targetSheet) {
      Logger.log("Sheets not found for tier: " + tier);
      return;
    }

    // Read all source data at once
    const lastSourceRow = sourceSheet.getLastRow();
    if (lastSourceRow < 2) {
      Logger.log("No data for tier: " + tier);
      return;
    }

    const sourceData = sourceSheet.getRange(2, 1, lastSourceRow - 1, 14).getValues();

    // Filter in memory (no logging in loop for speed)
    const filtered = sourceData.filter(row => {
      const rscId = String(row[1]).trim();
      const status = contractMap[rscId];
      return status === "Free Agent" || status === "Draft Eligible";
    });

    // Clear and write in single operation
    const lastTargetRow = targetSheet.getLastRow();
    if (lastTargetRow >= 9) {
      targetSheet.getRange(9, 1, lastTargetRow - 8, 14).clearContent();
    }

    if (filtered.length > 0) {
      targetSheet.getRange(9, 1, filtered.length, 14).setValues(filtered);
    }

    Logger.log("%s: Pasted %s rows", tier, filtered.length);
  });

  Logger.log("Completed all tiers");
}

Notes

  • This script depends on both populateTierDataImportSheets having been run first — the Data Import sheets need to be populated before this script has anything to filter from.
  • Player data is written into tier sheets starting at row 9. Rows 1–8 are left untouched, so any headers, labels, or static content you have above that line are safe.
  • The contract status filter is case-sensitive — values in the Contract Import sheet must be exactly Free Agent or Draft Eligible (no extra spaces, no alternate capitalization).
  • The source data range is hardcoded to 14 columns (getRange(..., 14)). If the Data Import sheet layout changes column count, this will need updating.
  • Because the contract map is built once and reused, this is significantly faster than the naive per-tier approach — especially relevant as the league roster grows.