Navigating the columns and rows in pasting array values

Question

I'm trying to get the list of names from the Column A, randomize the list, and then distribute them evenly (as much as possible, including the remainders from division) by the user-specified number of groups.

An example of what I need is like this:

List of Names: A, B, C, D, E, F, G, H, I, J, K

Result of 3 Groups:

Group 1: D, A, F
Group 2: B, H, G, K
Group 3: E, C, I, J

Edited: I've cleaned up the code: I have assigned the list of names to an empty array and randomized the array successfully. Now I've got to figure out how to paste these values in their own columns for each groups. How do I paste the values to the right and down each column also accounting for the remainders (the first values are the headers for each columns):

Column C: Group 1, D, A, F
Column D: Group 2, B, H, G, K
Column E: Group 3, E, C, I, J

This is what I have so far:

function onOpen() {
SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
  .createMenu('Custom Menu')
  .addItem('Show prompt', 'showPrompt')
  .addToUi();
}

function SortNames() {
var ui = SpreadsheetApp.getUi();

var result = ui.prompt(
  'How many groups?',
  ui.ButtonSet.OK_CANCEL);

// Process the user's response.
var button = result.getSelectedButton();
var groupquantity = result.getResponseText();
if (button == ui.Button.OK) {
// User clicked "OK"  - Need to clear the cells from the previous sorting in this step

// Get the last row number of the names list
var Avals = SpreadsheetApp.getActiveSheet().getRange("A1:A").getValues();
var Alast = Avals.filter(String).length;

// Set an empty Array
var ar = [];

/****** In its original order, append the names to the array *****/
for (var i = 2; i < Alast+1; i++) {

  var source = 'A' + i;
  var Avals = SpreadsheetApp.getActiveSheet().getRange(source).getValues();
  ar.push(Avals);
}
/***************************/

/****** Shuffle the array    *****/
function shuffle(a) {
  var j, x, i;
  for (i = a.length; i; i--) {
    j = Math.floor(Math.random() * i);
    x = a[i - 1];
    a[i - 1] = a[j];
    a[j] = x;
  }
}

shuffle(ar);
/***************************/


/****** Calculates the rounded down # of members per group    *****/
var memberspergroup = ar.length / groupquantity;
var memberspergroup = Math.floor(memberspergroup);
/*********************************/



/****** Copy and Paste the rounded down number of members to each groups until 
the remainder is 0, then distribute evenly with remaining number of groups    *****/

// First Cell location to paste
var pasteloc = "C1";
for (var i = 1; i <= groupquantity; i++) {

  SpreadsheetApp.getActiveSheet().getRange(pasteloc).setValue('Group ' + i);
  var source = 'A' + i;
  var Avals = SpreadsheetApp.getActiveSheet().getRange(source).getValues();
}
/*********************************/



} 

else if (button == ui.Button.CANCEL) {
// User clicked "Cancel".
ui.alert('The request has been cancelled');
} 
else if (button == ui.Button.CLOSE) {
// User clicked X in the title bar.
ui.alert('You closed the dialog.');
}

}

Does [this](http://stackoverflow.com/questions/6274339/how-can-i-shuffle-an-array-in-javascript) help? — Robin Gertenbach, Oct 07 '16 at 09:42

score 0 · Accepted Answer · answered Oct 09 '16 at 09:34

I have found the solution to my question. It is not in the best shape - it can use an improvement to account for empty cells in the names list. However, it functions properly, and does everything I was looking for:

It assigns the list of names in an array
Randomizes the array

Distributes completely evenly which takes account for remainders (Just like the example I provided above)

function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
  .createMenu('Custom Menu')
  .addItem('Show prompt', 'showPrompt')
  .addToUi();
/******closing function onOpen()*********************/
}

function SortNames() {
  var ui = SpreadsheetApp.getUi();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  var result = ui.prompt(
    'How many groups?',
  ui.ButtonSet.OK_CANCEL);

  // Process the user's response.
  var button = result.getSelectedButton();
  var groupquantity = result.getResponseText();
  if (button == ui.Button.OK) {
  // User clicked "OK"

  // Get the last row number of the names list
  var Avalues = sheet.getRange("A1:A").getValues();
  var Alast = Avalues.filter(String).length;

    if(groupquantity > 0 && groupquantity <= Alast)
    {
    // User inputted a valid group quantity  - Need to clear the cells from the previous sorting in this step
   var lastRow = SpreadsheetApp.getActiveSheet().getMaxRows();
   sheet.getRange('C1:Z'+lastRow).clearContent();

   // Set an empty Array
   var ar = [];

   /****** In its original order, append the names to the array *****/
   for (var i = 2; i < Alast+1; i++) {

   var source = 'A' + i;
   var Avals = sheet.getRange(source).getValues();

   ar.push(Avals);

   }
   /***************************/

   /****** Shuffles array    *****/
   function shuffle(a) {
     var j, x, i;
     for (i = a.length; i; i--) {
       j = Math.floor(Math.random() * i);
       x = a[i - 1];
       a[i - 1] = a[j];
       a[j] = x;
     }
   /**********closing function shuffle(a)*****************/
   }

   shuffle(ar);

   /****** Calculates the rounded down # of members per group    *****/
   var memberspergroup = Math.floor(ar.length / groupquantity);
   /*********************************/  


   /****** Main Goal: Copy and Paste the rounded down number of members to each groups until 
   the remainder is 0, then distribute evenly with remaining number of groups    *****/
   // 1. Define the first Cell location to paste
   var pasteloc = "C1";

   // 2. Begin a for-loop: Navigate Horizontally across from the first cell location
   for (var i = 1; i <= groupquantity; i++)
     {

       // 3. Set the Column Headings in the current column, i
       sheet.getRange(1,i+2).setValue('Group ' + i);

       /************** 4. Fill in the Rows of names for each groups **********/
       // List out the values in array "ar" by the expected group qty, until the remainder is zero

       if ((ar.length)%(groupquantity-(i-1)) > 0)
       {
         for (var rows = 2; rows <= memberspergroup+1; rows++)
         {
           var j = 0;
           sheet.getRange(rows,i+2).setValue(ar[j]);
           var index = ar.indexOf(ar[j]);
            ar.splice(index, 1);
         }

        }
        else
        {
          var memberspergroup = ar.length/(groupquantity-(i-1))
          for (var rows = 2; rows <= memberspergroup+1; rows++)
          {
           var j = 0;
           sheet.getRange(rows,i+2).setValue(ar[j]);
           var index = ar.indexOf(ar[j]);
           ar.splice(index, 1);
          }
        }

     }
   /*********************************/

}
   /*****************closing if(groupquantity > 0 && groupquantity <= Alast)****************/

else{
 ui.alert("Error: " + '"' + groupquantity + '"' +" is not a proper group quantity")
}




   }

  else if (button == ui.Button.CANCEL) {
  // User clicked "Cancel".
   }
else if (button == ui.Button.CLOSE) {
// User clicked X in the title bar.
 }


}

I hope this helps!

Navigating the columns and rows in pasting array values

1 Answers1