How to Randomize the Order of Rows in Google Sheets

How to Randomize the Order of Rows in Google Sheets

by

Hey, friends today I will teach you How to Randomize the Order of Rows in Google Sheets. so let get started with today Code snippets. Getting different problems is altogether gives a very different experience. today the Code snippets I am going to share with you is How to Randomize the Order of Rows in Google Sheets. Learn how to sort your data in a Google Sheet in randomized order using Excel formulas and Google Apps Script.

You have a workbook in Google Sheets with multiple rows of data, and you must sort the list in random order. For example, your sheet may contain the names of your team members, and you may need to reshuffle the list before randomly assigning tasks to each member. Alternatively, your Google Sheet may contain the email addresses of people who participated in a giveaway, and you must select any three random entries for the prize in an unbiased manner.

You might also like our trending code snippets

In Google Sheet, you can randomise the data rows in a variety of ways. You can either use Google Sheets’ built-in SORT function or create a menu-based function that allows you to randomise data with a single click.

Demo – Make a copy of this Google Sheet to try random sort with your own data in sheets.

Sort Google Sheets in Random Order

125956

Open your Google Sheet that contains the list of data and create a new sheet. Paste the following formula in A1 cell of this empty sheet.

=SORT(Customers!A2:D50, RANDARRAY(ROWS(Customers!A2:A50), 1), FALSE)

The SORT function’s first argument specifies the range of data to be sorted in A1 Notation, the second argument creates a virtual column of the same dimension but filled with random numbers, and the third argument specifies the sort order from smallest to largest.

You may also want to replace Customers with the exact name of your Google Sheet in the formula. If your sheet name contains spaces, enclose it in single quotes, such as ‘Employee List’! A2:D50. Because the first row is assumed to contain the header, we begin with row 2. (titles).

The benefit of this method is that it does not change the source of data because the randomised list of data appears in a new sheet.

Sort a List Randomly in Google Sheets with Apps Script

If you prefer a more automated approach that doesn’t require you to manually add formulas each time you need to perform a random sort, take the Apps Script route.

Sort Google Sheets Randomly

Open your Google Sheet, go to the Tools menu and choose Script editor. Copy-paste the following code in the editor and save. Reload the Google Sheet and you should see a new menu as shown in the screenshot above.

/** @OnlyCurrentDoc */

// Sort data in random order
const sortRowsInRandomOrder = () => {
  // Get the current sheet that contains the list of data
  const sheet = SpreadsheetApp.getActiveSheet();

  // Get the first non-empty column
  const column = sheet.getLastColumn() + 1;

  // Add the RAND() formula to all rows in the new column
  sheet
    .getRange(1, column)
    .setFormula("=RAND()")
    .autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

  // Sort the entire range of data using the random values
  // Do not include the first row of data (header) for sort
  sheet.getDataRange().offset(1, 0).sort({ column });

  // Remove the temporary column from Google sheet
  sheet.deleteColumn(column);

  // Flush the changes
  SpreadsheetApp.flush();
};

// Add the menu to Google Sheets
const onOpen = () => {
  SpreadsheetApp.getUi()
    .createMenu("Randomize Rows")
    .addItem("Start", "sortRowsInRandomOrder")
    .addToUi();
};

Keep Shuffling Rows

Select Start from the Randomize Rows menu. It creates a temporary column, fills the RAND() formula in the new column for the entire range of cells, sorts the sheet range based on this data, and then automatically removes the temporary column.

You can click the same menu item multiple times and it will keep shuffling the rows in random order.

Leave a Comment