Aizu Online Judgeから精進用スプレッドシートを作るGoogle Apps Script

gistfile1.js

function getAOJData(userid) {
  var response = UrlFetchApp.fetch("http://judge.u-aizu.ac.jp/onlinejudge/webservice/user?id=" + userid);
  if(response.getResponseCode() != 200)
    return false;
  
  var xml = response.getContentText();
  var doc = Xml.parse(xml);
  var data = doc.getElement();
  var res = {};
  
  var status = data.getElement('status');
  if(status == null)
    return false;
  res['count'] = status.getElement('solved').getText();
  
  res['problem'] = new Array();
  var problems = data.getElement('solved_list').getElements('problem');
  for(var i in problems) {
    var problem = problems[i];
    res['problem'].push(problem.getElement('id').getText());
  }
                   
  return res;
}

function updateAOJRanking() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  var surveySheet = spreadsheet.getSheetByName('アンケート');
  var rankingSheet = spreadsheet.getSheetByName('ランキング');
  var problemSheet = spreadsheet.getSheetByName('問題別')
  if(surveySheet == null || rankingSheet == null || problemSheet == null)
    return false;
  
  var survey = surveySheet.getDataRange();
  var ranking = rankingSheet.getDataRange();
  var problemList = problemSheet.getDataRange();
  var users = survey.getValues();
  var ranks = ranking.getValues();
  var problems = problemList.getValues();
  
  var problemDict = {};
  for(var i in users) {
    if(i == 0)
      continue;
    
    var user = users[i];
    var data = getAOJData(user[3]);
    if(data == false)
      continue;
    
    for(var j in data['problem']) {
      var id = data['problem'][j];
      
      if(problemDict[id] == undefined)
        problemDict[id] = new Array();
      problemDict[id].push(user[2]);
    }
    
    var found = false;
    for(var j in ranks) {
      var rank = ranks[j];
      
      if(rank[1] == user[2]) {
        var cells = ranking.offset(j, 0, 1);
        cells.setValues([[user[1], user[2], user[3], data['count']]]);
        
        found = true;
        break;
      }
    }
    if(!found)
      rankingSheet.appendRow([user[1], user[2], user[3], data['count']]);
    
    var found = false;
    for(var j in problems[0]) {
      if(problems[0][j] == user[2]) {
        found = true;
        break;
      }
    }
    if(!found) {
      problemSheet.insertColumns(problemSheet.getLastColumn() + 1);
      
      var cell = problemList.offset(0, problemSheet.getLastColumn(), 1, 1);
      cell.setValue(user[2]);
    }
    
    ranking = rankingSheet.getDataRange();
    problemList = problemSheet.getDataRange();
    ranks = ranking.getValues();
    problems = problemList.getValues();
  }
  
  for(var problemId in problemDict) {
    var rowIndex = null;
    
    for(var i in problems) {
      if(problems[i][0] == problemId) {
        rowIndex = i;
        break;
      }
    }
    if(rowIndex == null) {
      problemSheet.appendRow([[problemId]]);
      rowIndex = problemSheet.getLastRow() - 1;
    }
    
    for(var i in problemDict[problemId]) {
      var name = problemDict[problemId][i];
      
      for(var j in problems[0]) {
        if(problems[0][j] == name) {
          var cell = problemList.offset(rowIndex, j, 1, 1);
          cell.setValue('○');
          break;
        }
      }
    }
    
    problemList = problemSheet.getDataRange();
    problems = problemList.getValues();
  }
  
  rankingSheet.sort(4, false);
  problemSheet.sort(1);
}