1

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;
}
0

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.

1
function shuffleRange(range) {

    var blankCell = findBlackCell(range.getSheet());

    var cells = getCells(range);

    //random cells
    for (var i = 0; i < cells.length; i++) {
        var j = Math.floor(Math.random() * (cells.length - i)) + i;
        swapCells(cells[i], cells[j], blankCell);
    }

    blankCell.clear();
    blankCell.clearDataValidations()

    function swapCells(cell1, cell2, blankCell) {
        cell1.copyTo(blankCell);
        cell2.copyTo(cell1);
        blankCell.copyTo(cell2);
    }

    function getCells(range) {

        var cells = [];
        var columns = range.getNumColumns(),
            rows = range.getNumRows();

        for (var column = 1; column <= columns; column++) {
            for (var row = 1; row <= rows; row++) {
                cells.push(range.getCell(row, column));
            }
            return cells;
        }

        range.setValues(shuffleArray(range.getValues()));
    }

    function findBlackCell(sheet) {
        var range = sheet.getRange('1:999');
        var columns = range.getNumColumns(),
            rows = range.getNumRows();

        for (var column = 1; column <= columns; column++) {
            for (var row = 1; row <= rows; row++) {
                var cell = range.getCell(row, column);
                if (cell.isBlank()) {
                    return cell;
                }
            }
        }
        throw ("Error: no empty cell found");
    }
}
  • This is the alternative approach referred to at the end of my answer; the advantage is that copyTo method carries all the attributes at once; the disadvantage is that one needs 3*N copyTo calls, where N is the number of cells shuffled. With a large array, performance issues are likely. – user135384 May 27 '17 at 20:43

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.