Google Sheets Random Copy and Sort with Scripts-CodePudding

What I'm attempting to do is copy a column over and re-sort it. Problem is, it captures all available cells and uses the same space to re-sort, causing blank spaces. The idea is to create tournament match pairings, with the first column being the Roster itself, and following columns being players they will be matched against.

I'd also like to add a line that verifies a name doesn't appear twice on the same row, reshuffling until the column is all unique along each row

This is the code I have so far. I attempted to filter the data by swapping

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

for

  range2.setValues(shuffleArray(range.getValues().filter(String)));

but this results in a "Number of data rows is 10 when range is 41" error, not verbatim obviously. I'm trying to collapse the blank spaces that are shown in this Screenshot.

I'm sure I can figure out how to expand it by however many matches I wish to generate.

function shuffleRange() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('SETUP');
  var range = sheet.getRange('A31:A')
  var range2 = sheet.getRange('C31:C');
  range2.clearContents;
  range2.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;
}

CodePudding user response：

Modification points:

I think that range2.clearContents might be range2.clearContent().
In your script, by sheet.getRange('A31:A'), all rows in the sheet are retrieved.

When these points are reflected in your script, how about modifying shuffleRange() as follows?

Modified script:

function shuffleRange() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('SETUP');
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange('A31:A'   lastRow);
  var range2 = sheet.getRange('C31:C'   lastRow);
  range2.clearContent();
  var values = shuffleArray(range.getValues()).filter(String);
  range.offset(0, 2, values.length).setValues(values);
}

I'm not sure about the last row of your sheet. So, I proposed the above modification.