Google Sheets – How to Sort Randomly Using Google Script

google sheetsgoogle-apps-script

I found this script to sort a Google Spreadsheet but I need to sort everything randomly instead of alphabetically, is there a way to sort it randomly every time this script runs?

// LinkBack to this script:  // http://webapps.stackexchange.com/questions/7211/how-can-i-make-some-data-on-a-google-spreadsheet-auto-sorting/43036#43036

/**  
 * Automatically sorts the 1st column (not the header row) Ascending. 
 */ 
function onEdit(event){
   var sheet = event.source.getActiveSheet();
   var editedCell = sheet.getActiveCell();

   var columnToSortBy = 1;   var tableRange = "A2:T99"; // What to sort.

  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange);
    range.sort( { column : columnToSortBy, ascending: true } );
   } 
}

Best Answer

Here is the function shuffleSheet which randomly shuffles the rows of the current sheet. The actual shuffling algorithm (which is classical) is taken from this answer by Laurens Holst.

I imagine this is something you will not want to do on every edit, but only when the script is explicitly invoked. 

function shuffleSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();
  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;
}
Related Topic