Google Sheets – How to Sort Cells Randomly Using Google Apps Script

google sheetsgoogle-apps-script

I need to sort cells randomly, but not only values but their formats too (for example cells with data validation).
This code can shuffle values but not formats.
Actually, the main question is how to swap cells.

function shuffleRange(range) {
  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;
}

Best Answer

You can use get* methods to get the attributes and formats for the cells you wish to shuffle, then reorder the cells all in a consistent random way, and then use the set* method to assign after reordering.

There are a lot of get* methods: various font attributes, data validation rules, text alignment, cell backgrounds, etc. In the example below I only work with font weights (like bold), font styles (like italic), and data validation rules. 

function shuffleRange(range) {
  var height = range.getHeight();
  var indexArray = new Array(height); 
  for (var i = 0; i < height; i++) {
    indexArray[i] = i; 
  }
  indexArray = shuffleArray(indexArray);
  //  indexArray holds numbers 0, 1, 2, 3 ... in random order

  range.setValues(range.getValues().map(reorder));
  range.setFontWeights(range.getFontWeights().map(reorder));
  range.setFontStyles(range.getFontStyles().map(reorder));
  range.setDataValidations(range.getDataValidations().map(reorder));
  //  add more such lines using get* and set* methods of Range object

  function reorder(x, i, arr) {
    return arr[indexArray[i]];
  }
}

An alternative is to use the copyTo method which can carry all the attributes at once, but then one would have to copy cell by cell, which is probably a lot slower when you have a large range.

Related Topic