Tally Votes across multiple sheets of data, and weight the scores.

TallyVotes.gs

ACTIVE_SPREADSHEET = SpreadsheetApp.getActiveSpreadsheet();

/**
 * add a menu to manually refresh the leaderboard
 */
function onOpen() {
  var sheet = ACTIVE_SPREADSHEET;
  var entries = [{
    name: "Refresh",
    functionName: "refreshLastUpdate",
  }, ];
  sheet.addMenu("Refresh", entries);

  refreshLastUpdate();
}

// /**
//  * automatically refresh the leaderboard on edit
//  */
// function onEdit() {
//   refreshLastUpdate();
// }

/**
 * set the Last Updated field to the current time.
 */
function refreshLastUpdate() {
  const start = new Date().getTime();
  let last = 0;

  ACTIVE_SPREADSHEET.getSheetByName("LEADERBOARD")
    .getRange("B1")
    .setValue(new Date().toTimeString());
}

/**
 * Tally votes across multiple sheets and return a weighted total.
 * @param key the key or username to search for
 * @param search_rangeA1 range to search in (A1 notation)
 * @param result_rangeA1 range to pick results from (A1 notation)
 * @param datetime unused variable to recalculate results
 * @returns weighted total of votes over all sheets
 */
function TallyVotes(key, search_rangeA1, result_rangeA1, datetime) {
  // get all dataset sheets (with WEEK in the name)
  let sheets = ACTIVE_SPREADSHEET.getSheets().filter((sheet) =>
    sheet.getName().toString().startsWith("WEEK")
  );

  // get a number[][] of all the votes for the given key
  let all_votes = sheets
    .map((sheet) => {
      let votes;

      search_range = sheet.getRange(search_rangeA1);
      result_range = sheet.getRange(result_rangeA1);
      let textFinder = search_range.createTextFinder(key);

      let matches = textFinder.matchEntireCell(true).findAll();

      if (matches.length === 0) return;

      votes = matches.map((match) => {
        // get relative row and column of the match in the search range
        // we have to add 1 before the comparison, because these are indexed from 1
        let row = 1 + match.getRow() - search_range.getRow();
        let column = 1 + match.getColumn() - search_range.getColumn();

        return result_range.getCell(row, column).getValue();
      });

      return votes;
    })
    .filter(Boolean); // filter out skipped iterations (sheets without a match)

  // calculate the weighted total for the votes on each sheet
  // the formula for the weighting is m=1/n where n is the index of each submission, starting at 1
  let weighted_votes = all_votes.map((votes) =>
    votes
    .sort((a, b) => b - a)
    .reduce((acc, match, index) => acc + match / (index + 1), 0)
  );

  // calculate the weighted total for the votes across all sheets, using the same weighting
  let weighted_total = weighted_votes
    .sort((a, b) => b - a)
    .reduce((acc, match, index) => acc + match / (index + 1), 0);

  return weighted_total;
}

zTallyVotes (performance profiling).gs

ACTIVE_SPREADSHEET = SpreadsheetApp.getActiveSpreadsheet();

/**
 * add a menu to manually refresh the leaderboard
 */
function onOpen() {
  const start = new Date().getTime();
  let last = 0;

  let current = new Date().getTime() - start;
  Logger.log(`${current}ms: start of function (${current - last}ms)`);
  last = current;

  var sheet = ACTIVE_SPREADSHEET;
  var entries = [{
    name: "Refresh",
    functionName: "refreshLastUpdate",
  }, ];
  sheet.addMenu("Refresh", entries);

  current = new Date().getTime() - start;
  Logger.log(`${current}ms: added Refresh Menu (${current - last}ms)`);
  last = current;

  refreshLastUpdate();
}

// /**
//  * automatically refresh the leaderboard on edit
//  */
// function onEdit() {
//   const start = new Date().getTime();
//   let last = 0;

//   let current = new Date().getTime() - start;
//   Logger.log(`${current}ms: start of function (${current - last}ms)`);
//   last = current;

//   refreshLastUpdate();
// }

/**
 * set the Last Updated field to the current time.
 */
function refreshLastUpdate() {
  const start = new Date().getTime();
  let last = 0;

  let current = new Date().getTime() - start;
  Logger.log(`${current}ms: start of function (${current - last}ms)`);
  last = current;

  ACTIVE_SPREADSHEET.getSheetByName("LEADERBOARD")
    .getRange("B1")
    .setValue(new Date().toTimeString());

  current = new Date().getTime() - start;
  Logger.log(`${current}ms: updated Last Updated Cell (${current - last}ms)`);
  last = current;
}

/**
 * Tally votes across multiple sheets and return a weighted total.
 * @param key the key or username to search for
 * @param search_rangeA1 range to search in (A1 notation)
 * @param result_rangeA1 range to pick results from (A1 notation)
 * @param datetime unused variable to recalculate results
 * @returns weighted total of votes over all sheets
 */
function TallyVotes(key, search_rangeA1, result_rangeA1, datetime) {
  const start = new Date().getTime();
  let last = 0;

  let current = new Date().getTime() - start;
  Logger.log(`${current}ms: start of function (${current - last}ms)`);
  last = current;

  // get all dataset sheets (with WEEK in the name)
  let sheets = ACTIVE_SPREADSHEET.getSheets().filter((sheet) =>
    sheet.getName().toString().startsWith("WEEK")
  );

  current = new Date().getTime() - start;
  Logger.log(`${current}ms: array of data sheets (${current - last}ms)`);
  last = current;

  // get a number[][] of all the votes for the given key
  let all_votes = sheets
    .map((sheet) => {
      let votes;

      search_range = sheet.getRange(search_rangeA1);
      result_range = sheet.getRange(result_rangeA1);
      let textFinder = search_range.createTextFinder(key);

      current = new Date().getTime() - start;
      Logger.log(
        `${current}ms: created TextFinder on sheet "${sheet.getName()}" (${
          current - last
        }ms)`
      );
      last = current;

      let matches = textFinder.matchEntireCell(true).findAll();

      current = new Date().getTime() - start;
      Logger.log(
        `${current}ms: found all matches for key "${key}" on sheet "${sheet.getName()}" (${
          current - last
        }ms)`
      );
      last = current;

      if (matches.length === 0) return;

      votes = matches.map((match) => {
        // get relative row and column of the match in the search range
        // we have to add 1 before the comparison, because these are indexed from 1
        let row = 1 + match.getRow() - search_range.getRow();
        let column = 1 + match.getColumn() - search_range.getColumn();

        return result_range.getCell(row, column).getValue();
      });

      current = new Date().getTime() - start;
      Logger.log(
        `${current}ms: mapped all votes for key "${key}" on sheet "${sheet.getName()}" (${
          current - last
        }ms)`
      );
      last = current;

      return votes;
    })
    .filter(Boolean); // filter out skipped iterations (sheets without a match)

  current = new Date().getTime() - start;
  Logger.log(`${current}ms: all votes collected (${current - last}ms)`);
  last = current;

  // calculate the weighted total for the votes on each sheet
  // the formula for the weighting is m=1/n where n is the index of each submission, starting at 1
  let weighted_votes = all_votes.map((votes) =>
    votes
    .sort((a, b) => b - a)
    .reduce((acc, match, index) => acc + match / (index + 1), 0)
  );

  current = new Date().getTime() - start;
  Logger.log(
    `${current}ms: weighted all votes for "${key}" per sheet (${
      current - last
    }ms)`
  );
  last = current;

  // calculate the weighted total for the votes across all sheets, using the same weighting
  let weighted_total = weighted_votes
    .sort((a, b) => b - a)
    .reduce((acc, match, index) => acc + match / (index + 1), 0);

  current = new Date().getTime() - start;
  Logger.log(
    `${current}ms: weighted all votes for "${key}" across all sheets (${
      current - last
    }ms)`
  );
  last = current;

  return weighted_total;
}