1 // Updates back to the Google spreadsheet.
2 // There's basically zero error handling here, but OK, missing some updates is fine, really.
5 "type": "service_account",
6 "project_id": "solskogen-cubemap",
7 "private_key_id": "9eaf56bb4d6b688c3c73bd532fecdde943eea718",
8 "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQCPlcoZuj+tiaiw\nH5tjcZmCAFuCS3LhND+4WgA7BPvA3yrHvgm23T9aYVhhntA/uv2MSNjbZLj9Bk5z\nTfhSF6X6mr6JdtK05X5FXOiZdk8/36FT+aLANFqhyTD4WGXQVaHVjp1i6YNm9NvH\nUCt+R99VSteuvEyMQbqQtqTgTAisCmiO6bMssK90xKH9hwc5Zew/OUEaxa+ivgPR\nbgD3cTTQrPh0SKrZQFvdxx9ikGI/7rTZUazGU8r+VHBRCTMExCx1uQa2QzMd3REM\nngQCQ9TIkS0fNsE6NE8omrbbDJK1ob5tm4Jm6O7a7cN+yCel8sWoqW4fHdUb1z5n\n9IOUgHgFAgMBAAECggEAAWYJL2scghpvzbNf+JlbdO1a9tRvvIaK+cbvOBGqST7e\nqynVf2O0KqEh91MsMIq6O/Gl/fWns1fPBoc10zZOwcnugeb8LbcLZwlqtbtjo8wi\nV8sgn1kVfKDwjvT/LyuHgPI7mqbTxp7iGN36ZnnZLB9wkxjJKBe6YPznl8yROeoK\n4BdLaTWSv5w9mp1wnPG5RVsS5oAkoSFyDY0U7gAetsUjNf7bdlGtLvobw3kOpa7W\nm8WdN6jGbbyxmpe5Ql66/DhTBI4giNDDVvhf6fcRCOO+aAWNzZ5R6SgrdSYHbuBQ\npzGI7nBmBg3Nu1EYpm42wrUpoh6czy1Uf0F6VDmIkQKBgQDEh1Ado/QICWGQu6MB\nP0tX+APhN10x9Oq511Fd9SnTLZz2yzUN5Sshor+nevpes1Ljf8hS2FSV0fl0nRg3\nb7uMRt6EZNmzJJlPCQeBHNevVg5Z3kn3cpGE2cIr5JWB3r+EVd9wTOz7ihOVOboY\nt0yREiMeRuVmrwPi98hyoaNiHQKBgQC7CQzLGUHQDORBlQA0V6NFyamgrpkbSdML\nIZ3VThxbSxFGROC6W8At914F3XXTeP4f/kU1jjYOYhKpQy2RpOg6oLyCIx78sC/J\nkZS5eMeqv/hLSLt5eebAx0tVpDO++z/MWbbr/EpPMwQlSMMFlU2HqUK3XnAlSv9Q\njBxrs1sJCQKBgGy1GFi85vBHGCOx1rGK7EcllifOsws+GVRgyM47HT6FvYw5zQf5\nmokJeA/RE4qskI3skcdZiDgzJFQfzVRkxo4KaW08R7sy5GZ2bSM67Ac9h8SoE6v/\nQIUG2sPitdxXdQJjaau5sWBV+Q0TGGAxi/W23ZwSxTOuXWz/eG4IANL1AoGBAJdc\nmpLejMk/NZXxbGnvpn161yDnS5au5vEyMlYGUaJ8HK2+XhPS3rMUZm3erFUIrLfd\ngcr2nL6FFc8PQ5iDWUDhBc1XeONL/lBk1XRHz2Za1yit4rJLObg3ULstGIdtM1NA\nI23VDZoMkkVOHi2th0HLc+eLsLwtdnOMABAU5Q5pAoGBAKoZY3MflCEIj1S2hKQB\ncmz68DcwwXiwwuwE4zXoTWO95xApl7IP9ElNr1LFjYEhRp0VKyeZJ8UASKLN0nKF\ncD36qa71rd9VvKsNOiiKwbNy/E9WQ2B5rfovPbg2xSr8AQJxwZww2iv0zsP/Z+fG\nWYKJbvIPySmSrXhg9seBoSOL\n-----END PRIVATE KEY-----\n",
9 "client_email": "ultimate-nm-2018@solskogen-cubemap.iam.gserviceaccount.com",
10 "client_id": "102636658655884526659",
11 "auth_uri": "https://accounts.google.com/o/oauth2/auth",
12 "token_uri": "https://accounts.google.com/o/oauth2/token",
13 "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
14 "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/ultimate-nm-2018%40solskogen-cubemap.iam.gserviceaccount.com"
17 function post_data(url, contents, content_type, cb, auth) {
18 let req = new XMLHttpRequest();
19 req.onload = function(e) {
22 req.open('POST', url);
23 req.setRequestHeader("Content-type", content_type);
24 if (auth !== undefined) {
25 req.setRequestHeader("Authorization", "Bearer " + auth);
30 function post_json(url, json, cb, auth) {
31 post_data(url, JSON.stringify(json), "application/json;charset=UTF-8", cb, auth);
34 let current_oauth_access_token = null;
37 function update_oauth_key(cb) {
38 let now = Math.floor(new Date().getTime() / 1000);
40 "iss": jwt_key.client_email,
41 "scope": "https://www.googleapis.com/auth/spreadsheets",
42 "aud":"https://www.googleapis.com/oauth2/v4/token",
46 let sJWS = KJUR.jws.JWS.sign(null, {"alg": "RS256"}, jwt, jwt_key.private_key);
47 post_data('https://www.googleapis.com/oauth2/v4/token',
48 "grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Ajwt-bearer&assertion=" + sJWS,
49 "application/x-www-form-urlencoded",
51 current_oauth_access_token = JSON.parse(response)['access_token'];
52 console.log("Got new OAuth key.");
53 oauth_expire = now + 1800;
54 if (cb !== undefined) { cb(); }
58 function possibly_update_oauth_key(cb) {
59 let now = Math.floor(new Date().getTime() / 1000);
60 if (oauth_expire - now < 60) {
61 console.log("Getting new OAuth key...");
68 function publish_group_rank(response, group_name)
71 let cols = ultimateconfig['score_sheet_cols'][group_name];
73 let teams = parse_teams_from_spreadsheet(response);
74 let games = parse_games_from_spreadsheet(response, group_name, false);
75 apply_games_to_teams(games, teams);
77 // Write the points total to the unsorted columns.
78 for (let i = 0; i < teams.length; ++i) {
79 let row = ultimateconfig['point_total_start_row'] + i;
80 updates.push({ "range": cols[2] + row, "values": [ [ teams[i].pts ] ] });
84 teams = rank(games, teams, 1, tiebreakers);
86 // Write the ranking table, from scratch.
87 for (let i = 0; i < teams.length; ++i) {
88 let row = ultimateconfig['ranking_list_start_row'] + i;
89 updates.push({ "range": cols[0] + row, "values": [ [ teams[i].rank ] ] });
90 updates.push({ "range": cols[1] + row, "values": [ [ teams[i].mediumname ] ] });
91 updates.push({ "range": cols[2] + row, "values": [ [ teams[i].pts ] ] });
95 if (tiebreakers.length != 0) {
96 tb_str = tiebreakers.join("\n");
98 updates.push({ "range": cols[0] + ultimateconfig['ranking_list_explain_row'], "values": [ [ tb_str ] ]});
101 "valueInputOption": "USER_ENTERED",
104 possibly_update_oauth_key(function() {
105 post_json('https://sheets.googleapis.com/v4/spreadsheets/' + ultimateconfig['score_sheet_id'] + '/values:batchUpdate?key=' + ultimateconfig['api_key'], json, function(response) {}, current_oauth_access_token);
109 function montecarlo(responses) {
110 let pseudo_group_names = ['X', 'Y', 'Z'];
111 let real_group_names = ['A', 'B', 'C'];
112 let teams = [], games = [], teams_to_idx = [];
114 let third_groups = [];
115 let busted_thirds = false;
117 for (const response of responses) {
118 let teams_group = parse_teams_from_spreadsheet(response);
119 let games_group = parse_games_from_spreadsheet(response, 'irrelevant group name', true);
120 apply_games_to_teams(games_group, teams_group);
122 teams.push(teams_group);
123 games.push(games_group);
124 teams_to_idx.push(make_teams_to_idx(teams_group));
127 for (let simulation_idx = 0; simulation_idx < 100; ++simulation_idx) { // 100 seems to be enough.
129 for (let group_idx = 0; group_idx < responses.length; ++group_idx) {
130 // Fill in random results. We deliberately use a uniform [-13,+13]
131 // model here, since we are interested in the extremal results.
132 // Of course, not all real games go to 13, but the risk of that
133 // influencing the tiebreakers is very slim.
135 for (const game of games[group_idx]) {
136 games_copy.push(Object.assign({}, game));
139 for (const team of teams[group_idx]) {
140 teams_copy.push(Object.assign({}, team));
143 for (let i = 0; i < games_copy.length; ++i) {
144 let idx1 = teams_to_idx[group_idx][games_copy[i].name1];
145 let idx2 = teams_to_idx[group_idx][games_copy[i].name2];
146 if (idx1 === undefined || idx2 === undefined) continue;
147 if (games_copy[i].score1 === undefined || games_copy[i].score2 === undefined ||
148 isNaN(games_copy[i].score1) || isNaN(games_copy[i].score2) ||
149 games_copy[i].score1 == games_copy[i].score2) {
150 // These were skipped by apply_games_to_teams() above.
151 let score1 = 0, score2 = 0;
152 let r = Math.floor(Math.random() * 26);
156 teams_copy[idx1].pts += 2;
160 teams_copy[idx2].pts += 2;
162 games_copy[i].score1 = score1;
163 games_copy[i].score2 = score2;
164 ++teams_copy[idx1].nplayed;
165 ++teams_copy[idx2].nplayed;
166 teams_copy[idx1].goals += score1;
167 teams_copy[idx2].goals += score2;
168 teams_copy[idx1].gd += score1;
169 teams_copy[idx2].gd += score2;
170 teams_copy[idx1].gd -= score2;
171 teams_copy[idx2].gd -= score1;
177 // Now rank according to the simulation.
178 let tiebreakers = [];
179 teams_copy = rank(games_copy, teams_copy, 1, tiebreakers);
181 // See if we have conflicting information with other simulations.
182 if (simulation_idx == 0) {
183 for (let i = 0; i < teams[group_idx].length; ++i) {
184 let idx = teams_to_idx[group_idx][teams_copy[i].name];
185 teams[group_idx][idx].simulated_rank = teams_copy[i].rank;
188 for (let i = 0; i < teams[group_idx].length; ++i) {
189 let idx = teams_to_idx[group_idx][teams_copy[i].name];
190 if (teams[group_idx][idx].simulated_rank !== teams_copy[i].rank) {
191 teams[group_idx][idx].simulated_rank = null;
196 if (!busted_thirds) {
197 let any_third_found = false;
198 for (let i = 0; i < teams[group_idx].length; ++i) {
200 if (i == 2 || teams_copy[i].rank == 3) {
201 if (any_third_found) {
202 busted_thirds = true;
204 teams_copy[i].group_idx = group_idx;
205 thirds.push(teams_copy[i]);
206 any_third_found = true;
214 if (!busted_thirds) {
215 let tiebreakers = [];
216 let ranked = rank_thirds([], thirds, 1, tiebreakers);
217 if (simulation_idx == 0) {
218 third_groups = ranked;
220 for (let i = 0; i < responses.length; ++i) {
221 if (third_groups[i].group_idx !== ranked[i].group_idx) {
222 third_groups[i].group_idx = null;
229 let replacements = [];
230 for (let group_idx = 0; group_idx < responses.length; ++group_idx) {
231 if (third_groups[group_idx].group_idx !== null) {
232 replacements.push([ pseudo_group_names[group_idx], real_group_names[third_groups[group_idx].group_idx] ]);
236 for (let group_idx = 0; group_idx < responses.length; ++group_idx) {
237 for (let i = 0; i < teams[group_idx].length; ++i) {
238 if (teams[group_idx][i].simulated_rank !== null) {
239 replacements.push([ real_group_names[group_idx] + teams[group_idx][i].simulated_rank, teams[group_idx][i].shortname ]);
247 function names_for_team(team, expansions) {
248 if (expansions.hasOwnProperty(team)) {
249 return expansions[team];
251 let longteam = team.replace("W ", "Win. ").replace("L ", "Los. ");
252 return [ longteam, longteam, team ];
255 function expand_mediumname_if_single_team(team, expansions) {
256 if (expansions.hasOwnProperty(team)) {
257 return expansions[team][1];
262 function do_replacements(str, replacements) {
263 for (const r of replacements) {
264 str = str.replace(r[0], r[1]);
269 function fill_playoff(replacements, teams) {
270 let team_expansions = {};
271 for (const group of teams) {
272 for (const team of group) {
273 team_expansions[team.name] = team_expansions[team.mediumname] = team_expansions[team.shortname] =
274 [ team.name, team.mediumname, team.shortname ];
278 let games = ultimateconfig['playoff_games'];
279 get_results('Results', function(response) {
282 for (const game of games) {
283 let team1 = do_replacements(game[0], replacements);
284 let team2 = do_replacements(game[1], replacements);
285 let team1_mediumname = expand_mediumname_if_single_team(team1, team_expansions);
286 let team2_mediumname = expand_mediumname_if_single_team(team2, team_expansions);
287 let row = ultimateconfig['playoff_games_start_row'] + game[3];
288 let cols = ultimateconfig['playoff_games_cols'][game[2]];
289 let cell_team1 = "Results!" + String.fromCharCode(cols[0] + 65) + row;
290 let cell_score1 = "Results!" + String.fromCharCode(cols[1] + 65) + row;
291 let cell_score2 = "Results!" + String.fromCharCode(cols[2] + 65) + row;
292 let cell_team2 = "Results!" + String.fromCharCode(cols[3] + 65) + row;
293 updates.push({ "range": cell_team1, "values": [ [ team1_mediumname ] ] });
294 updates.push({ "range": cell_team2, "values": [ [ team2_mediumname ] ] });
296 let score1 = response['values'][row - 1][cols[1]];
297 let score2 = response['values'][row - 1][cols[2]];
298 let game_name = game[4];
299 let game_name2 = game_name.replace("Semi", "semi");
301 if (parseInt(score1) >= 0 && parseInt(score2) >= 0 && score1 != score2) {
302 if (parseInt(score1) > parseInt(score2)) {
303 replacements.unshift(["W " + game_name, team1]);
304 replacements.unshift(["L " + game_name, team2]);
305 replacements.unshift(["W " + game_name2, team1]);
306 replacements.unshift(["L " + game_name2, team2]);
308 replacements.unshift(["W " + game_name, team2]);
309 replacements.unshift(["L " + game_name, team1]);
310 replacements.unshift(["W " + game_name2, team2]);
311 replacements.unshift(["L " + game_name2, team1]);
313 } else if (game[5]) {
314 score1 = score2 = "";
315 updates.push({ "range": cell_score1, "values": [ [ game[4] ] ] });
318 if (game[2] == 0) { // Stream field.
321 "range": "Playoffs!A" + (game_num + 32) + ":J" + (game_num + 32),
322 "values": [ [ team1, team2, score1, score2, "", "", "", 7, response['values'][row - 1][1].replace(".",":"), game[6] ] ]
327 "range": "Playoffs!A" + (2 * game_num + 3) + ":C" + (2 * game_num + 3),
328 "values": [ names_for_team(team1, team_expansions) ]
331 "range": "Playoffs!A" + (2 * game_num + 4) + ":C" + (2 * game_num + 4),
332 "values": [ names_for_team(team2, team_expansions) ]
339 "valueInputOption": "USER_ENTERED",
342 possibly_update_oauth_key(function() {
343 post_json('https://sheets.googleapis.com/v4/spreadsheets/' + ultimateconfig['score_sheet_id'] + '/values:batchUpdate?key=' + ultimateconfig['api_key'], json, function(response) {}, current_oauth_access_token);
348 function get_results(sheet_name, cb)
350 let req = new XMLHttpRequest();
351 req.onload = function(e) {
352 cb(JSON.parse(req.responseText), sheet_name);
354 req.open('GET', 'https://sheets.googleapis.com/v4/spreadsheets/' + ultimateconfig['score_sheet_id'] + '/values/\'' + sheet_name + '\'!A1:Q50?key=' + ultimateconfig['api_key']);
358 function publish_group_ranks() {
359 get_group('Group A', function(response_a) {
360 get_group('Group B', function(response_b) {
361 get_group('Group C', function(response_c) {
362 publish_group_rank(response_a, 'Group A');
363 publish_group_rank(response_b, 'Group B');
364 publish_group_rank(response_c, 'Group C');
366 let replacements = montecarlo([response_a, response_b, response_c]);
367 let team_a = parse_teams_from_spreadsheet(response_a);
368 let team_b = parse_teams_from_spreadsheet(response_b);
369 let team_c = parse_teams_from_spreadsheet(response_c);
370 fill_playoff(replacements, [team_a, team_b, team_c]);
376 function get_ranked(response, group_name) {
377 let teams = parse_teams_from_spreadsheet(response);
378 let games = parse_games_from_spreadsheet(response, group_name, false);
379 apply_games_to_teams(games, teams);
380 let tiebreakers = [];
381 teams = rank(games, teams, 1, tiebreakers);
385 // Pick out everything that is at rank N _or_ avoids rank N by lack of tiebreakers only.
386 function pick_out_rank(teams, rank, candidates) {
387 let lowest_rank = teams[rank - 1].rank;
390 for (const team of teams) {
391 if (team.rank >= lowest_rank && team.rank <= rank) {
396 if (count >= teams.length / 2) {
397 // We have no info yet, ignore this group.
401 for (const team of teams) {
402 if (team.rank >= lowest_rank && team.rank <= rank) {
403 candidates.push(team);
408 function publish_best_thirds() {
409 get_group('Group A', function(response_a) {
410 get_group('Group B', function(response_b) {
411 get_group('Group C', function(response_c) {
412 let A = get_ranked(response_a, 'Group A');
413 let B = get_ranked(response_b, 'Group B');
414 let C = get_ranked(response_c, 'Group C');
417 pick_out_rank(A, 3, candidates);
418 pick_out_rank(B, 3, candidates);
419 pick_out_rank(C, 3, candidates);
421 let tiebreakers = [];
423 if (candidates.length >= 2) {
424 let ranked = rank_thirds([], candidates, 1, tiebreakers);
425 text = "Best thirds: " + ranked[0].mediumname + ", " + ranked[1].mediumname + "\n" + tiebreakers.join("\n");
428 updates.push({ "range": ultimateconfig['explain_third_cell'], "values": [ [ text ] ] });
430 "valueInputOption": "USER_ENTERED",
433 possibly_update_oauth_key(function() {
434 post_json('https://sheets.googleapis.com/v4/spreadsheets/' + ultimateconfig['score_sheet_id'] + '/values:batchUpdate?key=' + ultimateconfig['api_key'], json, function(response) {}, current_oauth_access_token);
442 setTimeout(function() {
443 publish_group_ranks();
444 publish_best_thirds();
445 setInterval(function() { publish_group_ranks(); publish_best_thirds(); }, 60000);