vendredi 7 octobre 2016

Google Sheet Script: Randomizing a list of names and distributing by a specified number of groups

I'm trying to get the list of names from the Column A, randomize the list, and then distribute them evenly by the user-specified number of groups.

A sample example of what I need is like this:

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

Result of 3 Groups:

Group1: D, A, F

Group2: B, H, G

Group3: E, C, I

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".
ui.alert( 'Generating ' + groupquantity + ' groups');

// Assign the Group names -- Have to figure this out later
SpreadsheetApp.getActiveSheet().getRange('C1').setValue('Group ' + groupquantity);

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


// Append the names in an array
for (var i = 2; i < Alast+1; i++) {

  var dest = 'C' + i;
  var source = 'A' + i;

  var Avals = SpreadsheetApp.getActiveSheet().getRange("A1:A").getValues();
  var namesarray = namesarray.push([Avals]);

}


} 

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.');
}


}

I've been stuck in the for loop trying to figure out how I will go about creating an array of names and then randomizing it before I can distribute them evenly. Is there a better way of going about this? I need someone to shine a light on this. I appreciate the help very much! Thank you!




Aucun commentaire:

Enregistrer un commentaire