Google sheets add-on for heat assignment

[biscuit]

Google sheets add-on for heat assignment (V 1.0)

Heat assignment ad-on V 1.0.1

Background of the ad-on
I hosted my first comp a couple on 11/7/15, and I needed to transfer heat information to a spread sheet. Instead of sucking it up and manually copying all the data into the spread sheet, I decided I'd make a script to help with the process. This way I could type in the event and heat once, and then just copy and paste the names in, and Viola! It enters it in. I made a script that kind of works, but wasn't all that happy with it. I now have a working version

Hopefully you guys find it helpful! If you have any suggestions/bugs to report/other feed back, please let me know!


How to use

1. Import the spread sheet from the Cubing USA work book, and add a sheet called "add". See linked spreadsheet for an example of how to set it up. Click here

NOTE: The first rows of each column MUST be the name of the event. In the current build, your name's column must be the first column.

2. Under the Tools tab of your spread sheet, click the Script Editor option. It will bring up a script.google.com. If this is your first time on the site, it will display a welcome page. Find the button that will take you to the editor, and paste in the code in the spoiler at the bottom, and save it.

3. Return to the spread sheet, and reload the page. If step two was successful, a new option should appear on the task bar called, "Heat Manager".

4. Switch to "add" sheet (found at the bottom left). If you did not already set it up, copy and paste the template from the linked spreadsheet above (in step 1)

5. Under the Event header (I.E. A2 in the grid) and enter the event you would like to add heats for EXACTLY as you have it in the heats tab. I.E If you would like to add a new 3x3 round, you would type in, "3x3". You can type in any event you have created a column for. If you decide to host magic, even though it is no longer an official event, you would just need to add a column that is titled "Magic".

NOTE: 3x3 and Rubik's Cube are considered different events. It is also case and space sensitive. I.E. "rubik's Cube", "Rubik's Cube" and "Rubik's Cube" are all different events.

6. Under the # of heats header (I.E. B2 in the grid) enter the number of heats you want. Do the math, and make sure you have enough heats.

7. Under the # of competitors in a heat header (I.E. C2 in the grid) enter the number of competitors you want in each heat. (Update in the future to allow a incomplete heat if you can't divide the number of competitors evenly into heats)

8. Copy and paste all the names of competitors for that event (one per line) exactly as shown on the hats tab. (Update in the future to scan the heats tab to find names with the event selected.)

9. Click on the "Heat Manager" menu item, and then click "Add Heat" The spread sheet will then go through and make the desired changes. If you are adding a lot of people at once, this may take a few minutes.

This distributes competitors randomly, so you may want to make some changes, such as making sure you don't have all your staff members in a single heat etc.

CHANGE LOG
V 1.0.1

• Made number of heats unlimited, by changing the way the array with which number of competitors in each heat is stored.

Older versions

Spoiler

V 1.0.0

• Removed the dialog based system for adding new people in place of a mass entry system
• Added a system that stops the script if all competitors to be added have been added
• Added a system that stops the script if it reaches an empty cell

SUPPORTED FEATURES

• Enter an event, heat, and names to automatically assign a participant to an event
• Supports any amount of events, including non-wca events.

PLANNED FEATURES (in order of importance)

• Scan heats tab for competitors who have the event selected, instead of entering a list of names
• Allow for an incomplete heat
• Truncation of extra spaces in event/heat/name entry/allowing non case-sensitive entry
• Add comments to code for easier readability
• Re-organization of the layout to make more sense.
• Search and order functionality (requires HTML sidebar)

Any feedback is welcomed, as well as new ideas to implement.

Spoiler

//runs on open. Adds custom menu
function onOpen() {
  ss = SpreadsheetApp.getActiveSpreadsheet();
    
  SpreadsheetApp.getUi()
      .createMenu('Heat Manager')
      .addItem('Add heat', 'addHeat')
      //.addItem('Apply configurations', 'config')
      .addToUi();

}

//Adds competitors from the list in "add" to dssignated heat
function addHeat (){
    ss = SpreadsheetApp.getActiveSpreadsheet();
  setSheet("add");
  
  var row = 4;
  var loopBool = true;
 
  var event = getValue("A2");
  var heat = getValue("B2");
  var names = [];
  var numCompetitor = getValue("C2");
  
  while (loopBool){
   
    if (getValue("D"+row) == ""){
     loopBool = false; 
    }else{
     names.push(getValue("D"+row));  
    }
    
    row++
  }
  
  setHeat(names, event, heat, numCompetitor);
}

// set the heat
function setHeat(name, event, heat, numC){
  setSheet("heats");
  ss = SpreadsheetApp.getActiveSpreadsheet();
  
  var sheet = ss.getSheetByName("heats");
  
  
  //finds the event in the heats page for further use
  for (i = 1; i < sheet.getMaxColumns(); i++){
    if (getValue(String.fromCharCode(64 + i)+"1") == event){
      var colmToChange = String.fromCharCode(64 + i);
      i = sheet.getMaxColumns() + 1;
    }   
  }

  
  var heatsUsed = [];
  //Go through each name, until it matches the first name in the 'name' array. Then set the heat number to a random heat, which is not full.
  for (i = 2; i < 1000; i++){
    if (getValue("A"+i) == name[0]){ 
      
      //Assigns the comptitor to a random unfilled heat.
      var loop = true;
      while(loop){
        var toSet = Math.floor(Math.random()*heat + 1);

        //If the assigned heat has not been entered into the heatsUsed array yet, add it.
        if (heatsUsed[toSet] == null) {
          heatsUsed[toSet] = 0;
        }
        
      //checks to see if the assigned heat already is full. If not, update the heat number for the competitor, and exit the while loop. Then run 
      if (heatsUsed[toSet] < numC){
        setValue(colmToChange+i, toSet);
        name.shift();
        heatsUsed[toSet]++;
        loop = false;
      }
     }
      
      
      
    }else if(getValue("A"+i) == ""){
      i = 1001;
    }
    
    
    
    if (name.length == 0){
      i = 1001;
    }
    
    
    
    
    
  }
}

//gets the value of a cell and pushes to an array
function getAndSetVal (position, array){
  var valToStore = getValue(position);
  array.push(valToStore);
}

function countInArray (array, toCount){
 
  c = 0;
  for (i = 0; i < array.length; i++){
    
    if(array[i] == toCount){
    c++;
    }
  }
  
  return c;
}
       
function getValue(position) {
  var targetCell = ss.setActiveSelection(position);
  if (targetCell.getValue() == null){
   return null; 
  }else{
  return targetCell.getValue();
  }
}

function setValue(position, value) {
  var targetCell = ss.setActiveSelection(position);
  targetCell.setValue(value);
}

function setSheet(sheetName){
  SpreadsheetApp.setActiveSheet(ss.getSheetByName(sheetName));
}
[/SPOILER]

 

[JustinTimeCuber]

just a comment, on the second to last line it says "sh eetName" but I think it is a bug with speedsolving.com, because a similar thing has happened to me before

 

[biscuit]

V 1.0 is now out! I'm really happy how this turned out. Check the OP for a description of what changed/an overview of what it is

 

[biscuit]

V 1.0.1

Changed a small issue with the way heats are added, to allow for an unlimited amount of heats, as well as updating the instructions to work better, and adding to the template spreadsheet to be more useful.

If you use it, please tell me how it works out, and any bugs you find. Thank you!