r/sheets: Randomize a range of numbers in a column

/**
 * @fileoverview Randomiser Tool for Google Sheets.
 * @author u/IAmMoonie
 * @version 1.0
 * @see https://www.reddit.com/r/sheets/comments/1fhgtb3/randomize_a_range_of_numbers_in_a_column/
 */

/**
 * Configuration object for the Randomiser Tool. You can modify this as necessary.
 * @const {Object}
 * @property {string} menuName - The name of the custom menu in Google Sheets.
 * @property {number[]} defaultRange - The default range of numbers for randomisation, specified as [min, max].
 */
const config = {
  menuName: "Randomiser Tool",
  defaultRange: [0, 9]
};

/**
 * WARNING: Do not edit anything below this point unless you are familiar with Google Apps Script and the purpose of the code.
 */

/**
 * Creates the custom menu in Google Sheets UI when the document is opened.
 * @function
 */
const onOpen = () => {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu(config.menuName)
    .addItem("Randomise Cell Values", "showPrompt_")
    .addItem("Configuration", "openScriptPage_")
    .addToUi();
};

/**
 * Opens a modal dialog that redirects to the script configuration page.
 * @param {string} url - The URL of the script configuration page.
 * @function
 */
const openURL_ = (url) => {
  SpreadsheetApp.getUi().showModalDialog(
    HtmlService.createHtmlOutput(`<script>
        window.addEventListener('load', () => {
          window.open('${url}');
          google.script.host.close();
        });
      </script>`),
    "Opening Script Config..."
  );
};

/**
 * Opens the script configuration page in the script editor.
 * @function
 */
const openScriptPage_ = () => {
  try {
    const scriptId = ScriptApp.getScriptId();
    const scriptUrl = `https://script.google.com/d/${scriptId}/edit`;
    openURL_(scriptUrl);
  } catch (error) {
    console.error("Failed to open script page: ", error);
    SpreadsheetApp.getUi().alert("Error opening script configuration.");
  }
};

/**
 * Prompts the user to input cell ranges and configure randomisation settings.
 * @function
 */
const showPrompt_ = () => {
  const ui = SpreadsheetApp.getUi();
  const rangeResponse = ui.prompt(
    "Randomiser Tool",
    "Enter the range (e.g., A2:A11 or a list of comma-separated ranges like A2:A7, A9, A10:A11):",
    ui.ButtonSet.OK_CANCEL
  );
  if (rangeResponse.getSelectedButton() !== ui.Button.OK) return;
  const input = rangeResponse.getResponseText().trim();
  const useDefaultRangeResponse = ui.alert(
    "Randomiser Tool",
    `Use Default Random Number Range [${config.defaultRange[0]}-${config.defaultRange[1]}]?`,
    ui.ButtonSet.YES_NO
  );
  let min;
  let max;
  if (useDefaultRangeResponse === ui.Button.YES) {
    [min, max] = config.defaultRange;
  } else {
    min = getNumberInput_(ui, "Enter the minimum value:");
    max = getNumberInput_(ui, "Enter the maximum value:");
    if (min === null || max === null || min > max) {
      ui.alert("Invalid min/max values. Please try again.");
      return;
    }
  }
  try {
    randomiseCellValues_(parseRanges_(input), min, max);
    ui.alert("Randomisation Complete!");
  } catch (error) {
    console.error("Error randomising cell values: ", error);
    ui.alert("Error", `Invalid input: ${error.message}`);
  }
};

/**
 * Prompts the user to enter a number.
 * @param {GoogleAppsScript.Base.Ui} ui - The Google Sheets UI instance.
 * @param {string} message - The prompt message.
 * @returns {number|null} The user-input number or null if the prompt was cancelled.
 * @function
 */
const getNumberInput_ = (ui, message) => {
  const response = ui.prompt(
    "Randomiser Tool",
    message,
    ui.ButtonSet.OK_CANCEL
  );
  return response.getSelectedButton() === ui.Button.OK
    ? parseInt(response.getResponseText().trim(), 10)
    : null;
};

/**
 * Parses a comma-separated string of ranges into Google Sheets Range objects.
 * @param {string} input - The comma-separated string of cell ranges.
 * @returns {GoogleAppsScript.Spreadsheet.Range[]} An array of Google Sheets Range objects.
 * @throws {Error} If the input format is invalid or ranges cannot be parsed.
 * @function
 */
const parseRanges_ = (input) => {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  try {
    return input.split(",").map((r) => sheet.getRange(r.trim()));
  } catch (error) {
    console.error("Failed to parse ranges: ", error);
    throw new Error("Invalid range format.");
  }
};

/**
 * Randomises the values in the specified ranges within the given range of numbers.
 * @param {GoogleAppsScript.Spreadsheet.Range[]} ranges - An array of Google Sheets Range objects to randomise.
 * @param {number} min - The minimum value for randomisation.
 * @param {number} max - The maximum value for randomisation.
 * @function
 */
const randomiseCellValues_ = (ranges, min, max) => {
  ranges.forEach((range) => {
    const numRows = range.getNumRows();
    const numCols = range.getNumColumns();
    const values = Array.from(
      {
        length: numRows
      },
      () =>
        Array.from(
          {
            length: numCols
          },
          () => getRandomInt_(min, max)
        )
    );
    range.setValues(values);
  });
  SpreadsheetApp.flush();
};

/**
 * Generates a random integer between the specified minimum and maximum values, inclusive.
 * @param {number} min - The minimum value.
 * @param {number} max - The maximum value.
 * @returns {number} A random integer between min and max, inclusive.
 * @function
 */
const getRandomInt_ = (min, max) =>
  Math.floor(Math.random() * (max - min + 1)) + min;