Generate A Random Number In Google Sheets

I was thinking about trying to transfer one of my most popular spreadsheet templates, Super Bowl Squares, from Excel to Google sheets. However, macros do not work in Google sheets. I was thinking about trying to recreate at least some of them, and the first problem to solve was how to make a random number generator script in Google Sheets.

Here's how you can create a Google Apps Script to generate random numbers between 0 and 9 in cells A1 to A9 without any repeats. 

Steps to Create the Google Apps Script:

1. Open your Google Sheet.
2. Click on Extensions in the menu.
3. Select Apps Script.
4. Delete any existing code in the script editor, and paste the following code:

function generateRandomNumbers() {
  // Create an array with numbers 0 to 9
  var numbers = Array.from({length: 10}, (_, i) => i);

  // Shuffle the array
  for (var i = numbers.length - 1; i > 0; i--) {
    var j = Math.floor(Math.random() * (i + 1));
    var temp = numbers[i];
    numbers[i] = numbers[j];
    numbers[j] = temp;
  }

  // Get the active spreadsheet and sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Place the shuffled numbers in cells A1 to A9
  for (var k = 0; k < 9; k++) {
    sheet.getRange(k + 1, 1).setValue(numbers[k]);
  }
}

5. Save the script by clicking the disk icon or pressing Ctrl + S. You can name it something like RandomNumberGenerator.
6. Close the Apps Script editor.
7. Back in your Google Sheet, go to Extensions -> Macros -> Import and then select your generateRandomNumbers function.

How to Run the Random Number Generator Script:

• To run the script, go to Extensions -> Macros -> generateRandomNumbers.
• The script will place random numbers between 0 and 9 in cells A1 to A9, with no repeats.

If this is your first time running the script, Google Sheets may ask you for permission to run the script. Approve the permissions to proceed. 

In my templates, I make macros easy to use by running them from a button click. Yes, you can run the script from a button in Google Sheets too! Here’s how you can set it up:

Step 1: Create the Script

1. Open your Google Sheet.
2. Click on Extensions in the menu.
3. Select Apps Script.
4. Delete any existing code in the script editor, and paste the script provided above.
5. Save the script by clicking the disk icon or pressing Ctrl + S.

Step 2: Add a Button to Google Sheets

1. Insert a Drawing (for the Button):

   • Go to your Google Sheet.
   • Click on Insert -> Drawing.
   • Click on the Text Box icon in the Drawing toolbar and draw a text box.
   • Type in a label for your button, such as "Generate Numbers."
   • Format the text and shape as you like.
   • Click Save and Close. The button will now appear on your sheet.

2. Assign the Script to the Button:

   • Click on the drawing (the button) you just created.
   • Click on the three vertical dots in the top right corner of the button, and select Assign script.
   • In the text box that appears, type the name of your script function, which is generateRandomNumbersInRow.
   • Click OK.

Step 3: Use the Button

• Now, whenever you click the button, the generateRandomNumbersInRow script will run, and random numbers will be placed in the cells G3 to P3.

This provides a user-friendly way to trigger the script without needing to go through the menu every time. The first problem is solved! Onto the next one...