I have a table with 20 rows of data. I want to randomize the data with 10% of the total data. The script I made is like this:

function shuffleSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange('A2:D21');

function shuffleArray(array) {
  var i, j, temp;
  for (i = array.length - 1; i > 0; i--) {
    j = Math.floor(Math.random() * (i + 1));
    temp = array[i];
    array[i] = array[j];
    array[j] = temp;
  return array;

After I run the script, the results are the data is randomized all the data. enter image description here

The result what i want, it can see in below : enter image description here

2 Answers 2


So you can use something like this

 * @param {GoogleAppsScript.Spreadsheet.Sheet}
function extractData_(sheet) {
  const percent = sheet.getRange('F6').getValue() || 0.1;
  const range = sheet.getRange('A3:C');
  const values = range.getValues();

  const totalData = values.filter(row => row[0] !== '');

  const sortIndexes = totalData
    .sort(() => 0.5 - Math.random())
    .slice(0, parseInt(totalData.length * percent));

    .getRange(3, 12, sortIndexes.length, sortIndexes[0].length)

There is the full sample https://docs.google.com/spreadsheets/d/1djnyrpZYDrIxfUS8khnGNbJp6KbsUYsBDIIqh1cyHfM/edit?usp=sharing

Snippet https://github.com/contributorpw/google-apps-script-snippets/tree/master/snippets/sheets/shuffle_some_data

enter image description here


Have you considered doing it without resorting to a script, and just using formulas?

For example something along the lines of: =array_constrain(sort(A2:A23,arrayFormula(randbetween(sign(row(A2:A23)),rows(A2:A23))),TRUE),round(rows(A2:A23)/10),1) Amending A2:A23 to your actual data etc


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.