Loot Box Simulator

Getting Started

Build your item library first, then create bundles that reference those items. Use the Roll Simulator to test drop rates. Export to Google Sheets to share or analyse your data.

Bundle Types

Type	Behaviour	Probability
Inclusive	Exactly one item is guaranteed per roll (weighted random pick)	All entries must sum to 100%
Exclusive	Each entry rolls independently, you can get 0 to all entries	Each entry has its own 0-100% chance

Auto Weights (Inverse Value)

When creating an Inclusive bundle, link items that have a Value set, then click Auto Weights. The formula used is:

Probability[i] = (1 / Value[i]) / Σ(1 / Value[j]) × 100

Higher-value items get a lower drop rate. Lower-value items drop more often.

Google Sheets Setup

Click Export in the top bar, then open lootbox-data.xlsx in Google Sheets. You will have three sheets:

Sheet	Key Columns
Bundles	A: Bundle Name, B: Bundle Type, D: Entry Name, E: Probability, F: Linked Item, G: Sub-bundle
Items	A: Name, C: Value, D+: any parameters you added
Roll History	Filled by Apps Script: Time, Bundle, Result

Create a new sheet called Rolls to run your formula simulations. All formulas below assume the exported sheet names are unchanged.

Inclusive Bundle: Roll Formula

An inclusive bundle gives exactly one item per roll using weighted probabilities. In your Rolls sheet, put the bundle name in cell A1, then in B1 paste this:

=LET(
  bundle_name, A1,
  names,  FILTER(Bundles!D:D, Bundles!A:A=bundle_name, Bundles!B:B="inclusive"),
  probs,  VALUE(FILTER(Bundles!E:E, Bundles!A:A=bundle_name, Bundles!B:B="inclusive")),
  cumsum, SCAN(0, probs, LAMBDA(acc, v, acc + v)),
  INDEX(names, MATCH(TRUE, cumsum >= RAND() * SUM(probs), 0))
)

How it works:

Pulls all entries for that bundle from the Bundles sheet
Builds a cumulative sum of probabilities (e.g. 25, 50, 100)
Generates a random number between 0 and the total, then finds which bucket it falls into
Returns the matching entry name

Press F9 to re-roll. Each recalculate triggers a new random number. Replace A1 with your bundle name in quotes if you prefer a hardcoded reference.

Exclusive Bundle: Roll Formula

An exclusive bundle rolls each entry independently. Set up your Rolls sheet like this:

Cell	Content
A1	Your bundle name (text)
A3 downward	Entry names (manually or pulled via FILTER)
B3 downward	Probabilities (manually or pulled via FILTER)
C3 downward	Roll result formula (see below)
E3	Collect drops formula (see below)

Step 1: Pull entries automatically into A3 and B3 (array-expands down):

=FILTER(Bundles!D:D, Bundles!A:A=A1, Bundles!B:B="exclusive")   ← A3
=VALUE(FILTER(Bundles!E:E, Bundles!A:A=A1, Bundles!B:B="exclusive"))   ← B3

Step 2: Roll each entry in C3, drag down to match the number of entries:

=IF(RAND() * 100 < B3, A3, "")

Step 3: Collect all items that dropped into E3:

=IFERROR(TEXTJOIN(", ", TRUE, FILTER(C3:C20, C3:C20 <> "")), "(nothing dropped)")

Press F9 to recalculate. Each entry in column C independently passes or fails its probability check. E3 shows only the items that dropped, joined with commas.

Look Up Item Parameters After a Roll

Once you have an item name from a roll result, use XLOOKUP to pull its stats from the Items sheet. If B1 contains the rolled item name:

=XLOOKUP(B1, Items!A:A, Items!C:C)          ← returns Value
=XLOOKUP(B1, Items!A:A, Items!D:D)          ← returns first param column
=XLOOKUP(B1, Items!A:A, Items!D:Z, "(not found)")  ← returns all params

If you are on an older Sheets version without XLOOKUP, use =VLOOKUP(B1, Items!A:Z, 3, FALSE) where 3 is the column index of the field you want.

Google Apps Script: Full Roll Simulator

For a fully automated experience inside Google Sheets: go to Extensions > Apps Script, paste the script below, save, then run onOpen() once to add the Loot Box menu.

/**
 * Loot Box Simulator - Google Apps Script
 * =========================================
 * 1. Export data from the Loot Box Simulator (click Export)
 * 2. Open lootbox-data.xlsx in Google Sheets
 * 3. Go to Extensions > Apps Script
 * 4. Paste this script, click Save (Ctrl+S)
 * 5. Run onOpen() once, then use the Loot Box menu
 */

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Loot Box')
    .addItem('Roll a Bundle', 'promptRoll')
    .addItem('Roll All Bundles', 'rollAllBundles')
    .addSeparator()
    .addItem('Clear Roll History', 'clearHistory')
    .addToUi();
}

// ---- Core Logic ----

function pickWeighted(names, probs) {
  var total = probs.reduce(function(a, b) { return a + b; }, 0);
  if (total <= 0) return '(empty pool)';
  var r = Math.random() * total;
  for (var i = 0; i < names.length; i++) {
    r -= probs[i];
    if (r <= 0) return names[i];
  }
  return names[names.length - 1];
}

function rollBundle(bundleName, allRows, depth) {
  depth = depth || 0;
  if (depth > 8) return '(max depth reached)';

  var rows = allRows.filter(function(r) {
    return String(r['Bundle Name']).trim() === bundleName;
  });
  if (rows.length === 0) return '(bundle not found: ' + bundleName + ')';

  var type = String(rows[0]['Bundle Type']).toLowerCase();

  if (type === 'inclusive') {
    var names = rows.map(function(r) { return String(r['Entry Name']); });
    var probs = rows.map(function(r) { return Number(r['Probability']) || 0; });
    return pickWeighted(names, probs);
  }

  // Exclusive: each entry rolls independently
  var drops = [];
  rows.forEach(function(r) {
    if (Math.random() * 100 < (Number(r['Probability']) || 0)) {
      var sub = String(r['Sub-bundle'] || '').trim();
      if (sub) {
        drops.push(sub + ' [' + rollBundle(sub, allRows, depth + 1) + ']');
      } else {
        drops.push(String(r['Entry Name']));
      }
    }
  });
  return drops.length > 0 ? drops.join(', ') : '(nothing dropped)';
}

// ---- Sheet Helpers ----

function getBundleRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Bundles');
  if (!sheet) throw new Error('Bundles sheet not found. Export your data first.');
  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  return data.slice(1).map(function(row) {
    var obj = {};
    headers.forEach(function(h, i) { obj[h] = row[i]; });
    return obj;
  });
}

function getBundleNames(allRows) {
  var seen = {};
  return allRows
    .map(function(r) { return String(r['Bundle Name']); })
    .filter(function(n) {
      if (!n || seen[n]) return false;
      seen[n] = true;
      return true;
    });
}

function logResult(ss, bundleName, result) {
  var histSheet = ss.getSheetByName('Roll History');
  if (!histSheet) {
    histSheet = ss.insertSheet('Roll History');
    histSheet.appendRow(['Time', 'Bundle', 'Result']);
    histSheet.getRange(1, 1, 1, 3).setFontWeight('bold');
  }
  histSheet.appendRow([new Date().toLocaleString(), bundleName, result]);
}

// ---- Menu Actions ----

function promptRoll() {
  var ui = SpreadsheetApp.getUi();
  var allRows;
  try { allRows = getBundleRows(); } catch(e) { ui.alert(e.message); return; }

  var names = getBundleNames(allRows);
  var response = ui.prompt(
    'Roll a Bundle',
    'Available bundles:\n  ' + names.join('\n  ') + '\n\nEnter bundle name:',
    ui.ButtonSet.OK_CANCEL
  );
  if (response.getSelectedButton() !== ui.Button.OK) return;

  var name = response.getResponseText().trim();
  if (!name) return;

  var result = rollBundle(name, allRows, 0);
  logResult(SpreadsheetApp.getActiveSpreadsheet(), name, result);
  ui.alert(name + ' rolled:\n\n' + result);
}

function rollAllBundles() {
  var ui = SpreadsheetApp.getUi();
  var allRows;
  try { allRows = getBundleRows(); } catch(e) { ui.alert(e.message); return; }

  var names = getBundleNames(allRows);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var results = names.map(function(name) {
    var result = rollBundle(name, allRows, 0);
    logResult(ss, name, result);
    return name + ': ' + result;
  });
  ui.alert('Roll Results:\n\n' + results.join('\n'));
}

function clearHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var histSheet = ss.getSheetByName('Roll History');
  if (!histSheet) { SpreadsheetApp.getUi().alert('No Roll History sheet found.'); return; }
  var last = histSheet.getLastRow();
  if (last > 1) histSheet.deleteRows(2, last - 1);
  SpreadsheetApp.getUi().alert('Roll History cleared.');
}

Export / Import Format

Sheet	Columns
Items	Name, Emoji, Value, [param columns…]
Bundles	Bundle Name, Bundle Type, Bundle Emoji, Entry Name, Probability, Linked Item, Sub-bundle
Roll History	Time, Bundle, Result

When importing, existing data is replaced. Item parameter columns are dynamic; any column beyond Value is treated as a parameter key.

Roll History