2

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

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;
}
  • Is there a way to do shuffle only in Row A? – Alan Uhh Yeah Jul 8 '15 at 15:16
  • Yes; change the line with var range to var range = sheet.getRange("A:A"); – user79865 Jul 8 '15 at 15:18
  • AWESOME! It worked perfectly!... Thank you so much – Alan Uhh Yeah Jul 8 '15 at 15:29
2

You could simply use the randomize() function for range. I'm using user79865's code as a basis:

function shuffleSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();
  range.randomize();    
}    

https://developers.google.com/apps-script/reference/spreadsheet/range#randomize()

  • This is indeed the simplest way. Would be nice to exclude the header row(s). – Gabriel R. May 16 at 14:12
1

Bookend's answer works perfectly. I had to call the function from outside of the spreadsheet though (which makes sense, given that the output changes the contents of the spreadsheet). Here's how to do that:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Extra Functions')
      .addItem('Shuffle Sheet', 'shuffleSheet')
      /*
      .addSeparator()
      .addSubMenu(ui.createMenu('Sub-menu')
          .addItem('Second item', 'menuItem2'))
      */
      .addToUi();
}

The next time you open your spreadsheet, go to 'Extra Functions' in the menu bar and select 'Shuffle Sheet'.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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