gistfile1.txt

const SHEET_NAMES = {
  PACKAGES: "📦 Packages",
  PACKAGE_ITEMS: "💍 Package Items"
}

/**
 * UTILITY FUNCTION (Shared)
 * Takes a string like "7.331,49 DKK" or "1250,00" and returns a clean number.
 */
// Improved parser to handle the specific CSV quotes and Danish commas
function parseDkkPrice(val) {
  if (val === null || val === undefined || val === "") return 0;
  let str = val.toString().trim();
  
  // Remove quotes, "DKK", and thousands-separator dots
  str = str.replace(/"/g, '').replace(' DKK', '').replace(/\./g, '');
  // Switch decimal comma to dot
  str = str.replace(',', '.');
  
  return parseFloat(str) || 0;
}

/**
 * Fetches the latest gold price from the nested JSON response.
 * Usage: =GET_GOLD_PRICE()
 * @customfunction
 */
function GET_GOLD_PRICE() {
  const url = "https://7n6w9i5x44.execute-api.eu-north-1.amazonaws.com/rates?latest=true";
  
  try {
    const response = UrlFetchApp.fetch(url);
    const json = JSON.parse(response.getContentText());
    
    // Navigating: Outer Array [0] -> Inner Array [0] -> pairs -> AU -> EUR
    const price = json[0][0].pairs.AU.EUR / 100;
    
    return price;
  } catch (e) {
    return "Error: Check JSON structure";
  }
}

/**
 * CUSTOM FUNCTION: =SUM_DKK_COSTS(A1:A10)
 * Uses the utility function to sum up costs in a range.
 */
function SUM_DKK_COSTS(input) {
  const flatInput = Array.isArray(input) ? input.flat() : [input];
  const total = flatInput.reduce((sum, cell) => sum + parseDkkPrice(cell), 0);
  
  // Return as a number so you can format it using Google Sheets' own currency tools
  return total;
}

/**
 * Compares Package totals with Item sums and highlights mismatches.
 * Returns and displays the count of failed packages.
 */
function CHECK_PACKAGE_SUMS() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const pkgSheet = ss.getSheetByName(SHEET_NAMES.PACKAGES);
  const itemSheet = ss.getSheetByName(SHEET_NAMES.PACKAGE_ITEMS);
  
  if (!pkgSheet || !itemSheet) return "Error: Sheets not found";

  // pkgData targets Column A (index 0) for PIDs
  const pkgData = pkgSheet.getRange("A2:E" + pkgSheet.getLastRow()).getValues();
  // itemData targets Column B (index 1) for Item IDs and Column C (index 2) for Package IDs
  const itemData = itemSheet.getRange("A2:L" + itemSheet.getLastRow()).getValues();
  
  const itemSums = {};
  const foundItemIds = new Set();
  
  const seenPkgIds = new Set();
  const duplicatePkgIds = new Set();
  const seenItemRowIds = new Set();
  const duplicateItemRowIds = new Set();

  const failedIds = [];

  // 1. Map sums from Package Items & Check for Item Duplicates (Column B)
  itemData.forEach(row => {
    const itemId = String(row[1]).trim(); // Column B: Item ID
    const pkgId = String(row[2]).trim();  // Column C: Package ID
    
    if (!pkgId || pkgId === "Package ID") return;

    // Check for duplicate Item IDs in Column B
    if (itemId && itemId !== "" && itemId !== "Item ID") {
      if (seenItemRowIds.has(itemId)) {
        duplicateItemRowIds.add(itemId);
      }
      seenItemRowIds.add(itemId);
    }

    itemSums[pkgId] = (itemSums[pkgId] || 0) + parseDkkPrice(row[11]);
    foundItemIds.add(pkgId);
  });

  // 2. Identify mismatches & check for Duplicate Package IDs (Column A)
  pkgData.forEach(row => {
    const pkgId = String(row[0]).trim(); // Column A: PID
    if (!pkgId || pkgId === "PID") return;

    // Check for duplicate Package IDs in Column A
    if (seenPkgIds.has(pkgId)) {
      duplicatePkgIds.add(pkgId);
    }
    seenPkgIds.add(pkgId);

    const pkgTotal = parseDkkPrice(row[4]);
    
    if (!foundItemIds.has(pkgId)) {
      failedIds.push("#" + pkgId + " (Missing from Items)");
      return;
    }

    const calculatedSum = itemSums[pkgId] || 0;
    if (Math.abs(pkgTotal - calculatedSum) > 0.1) {
      failedIds.push("#" + pkgId + " (Sum mismatch)");
    }
  });

  // 3. Compile Errors
  let errorMessages = [...failedIds];
  
  if (duplicatePkgIds.size > 0) {
    errorMessages.push("Duplicate PIDs (Pkg Sheet): " + Array.from(duplicatePkgIds).join(", "));
  }
  if (duplicateItemRowIds.size > 0) {
    errorMessages.push("Duplicate Item IDs (Items Sheet): " + Array.from(duplicateItemRowIds).join(", "));
  }

  if (errorMessages.length === 0) {
    return "✅ All packages add up & no duplicates found";
  } else {
    return "❌ Issues found: " + errorMessages.join(" | ");
  }
}

/**
 * Summarizes weight from 'Package Items' based on Material (GOLD) and Purity.
 * Handles: "0.585", ".75" (converted to 750), "0.333 (8K)", and "999".
 * @customfunction
 */
/**
 * Summarizes weight from 'Package Items' based on Material and Purity.
 * @param {string} materialName "GOLD" or "SILVER"
 * @param {Array|string|number} purities Single value, array {}, or "333, 375"
 * @customfunction
 */
function SUM_METAL_WEIGHT(materialName, purities) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const itemSheet = ss.getSheetByName(SHEET_NAMES.PACKAGE_ITEMS);
  
  if (!itemSheet) return "Error: Sheet not found";

  // Material: Col G (6), Weight: Col I (8), Purity: Col K (10)
  const itemData = itemSheet.getRange("A2:K" + itemSheet.getLastRow()).getValues();
  const targetMaterial = String(materialName).toUpperCase().trim();
  
  // Handle comma-separated strings or arrays
  let purityList = [];
  if (typeof purities === 'string' && purities.includes(',')) {
    purityList = purities.split(',').map(p => p.trim());
  } else {
    purityList = Array.isArray(purities) ? purities.flat().map(p => String(p).trim()) : [String(purities).trim()];
  }

  let totalWeight = 0;

  itemData.forEach(row => {
    const material = String(row[6]).toUpperCase().trim();
    let rawPurity = String(row[10]).trim();
    const weight = parseDkkPrice(row[8]); // Uses your existing utility

    if (material === targetMaterial && rawPurity !== "") {
      // Look for decimals (e.g., .75 or .92)
      const decimalMatch = rawPurity.match(/\.(\d+)/);
      let cleanPurity = "";

      if (decimalMatch) {
        cleanPurity = decimalMatch[1];
        // Pad 2-digit decimals (e.g., .92 -> 920, .75 -> 750)
        if (cleanPurity.length === 2) {
          cleanPurity += "0";
        }
      } else {
        // Fallback for 3-digit standalone numbers (999)
        const digitMatch = rawPurity.match(/(\d{3})/);
        cleanPurity = digitMatch ? digitMatch[1] : rawPurity;
      }

      if (purityList.includes(cleanPurity)) {
        totalWeight += weight;
      }
    }
  });

  return totalWeight;
}