updateCombinesMMRDelta¶

updateCombinesMMRDelta pulls MMR Delta values from an external Combines Data spreadsheet and writes them into column G of each tier's Data Import sheet, matching players by RSC ID.

On each run it reads the Combines Data spreadsheet URL from Constants B15, opens that external sheet, and builds an in-memory RSC ID → MMR Delta lookup map from its first sheet (column A = RSC ID, column F = MMR Delta). It then loops through all nine tier Data Import sheets, looks up each player's RSC ID against that map, and writes the corresponding delta value into column G — leaving the cell blank if no match is found. It correctly handles a delta of 0 as a valid value rather than treating it as empty.

Sheet Location¶

Frosty's Dumbass Notes Scouting Sheet
https://docs.google.com/spreadsheets/d/1uW9ppzsQzOJgunXpqXiaFsZIc6KTCmWUASHzmPq0n8M/

Dependencies¶

What	Where
Combines Data spreadsheet URL	Constants sheet, cell B15
External Combines sheet	First tab of the linked spreadsheet
RSC ID	Combines sheet col A / Data Import sheet col B
MMR Delta source	Combines sheet col F (index 5)
MMR Delta destination	Each tier's Data Import sheet, col G

How to Use¶

Make sure the Combines Data spreadsheet URL is entered in Constants B15
Open the Google Sheet
Go to Extensions > Apps Script
Paste the code below into the editor
Save and run

Code¶

function updateCombinesMMRDelta() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const constantsSheet = ss.getSheetByName("Constants");
  const tiers = ["Premier", "Master", "Elite", "Veteran", "Rival", "Challenger", "Prospect", "Contender", "Amateur"];

  // Get the Combines Data spreadsheet URL from Constants B15
  const combinesUrl = constantsSheet.getRange("B15").getValue();

  // Extract spreadsheet ID from URL and open the external spreadsheet
  const combinesSpreadsheetId = combinesUrl.match(/\/d\/([a-zA-Z0-9-_]+)/)[1];
  const combinesSpreadsheet = SpreadsheetApp.openById(combinesSpreadsheetId);

  // Get the first sheet from the Combines Data spreadsheet
  const combinesSheet = combinesSpreadsheet.getSheets()[0];

  if (!combinesSheet) {
    Logger.log("Combines Data sheet not found in external spreadsheet");
    return;
  }

  // Build MMR delta lookup map from Combines Data sheet
  // Column A = RSC ID, Column G = MMR Delta
  const combinesLastRow = combinesSheet.getLastRow();
  if (combinesLastRow < 2) {
    Logger.log("No data found in Combines Data sheet");
    return;
  }

  const combinesData = combinesSheet.getRange(2, 1, combinesLastRow - 1, 7).getValues();
  const mmrDeltaMap = {};

  combinesData.forEach(row => {
    const rscId = String(row[0]).trim(); // Column A
    const mmrDelta = row[5]; // Column F (index 5)
    if (rscId) {
      // Store the value even if it's 0
      mmrDeltaMap[rscId] = (mmrDelta !== null && mmrDelta !== undefined && mmrDelta !== "") ? mmrDelta : "";
    }
  });

  Logger.log("Built MMR delta map with %s entries", Object.keys(mmrDeltaMap).length);

  // Update each tier's Data Import sheet
  tiers.forEach(tier => {
    const dataImportSheet = ss.getSheetByName(tier + " Data Import");

    if (!dataImportSheet) {
      Logger.log("Sheet not found: " + tier + " Data Import");
      return;
    }

    const lastRow = dataImportSheet.getLastRow();
    if (lastRow < 2) {
      Logger.log("No data found in: " + tier + " Data Import");
      return;
    }

    // Get RSC IDs from column B (index 2)
    const rscIds = dataImportSheet.getRange(2, 2, lastRow - 1, 1).getValues();

    // Build array of MMR deltas to write to column G
    const mmrDeltas = rscIds.map(row => {
      const rscId = String(row[0]).trim();
      // Use hasOwnProperty to distinguish between "no match" and "value is 0"
      const mmrDelta = mmrDeltaMap.hasOwnProperty(rscId) ? mmrDeltaMap[rscId] : "";
      return [mmrDelta];
    });

    // Write MMR deltas to column G (column 7)
    dataImportSheet.getRange(2, 7, mmrDeltas.length, 1).setValues(mmrDeltas);

    Logger.log("Updated %s rows in %s Data Import", mmrDeltas.length, tier);
  });

  Logger.log("MMR Delta update complete for all tiers");
}

Notes¶

The script comment says column G for MMR Delta, but the actual read is from column F (index 5). The comment in the source is slightly misleading — the destination is column G of the Data Import sheets, but the source is column F of the Combines sheet.
A delta value of 0 is intentionally preserved as a real value. The map uses hasOwnProperty to distinguish between a player who wasn't found in Combines data (blank) versus one whose delta legitimately evaluated to zero.
This script opens an external spreadsheet via SpreadsheetApp.openById. The account running the script must have at least view access to the Combines Data spreadsheet or it will throw a permissions error.
If the Combines spreadsheet URL in Constants B15 is missing or malformed, the regex match will throw an uncaught error. Worth validating that cell before running.