Calculating code references for an organization​

CodeReferencesSearchScript.osts

// Secrets
const GITHUB_REPO_TOKEN = "deactivated"; // 🔐 Be careful, visible to editors

// ConfigMap
const GITHUB_ORG = "featureflagextensiveconsumer"

/**
 * Returns the GitHub search result count for a flag key in an org.
 * @param {string} org GitHub organization name
 * @param {string} flagKey LaunchDarkly flag key to search
 * @returns {Promise<number>} Number of code results found
 */
async function github_flag_count(org: string, flagKey: string, log: (message: string) => void): Promise<number> {
  const token = GITHUB_REPO_TOKEN;

  const query = encodeURIComponent(`${flagKey} org:${org}`);
  const url = `https://api.github.com/search/code?q=${query}`;

  const delay = (ms: number) => new Promise(resolve => setTimeout(resolve, ms));

  const maxRetries = 15;
  let attempts = 0;

  while (attempts < maxRetries) {
    attempts++;

    const res = await fetch(url, {
      method: "GET",
      headers: {
        "Authorization": `Bearer ${token}`,
        "Accept": "application/vnd.github+json"
      }
    });

    if (res.ok) {
      const data = (await res.json()) as { total_count: number };
      return data.total_count ?? -1;
    }

    const remainingRequests = res.headers.get("X-RateLimit-Remaining");
    if (remainingRequests === "0") {
      const resetTime = Number(res.headers.get("X-RateLimit-Reset"));
      const currentTime = Date.now() / 1000;
      const waitTimeMs = Math.max(resetTime - currentTime, 0) * 1000 + 1100;
      const waitTimeSec = Math.round(waitTimeMs / 1000)

      log(`⏳ Rate limit hit. Waiting ${waitTimeSec}s (attempt ${attempts})`);
      await delay(waitTimeMs);
      continue;
    }

    log(`❌ GitHub API error: ${res.status} ${res.statusText} (attempt ${attempts})`);
    await delay(1000); // delay 1s between non-rate-limit errors too
  }

  log("⚠️ Max retries reached.");
  return -1;
}

async function main(workbook: ExcelScript.Workbook) {
  const t0 = Date.now(); // ⏱️ Start timer

  const sheet = workbook.getActiveWorksheet();
  const startRow = 2; // Row 3 in Excel
  const colKeyIndex = 13; // Column N
  const colFilesIndex = 14; // Column O
  const cellStatus = "O1";
  const maxRowsToScan = 10000;

  const useConsole = true;
  const log = useConsole ? logToConsole : ((msg: string) => logToCell(sheet, "Z5", msg));

  if (!GITHUB_REPO_TOKEN || GITHUB_REPO_TOKEN.trim().toLowerCase() === "deactivated") {
    const statusCell = sheet.getRange(cellStatus);
    statusCell.setValue("❌ Deactivated – missing or invalid GitHub token");
    await blinkCell(statusCell, "red", 3, 250); // blink 3 times with red fill
    return;
  }

  // Set the status cell to "Running..."
  sheet.getRange(cellStatus).setValue("Running...");

  const rangeN = sheet.getRangeByIndexes(startRow, colKeyIndex, maxRowsToScan, 1);
  const valuesN = rangeN.getValues();

  let lastUsedRow = 0;
  for (let i = valuesN.length - 1; i >= 0; i--) {
    const cell = valuesN[i][0];
    if (cell !== "" && cell !== null) {
      lastUsedRow = i + 1;
      break;
    }
  }

  // Clear previous values and yellow fills in the result column
  const resultRange = sheet.getRangeByIndexes(startRow, colFilesIndex, lastUsedRow, 1);
  const resultValues = resultRange.getValues();
  const resultFills = resultRange.getFormat().getFill();

  for (let i = 0; i < resultValues.length; i++) {
    const cell = resultRange.getCell(i, 0);
    const fill = cell.getFormat().getFill();
    const color = fill.getColor()?.toLowerCase();

    // Only clear fill if it was yellow
    if (color === "#ffff00" || color === "yellow") {
      fill.clear();
    }

    cell.setValue(""); // Clear old value
  }

  const relevantValues = valuesN.slice(0, lastUsedRow);
  const output: (number | string)[][] = [];

  let processed = 0;
  let skipped = 0;
  let failed = 0;

  for (let i = 0; i < relevantValues.length; i++) {
    const flagKey = relevantValues[i][0];
    if (typeof flagKey === "string" && flagKey.trim() !== "") {

      // Call API to get the file count for the flag key
      let valueC = await github_flag_count(GITHUB_ORG, flagKey, log);

      if (valueC === -1) {
        failed++;

        // Write failure marker into the sheet
        const failCell = sheet.getRangeByIndexes(startRow + i, colFilesIndex, 1, 1);
        failCell.setValue("❌ failed");
        failCell.getFormat().getFill().clear(); // Remove any previous fill

        sheet.getRange(cellStatus).setValue(`Retrying... (${failed} failed attempts)`);
        continue;
      }

      // Immediately write the result to the respective cell in column O
      sheet.getRangeByIndexes(startRow + i, colFilesIndex, 1, 1).setValue(`${valueC} file(s)`);

      // If file count is 0, highlight the cell in light red (to emphasize possible deprecation)
      if (valueC === 0) {
        sheet.getRangeByIndexes(startRow + i, colFilesIndex, 1, 1).getFormat().getFill()
          .setColor("yellow");
      }

      processed++;

    } else {
      sheet.getRangeByIndexes(startRow + i, colFilesIndex, 1, 1).setValue(""); // Empty value for skipped rows
      skipped++;
    }

    // Update the status after every few rows processed
    if (i % 10 === 0) { // Update status every 10 rows (you can adjust this)
      const elapsedTime = Date.now() - t0; // Calculate elapsed time in milliseconds
      const seconds = Math.floor(elapsedTime / 1000) % 60;
      const minutes = Math.floor(elapsedTime / (1000 * 60)) % 60;
      const hours = Math.floor(elapsedTime / (1000 * 60 * 60)) % 24;
      const timeFormatted = `${hours}h ${minutes}m ${seconds}s`; // Format as hours, minutes, seconds

      sheet.getRange(cellStatus).setValue(`Processing... (${i + 1}/${relevantValues.length} rows) | Time: ${timeFormatted}`);
    }
  }

  const durationMs = Date.now() - t0;
  const seconds = Math.floor(durationMs / 1000) % 60;
  const minutes = Math.floor(durationMs / (1000 * 60)) % 60;
  const hours = Math.floor(durationMs / (1000 * 60 * 60));

  const durationFormatted = `${hours}h ${minutes}m ${seconds}s`;

  sheet.getRange(cellStatus).setValue(`Finished! ✅ Processed: ${processed}, Skipped: ${skipped}, Failed: ${failed} | Time: ${durationFormatted}`);

  const t1 = Date.now(); // ⏱️ End timer
  const duration = t1 - t0;

  log(`✅ Runtime: ${duration} ms`);
  log(`➡️ Processed: ${processed} rows`);
  log(`⚠️ Skipped (empty key): ${skipped} rows`);
  log(`❌ Failed (rate limit): ${failed} attempts`);
  log(`📊 Total rows checked: ${processed + skipped + failed}`);
}

async function blinkCell(cell: ExcelScript.Range, 
    color: string = "red", times: number = 2, interval: number = 300) {
  for (let i = 0; i < times; i++) {
    cell.getFormat().getFill().setColor(color);
    await new Promise(resolve => setTimeout(resolve, interval));
    cell.getFormat().getFill().clear();
    await new Promise(resolve => setTimeout(resolve, interval));
  }
}

// Logging function #1: Logs to console
function logToConsole(message: string): void {
  console.log(message);
}

// Logging function #2: Appends message to cell
function logToCell(sheet: ExcelScript.Worksheet, address: string, message: string): void {
  const cell = sheet.getRange(address);
  const oldValue = cell.getValue() as string || "";
  const newValue = oldValue + message + "\n";
  cell.setValue(newValue);
}