git.sesse.net Git - ultimatescore/blob - update_sheets.js

   1 // Updates back to the Google spreadsheet.
   2 // There's basically zero error handling here, but OK, missing some updates is fine, really.
   3
   4 let jwt_key = {
   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"
  15 };
  16
  17 function post_data(url, contents, content_type, cb, auth) {
  18         let req = new XMLHttpRequest();
  19         req.onload = function(e) {
  20                 cb(req.responseText);
  21         };
  22         req.open('POST', url);
  23         req.setRequestHeader("Content-type", content_type);
  24         if (auth !== undefined) {
  25                 req.setRequestHeader("Authorization", "Bearer " + auth);
  26         }
  27         req.send(contents);
  28 }
  29
  30 function post_json(url, json, cb, auth) {
  31         post_data(url, JSON.stringify(json), "application/json;charset=UTF-8", cb, auth);
  32 }
  33
  34 let current_oauth_access_token = null;
  35 let oauth_expire = 0;
  36
  37 function update_oauth_key(cb) {
  38         let now = Math.floor(new Date().getTime() / 1000);
  39         let jwt = {
  40                 "iss": jwt_key.client_email,
  41                 "scope": "https://www.googleapis.com/auth/spreadsheets",
  42                 "aud":"https://www.googleapis.com/oauth2/v4/token",
  43                 "exp": now + 1800,
  44                 "iat": now,
  45         };
  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",
  50                 function(response) {
  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(); }
  55                 });
  56 }
  57
  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...");
  62                 update_oauth_key(cb);
  63         } else {
  64                 cb();
  65         }
  66 }
  67
  68 function publish_group_rank(group_name)
  69 {
  70         get_group(group_name, function(response, group_name) {
  71                 let updates = [];
  72                 let cols = ultimateconfig['score_sheet_cols'][group_name];
  73
  74                 let teams = parse_teams_from_spreadsheet(response);
  75                 let games = parse_games_from_spreadsheet(response, group_name, false);
  76                 apply_games_to_teams(games, teams);
  77
  78                 // Write the points total to the unsorted columns.
  79                 for (let i = 0; i < teams.length; ++i) {
  80                         let row = ultimateconfig['point_total_start_row'] + i;
  81                         updates.push({ "range": cols[2] + row, "values": [ [ teams[i].pts ] ] });
  82                 }
  83
  84                 let tiebreakers = [];
  85                 teams = rank(games, teams, 1, tiebreakers);
  86
  87                 // Write the ranking table, from scratch.
  88                 for (let i = 0; i < teams.length; ++i) {
  89                         let row = ultimateconfig['ranking_list_start_row'] + i;
  90                         updates.push({ "range": cols[0] + row, "values": [ [ teams[i].rank ] ] });
  91                         updates.push({ "range": cols[1] + row, "values": [ [ teams[i].mediumname ] ] });
  92                         updates.push({ "range": cols[2] + row, "values": [ [ teams[i].pts ] ] });
  93                 }
  94
  95                 let tb_str = "";
  96                 if (tiebreakers.length != 0) {
  97                         tb_str = tiebreakers.join("\n");
  98                 }
  99                 updates.push({ "range": cols[0] + ultimateconfig['ranking_list_explain_row'], "values": [ [ tb_str ] ]});
 100
 101                 let json = {
 102                         "valueInputOption": "USER_ENTERED",
 103                         "data": updates
 104                 };
 105                 possibly_update_oauth_key(function() {
 106                         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);
 107                 });
 108
 109         });
 110 }
 111
 112 function publish_group_ranks() {
 113         publish_group_rank('Group A');
 114         publish_group_rank('Group B');
 115 }
 116
 117 update_oauth_key();
 118 setTimeout(function() {
 119         publish_group_ranks();
 120         setInterval(function() { publish_group_ranks(); }, 60000);
 121 }, 5000);