Custom Google Sheets functions to compile judges' inputs via Google Forms at a ballroom dance competition

Scrutineering.gs

// The following are custom Google Sheets functions to scrutinize judges' data for a ballroom competition.
// They are designed to work with Google Forms responses. See each function's description below for
// instructions on setting up the Google Forms question.
//
// More info on custom functions: https://developers.google.com/apps-script/guides/sheets/functions
// More info on the system for scoring ballroom competitions: https://en.wikipedia.org/wiki/Skating_system

/**
 * Counts the number of marks for each competitor in a qualifying (non-final) round of a single-dance event.
 *
 * Takes one or more strings as input.
 * Each string should be a comma-separated string of competitors.
 * Each competitor in the string receives one mark.
 *
 * This is used in rounds other than the final round.
 * It is up to the scrutineer to decide the minimum number of marks for a competitor to advance to the next round.
 *
 * To set up data collection with Google Forms:
 *  1. Create a question with checkboxes.
 *  2. Add each competitor as an option.
 *  3. Make the question required.
 *  4. In the lower-right of the question, click the three dots to open the menu.
 *  5. Check "Response validation."
 *  6. For the response validation, choose "Select exactly" and enter the number of competitors that judges are
 *     supposed to recall.
 *  7. If you have not already, link the form responses to Google Sheets.
 *  8. In Google Sheets, pass the column of responses from this question *without* the heading to this function.
 *
 * @param {string|Array<Array<string>>} input One or more comma-delimited competitor names
 * @return A table of competitors and their marks, sorted in descending order by the number of marks
 * @customfunction
 */
function SCRUTINEER_QUALIFYING(input) {
  var result = [];
  var counts = new Map();

  if (!Array.isArray(input)) {
    input = [[input]];
  }

  // Rule 1: In all rounds each Adjudicator must vote for the number of
  // couples as stipulated by the Chairman of Adjudicators.
  input.forEach(row => row.forEach(cell => cell && cell.split(",").forEach(competitor => {
    competitor = competitor.trim();
    counts.set(competitor, (counts.get(competitor) ?? 0) + 1);
  })));

  counts.forEach((count, competitor) => result.push([competitor, count]));

  result.sort((a, b) => b[1] - a[1]);
  result.unshift(["Competitor", "Marks"]);
  return result;
}

/**
 * Places 6 to 8 competitors in the final round of a single dance event.
 *
 * Takes a 2-D array of strings. The top row should have the names of the competitors.
 * Each row should have one adjudicator's rankings.
 * Each competitor gets one ranking from each adjudicator.
 *
 * To set up data collection with Google Forms:
 *  1. Create a question with a multiple choice grid.
 *  2. Add each competitor as a row.
 *  3. For the columns, enter the possible places (1st, 2nd, 3rd, etc.).
 *  4. In the lower-right of the question, enable "Require a response in each row."
 *  5. In the lower-right of the question, click the three dots to open the menu.
 *  6. Check "Limit to one response per column."
 *  7. If you have not already, link the form responses to Google Sheets.
 *  8. In Google Sheets, there will be one column per column in the question.
 *     Pass the columns *with* the headings to this function.
 *
 * @param {string|Array<Array<string>>} input The 2-D array of strings
 * @param {boolean} sort Whether to sort the result in ascending order by placement
 * @return A table of competitors and the scoring columns
 * @customfunction
 */
function SCRUTINEER_FINAL(input, sort) {
  var result = [];

  if (!Array.isArray(input)) {
    input = [[input]];
  }

  var inputHeader = input.shift();
  if (!inputHeader) {
    throw new Error("input is empty");
  }

  while (inputHeader.length && !inputHeader[inputHeader.length - 1]) {
    inputHeader.pop();
  }
  var numberOfPlaces = inputHeader.length;

  var allCompetitorIndices = [];
  (() => {
    for (var index = 0; index < numberOfPlaces; ++index) {
      allCompetitorIndices.push(index);
    }
  })();

  // Make the header row of the output.
  var resultHeader = ["Competitor", "1"];
  (() => {
    for (var place = 2; place <= numberOfPlaces; ++place) {
      resultHeader.push(`1-${place}`);
    }
  })();
  var placeColumn = resultHeader.length;
  resultHeader.push("Place");

  // Make the rest of the output and the `aggregates` array.
  var aggregates = [];
  inputHeader.forEach(competitor => {
    if (typeof competitor === "string" && competitor.substring(0, 7) === "Final [") {
      competitor = competitor.substring(7, competitor.length - 1);
    }

    var resultRow = [competitor];
    var aggregatesRow = [competitor];
    for (var place = 1; place <= numberOfPlaces; ++place) {
      resultRow.push(0);
      aggregatesRow.push(0);
    }
    resultRow.push("?");

    result.push(resultRow);
    aggregates.push(aggregatesRow);
  });

  // Populate the "Place Calculation" columns.
  // For each competitor, count the adjudicators who gave them 1st place.
  // Then, count the adjudicators who gave them 1st or 2nd place. Also, get the sum of the placements.
  // Then, count the adjudicators who gave them 1st, 2nd, or 3rd place. Also, get the sum of the placements.
  // And so on.
  var numberOfAdjudicators = 0;
  input.forEach((row, rowIndex) => {
    var placesToGive = new Set();
    (() => {
      for (var place = 1; place <= numberOfPlaces; ++place) {
        placesToGive.add(place);
      }
    })();

    var rowIsEmpty = true;
    row.forEach((cell, competitorIndex) => {
      if (!cell) {
        return;
      }
      rowIsEmpty = false;

      // Compute the place that this adjudicator gave this competitor.
      // `parseInt` will automatically ignore everything after the number,
      // so "6th Place" becomes 6.
      var place = parseInt(cell);
      if (isNaN(place)) {
        throw new Error(`Cannot parse integer from '${cell}' at row ${rowIndex} column ${competitorIndex}`);
      }

      // Rule 2: In the final round each Adjudicator must place all competing
      // couples in order of merit in each of the dances.

      // Rule 3: In the final round the Adjudicator must allocate 1st place to
      // the best couple, 2nd to the second couple, 3rd place to the third best
      // couple and so on for each dance.
      if (place < 1 || place > numberOfPlaces) {
        throw new Error(`Place '${place}' is out of valid range [1, ${numberOfPlaces}]`);
      }

      // Rule 4: An Adjudicator is required to place every couple in the final
      // in every dance. It is not permitted to “tie” any couples for any place in
      // the final of any dance.
      if (!placesToGive.has(place)) {
        throw new Error(`Adjudicator on row ${rowIndex} gave place ${place} twice`);
      }
      placesToGive.delete(place);

      // The calculation columns show how many times an adjudicator placed a
      // competitor at or above a placement. For example, the "1-3" column
      // shows the number of times that the competitor got 3rd place or better.
      //
      // We will now add 1 to each column where this competitor got this
      // placement or better. For example, if this competitor got 3rd place,
      // we will now add 1 to the "1-3", "1-4", "1-5", and "1-6" columns. If
      // the "1-7" and "1-8" columns, we add 1 to those columns, too.
      for (var column = place; column <= numberOfPlaces; ++column) {
        result[competitorIndex][column] += 1;
        aggregates[competitorIndex][column] += place;
      }
    });

    if (!rowIsEmpty) {
      ++numberOfAdjudicators;
      if (placesToGive.size) {
        throw new Error(`Adjudicator on row ${rowIndex} did not give all places`);
      }
    }
  });

  var overallPlaceToAward = 1;
  var majority = Math.ceil(numberOfAdjudicators / 2);

  /**
   * Applies rules 7 and 8 of the skating system to calculate placements for a single dance.
   * @param {number} column The index of the first "Place Calculation" column to consider
   * @param {Array<number>} competitorIndicesToConsider The indices of the competitors to consider
   */
  var calculateSingleDance = (column, competitorIndicesToConsider) => {
    /**
     * On the first call, awards first place to the given competitor.
     * On subsequent calls, awards the next place to the given competitor.
     * @param {number} competitorIndex The index of the competitor who should get the next award
     */
    var givePlaceToCompetitor = competitorIndex => {
      for (var index = column + 1; index <= numberOfPlaces; ++index) {
        result[competitorIndex][index] = "-----";
      }
      result[competitorIndex][placeColumn] = overallPlaceToAward++;
    };

    for (; column <= numberOfPlaces; ++column) (() => {
      // In this array, each element is an array of the indices of competitors
      // who got the same number of adjudicators who gave them `column`-th place
      // or better. For example, if `column === 2` and `competitorsWithMajority`
      // equals `[[2, 3], [1]]`, then the competitors at indices 2 and 3 got the
      // same number of 2nd-or-better placements, and the competitor at index 1
      // has fewer.
      var competitorsWithMajority = (() => {
        // In this map, the keys are a number, and the values are arrays of the
        // indices of the competitors where the key equals the number of
        // adjudicators who gave them `column`-th place or better. For example,
        // if `column === 2` and `map.get(3)` returns `[2, 3]`, the competitors
        // at indices 2 and 3 both got 2nd place or better.
        var map = new Map();
        var keys = [];
        competitorIndicesToConsider.forEach(competitorIndex => {
          // `count` is the number of adjudicators who gave this competitor
          // `column`-th place or better.
          var count = result[competitorIndex][column];
          if (count === "-----" || count < majority || result[competitorIndex][placeColumn] !== "?") {
            return;
          }

          if (!map.has(count)) {
            map.set(count, []);
            keys.push(count);
          }
          map.get(count).push(competitorIndex);
        });

        keys.sort();
        return keys.map(count => map.get(count));
      })();

      while (competitorsWithMajority.length) (() => {
        var competitorsWithGreatestMajority = competitorsWithMajority.pop();

        // Rule 5: The winner of a dance is the couple who is placed first by an
        // absolute majority of Adjudicators, second place is awarded to the
        // couple placed second or higher by an absolute majority, and the
        // remaining places are progressively allocated in a similar way.

        // Rule 6: If two or more couples have a majority for the same position,
        // the couple with the largest majority shall be allocated the position
        // under review, and the couple holding the next largest majority is
        // allocated the following position.

        if (competitorsWithGreatestMajority.length === 1) {
          givePlaceToCompetitor(competitorsWithGreatestMajority[0]);
          return;
        }

        // Rule 7: If two or more couples have an equal majority for the same position…
        //
        // (A) If such majorities are equal, then we add together the marks
        // given by those Adjudicators who form the majority and the
        // Couple with the lowest total (or aggregate) is allocated the
        // position under review. The next position is awarded to the
        // Couple with the next lowest aggregate.
        //
        // (B) If the aggregates of marks are also equal, then the next
        // lower place (or subsequent places if necessary) in respect of the
        // particular Couples concerned, must also be taken into account.

        var competitorsWithAggregate = (() => {
          // In this map, the keys are a number, and the values are arrays of the
          // indices of the competitors where the key equals the sum of the `column`-
          // or-better placements that the competitor received.
          var map = new Map();
          var keys = [];
          competitorsWithGreatestMajority.forEach(competitorIndex => {
            var aggregate = aggregates[competitorIndex][column];

            if (!map.has(aggregate)) {
              map.set(aggregate, []);
              keys.push(aggregate);
            }
            map.get(aggregate).push(competitorIndex);

            result[competitorIndex][column] = `${result[competitorIndex][column]} (${aggregate})`;
          });

          keys.sort((a, b) => b - a);
          return keys.map(aggregate => map.get(aggregate));
        })();

        while (competitorsWithAggregate.length) (() => {
          var competitorsWithGreatestAggregate = competitorsWithAggregate.pop();
          if (competitorsWithGreatestAggregate.length === 1) {
            givePlaceToCompetitor(competitorsWithGreatestAggregate[0]);
            return;
          }

          // These competitors have the same aggregate! Rule 7(B) now applies.
          // Continue the calculations in the subsequent columns *only* for these competitors.
          calculateSingleDance(column + 1, competitorsWithGreatestAggregate);
        })();

        // Rule 8: If no couple receives a majority for the position under review
        // then you must proceed by including the next place marks in the calculation. If
        // still no majority is found then we include the next place marks, and if
        // necessary, the next again until a majority vote is found.
      })();
    })();

    // When two or more Couples have identical concluding place markings then the
    // Couples have TIED for the positions under review. As all the couples involved are
    // considered equal in placing for the dance, they are allocated the MEAN of the
    // positions under review.
    competitorIndicesToConsider = competitorIndicesToConsider.filter(
      competitorIndex => result[competitorIndex][placeColumn] === "?"
    );
    if (competitorIndicesToConsider.length) (() => {
      var meanPlace = 0.0;
      for (var i = 0; i < competitorIndicesToConsider.length; ++i) {
        meanPlace += overallPlaceToAward++;
      }
      meanPlace /= competitorIndicesToConsider.length;

      competitorIndicesToConsider.forEach(competitorIndex => {
        result[competitorIndex][placeColumn] = meanPlace;
      });
    })();
  };
  calculateSingleDance(1, allCompetitorIndices);

  // Replace 0's with hyphens. You could do this with number formatting,
  // but doing it here allows the logic to use `numberOfPlaces`.
  result.forEach(row => {
    for (var index = 1; index <= numberOfPlaces; ++index) {
      if (row[index] === 0) {
        row[index] = "-";
      }
    }
  });

  if (sort) {
    result.sort((a, b) => a[placeColumn] - b[placeColumn]);
  }
  result.unshift(resultHeader);
  return result;
}