🎲 Dice Roller Script for Google Spreadsheets

dice_roller.js

function handleD20Roll(mode) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const cell = sheet.getActiveCell();
  const cellValue = cell.getValue();
  const cellStr = String(cellValue).toLowerCase().replace(/\s+/g, "");

  const ui = SpreadsheetApp.getUi();
  let total = 0;
  let formulaLog = "";
  let rolls = [];
  let valid = false;
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // If it's a number: treat as modifier for 1d20 roll
  if (!isNaN(Number(cellValue))) {
    valid = true;
    const modifier = Number(cellValue);
    const roll1 = Math.floor(Math.random() * 20) + 1;
    const roll2 = Math.floor(Math.random() * 20) + 1;
    rolls = [roll1, roll2];
    const chosen = (mode === "advantage") ? Math.max(roll1, roll2) : (mode === "disadvantage") ? Math.min(roll1, roll2) : roll1;
    total = chosen + modifier;
    const dices = (mode === "normal") ? `1d20 (${roll1})` : `2d20 (${roll1}, ${roll2})`;
    formulaLog = `${dices} → ${chosen} + ${modifier}`;
  }

  // If it's a dice formula like 1d6, 2d4+1
  else {
    const diceRegex = /^(\d*)d(\d+)(\+(\d+))?$/;
    const match = cellStr.match(diceRegex);
    if (match) {
      valid = true;
      let numDice = parseInt(match[1]) || 1;
      let diceSides = parseInt(match[2]);
      let modifier = parseInt(match[4]) || 0;

      // Advantage/disadvantage only applies to d20s
      if (numDice === 1 && diceSides === 20) {
        const roll1 = Math.floor(Math.random() * 20) + 1;
        const roll2 = Math.floor(Math.random() * 20) + 1;
        rolls = [roll1, roll2];
        const chosen = (mode === "advantage") ? Math.max(roll1, roll2) : Math.min(roll1, roll2);
        total = chosen + modifier;
        formulaLog = `2d20 (${roll1}, ${roll2}) → ${chosen} + ${modifier}`;
      } else {
        // Regular roll (no advantage/disadvantage)
        for (let i = 0; i < numDice; i++) {
          const roll = Math.floor(Math.random() * diceSides) + 1;
          rolls.push(roll);
          total += roll;
        }
        total += modifier;
        formulaLog = `${numDice}d${diceSides}${modifier ? "+" + modifier : ""} → [${rolls.join(", ")}]${modifier ? " + " + modifier : ""}`;
      }
    }
  }

  if (!valid) {
    ui.alert("Please select a cell with a valid modifier or dice formula (e.g., 2d6+3)");
    return;
  }

  ui.alert(`Result (${mode}): ${total}\nFormula: ${formulaLog}`);

  // Log the result
  const logSheetName = "Roll Log";
  let logSheet = ss.getSheetByName(logSheetName);

  if (!logSheet) {
    logSheet = ss.insertSheet(logSheetName);
    logSheet.appendRow(["Date", "Time", "User", "Formula", "Result"]);
  }

  const now = new Date();
  const dateStr = Utilities.formatDate(now, ss.getSpreadsheetTimeZone(), "yyyy-MM-dd");
  const timeStr = Utilities.formatDate(now, ss.getSpreadsheetTimeZone(), "HH:mm:ss");
  const user = Session.getActiveUser().getEmail() || "Unknown user";

  logSheet.appendRow([dateStr, timeStr, user, `[${mode}] ${formulaLog}`, total]);
}

function onCellClickDiceRoll() {
  handleD20Roll("normal");
}

function onCellClickWithAdvantage() {
  handleD20Roll("advantage");
}

function onCellClickWithDisadvantage() {
  handleD20Roll("disadvantage");
}

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("🎲 Dice Roller")
    .addItem("🔵 Normal Roll", "onCellClickDiceRoll")
    .addItem("🟢 Roll with Advantage", "onCellClickWithAdvantage")
    .addItem("🔴 Roll with Disadvantage", "onCellClickWithDisadvantage")
    .addToUi();
}

This is the script used by my Shadowdark RPG group in our Spreadsheets.

This script adds this menu:

With that you can select any cell in your spreadsheet and roll a dice:

• Cells with only numbers will be roll a d20 + the selected number;
• Cells with 1d8 will roll a d8;
• You can also rolls a little bit more complex formulas as 2d12+2.

All rolls will be saved in the Roll Log sheet.