yNnPmwce

1//Setting our sheet vars
2sheetID = "1bfFAdT66eaTF0cW7H6X5wtT5oSS5e75Jhq1J0-p4pLA" //The string of random characters between /d/ and /edit#gid=... in your spreadsheet URL
3
4//This is our basic JSON query
5function JSON_request(url){
6  var response = UrlFetchApp.fetch(url);
7  var json = response.getContentText();
8  return JSON.parse(json);
9}
10
11//This concatenates a user id to our predetermined string, generating our final URL that we then send to JSON_request
12function getUserInfo(uid) {
13  var apiKey = "a8dc6b3bfdbf7a91d7610b190ec8b561dc6e73ee" //You must put your API Key here, or this sheet will not work!
14  var url = "https://osu.ppy.sh/api/get_user?k=" + apiKey + "&u=" + uid + "&m=0&type=id";
15  var data = JSON_request(url);
16  return data.pop();
17}
18
19//Making an API call for the player's rank using getUserInfo() and returning an error string if error
20function getRank(uid) {
21  if(uid != "" && uid != "-"){
22    user = getUserInfo(uid);
23    try{
24      var pp_rank = user.pp_rank;
25    }
26    catch(e){
27      return "";      
28    }
29    return pp_rank;
30  }
31  else{
32    return "";
33  }
34}
35
36//Making an API call for the player's rank using getUserInfo() and returning an error string if error
37function getPP(uid) {
38  if(uid != "" && uid != "-"){
39    user = getUserInfo(uid);
40    try{
41      var pp_raw = user.pp_raw;
42    }
43    catch(e){
44      return "";      
45    }
46    return pp_raw;
47  }
48  else{
49    return "";
50  }
51}
52
53//Making an api call for the player's nick using getUserInfo() and returning an error string if error
54function getNick(uid) {
55  if(uid != "" && uid != "-"){
56    user = getUserInfo(uid);
57    try{
58      var pp_rank = user.username;
59    }
60    catch(e){
61      return "#RESTRICTED";      
62    }
63    return pp_rank;
64  }
65  else{
66    return "";
67  }
68}
69
70//Making an api call for the player's nick using getUserInfo() and returning an error string if error
71function getCountry(uid) {
72  if(uid != "" && uid != "-"){
73    user = getUserInfo(uid);
74    try{
75      var country = user.country;
76    }
77    catch(e){
78      return "";      
79    }
80    return country;
81  }
82  else{
83    return "";
84  }
85}
86
87//Fancy UI 
88function onOpen(e) {
89  var menu = SpreadsheetApp.getUi()
90  menu.createMenu('ID List Update')
91  .addItem('Update', 'updateIDlist')
92  .addToUi();
93}
94
95// This assumes that the links are actually osu links...if they're other links then fuck you
96function updateIDlist(){
97  var doc = SpreadsheetApp.openById(sheetID);
98  var idlist = doc.getSheetByName('ID List');
99  idlist.getRange('C3:E').clear();
100  
101  var links = idlist.getRange('B3:B').getValues();
102  
103  var currentRow = 3; // cause we're starting from row 3...no shit duh
104  for(var i = 0; i < links.length; i++){
105    if (links[i][0].length > 0 && links[i][0].includes('osu.ppy.sh/users')){
106      var linksplit = links[i][0].split('/');
107      var id = linksplit[linksplit.length-1];
108      try{
109        idlist.getRange(currentRow, 3, 1, 3).setValues([[getNick(id),id,getRank(id)]])
110      }
111      catch(e){
112        idlist.getRange(currentRow, 3, 1, 1).setValues([["#ERROR_SN"]]);
113      }
114    }
115    currentRow = currentRow + 1;
116  }
117}