Google Apps Script – Random Range with 10% of Total Data

google sheetsgoogle-apps-script

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');
  range.setValues(shuffleArray(range.getValues()));    
}    

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

Best Answer

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));

  sheet
    .getRange('L3:N')
    .clearContent()
    .getSheet()
    .getRange(3, 12, sortIndexes.length, sortIndexes[0].length)
    .setValues(sortIndexes);
}

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

Related Topic