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:
- Open your Google Sheet.
- Click on
Extensionsin the menu. - Select
Apps Script. - 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]);
}
}
- Save the script by clicking the disk icon or pressing
Ctrl + S. You can name it something likeRandomNumberGenerator. - Close the Apps Script editor.
- Back in your Google Sheet, go to
Extensions->Macros->Importand then select yourgenerateRandomNumbersfunction.
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
- Open your Google Sheet.
- Click on
Extensionsin the menu. - Select
Apps Script. - Delete any existing code in the script editor, and paste the script provided above.
- Save the script by clicking the disk icon or pressing
Ctrl + S.
Step 2: Add a Button to Google Sheets
Insert a Drawing (for the Button):
- Go to your Google Sheet.
- Click on
Insert->Drawing. - Click on the
Text Boxicon 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.
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
generateRandomNumbersInRowscript 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...
