3G7t0LtU

1// custom menu
2function onOpen() {
3  const ui = SpreadsheetApp.getUi();
4  ui.createMenu('Torn Menu')
5    .addItem('Refresh', 'displayE')
6    .addItem('Update Initial_auto', 'updateInitialAuto')
7    .addToUi();
8}
9
10var GYM = 'gymdexterity'; // one of 'gymdexterity', 'gymdefence', 'gymstrength', 'gymspeed'
11var SHEET = "Dexterity Aug '21"; // name of the sheet where results will be published
12var FACTION_GOAL = 7500000; // faction goal to reach next upgrade
13var IND_GOAL = 31*300; // individual goal set for the month
14var KEY ='YOUR_API_KEY';
15
16// helper function
17// calls Torn API
18function callApi(key) {
19  const url = 'https://api.torn.com/faction/?selections=contributors&stat=' + GYM;
20  const response = UrlFetchApp.fetch(url + '&key=' + key, {'muteHttpExceptions': true});
21  const json = response.getContentText();
22  let result = null;
23
24  try {
25    result = JSON.parse(json);
26  } catch (e) {
27    Logger.log(e);
28  }
29
30  return result;
31}
32
33// helper function
34// uses a sheet named "Initial" that contains initial contributions
35// (i.e. prior to the beginning of the month)
36function getDataById(id) {
37  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Initial");
38  const data = sheet.getDataRange().getValues();
39  for(let i = 0; i < data.length; i++) {
40    if (data[i][1] == id) { // [1] because column B
41      return [data[i][0], data[i][3]]; // columns A, D
42    }
43  }
44  return ['', ''];
45}
46
47// helper function
48// formats a number
49function formatNumber(num) {
50  return num.toString().replace(/(\d)(?=(\d{3})+(?!\d))/g, '$1,')
51}
52
53// main function - you want this to be run periodically, e.g. every 15 minutes
54// it gets members' contributions from the API and updates the sheet
55function displayE() {
56  const ss = SpreadsheetApp.getActiveSpreadsheet();
57  const sheet = ss.getSheetByName(SHEET);
58  sheet.activate();
59
60  const data = callApi(KEY);
61
62  if (!data || !data.contributors || !data.contributors[GYM]) {
63    Logger.log('API error, no data:');
64    Logger.log(data);
65    return;
66  }
67
68  if (sheet.getLastRow() > 1) {
69    const range = sheet.getRange(3, 1, sheet.getLastRow()-1, sheet.getLastColumn());
70    range.clear();
71  }
72
73  const gym = data.contributors[GYM];
74  let total = 0;
75  let output = [];
76
77  for (let id in gym) {
78    const item = gym[id];
79    const active = item.in_faction;
80    total += 1*item.contributed;
81    if (active > 0) {
82      const values = getDataById(id);
83      const contrib = item.contributed - values[1];
84      //sheet.appendRow([values[0], id, item.contributed, values[1], '', item.contributed - values[1]]);
85      output.push([values[0], id, item.contributed, values[1], '', contrib, `${Math.round(contrib / IND_GOAL * 100)}%`]);
86    }
87  }
88
89  const outputRange = sheet.getRange(3, 1, output.length, output[0].length);
90  outputRange.setValues(output);
91
92  // sort by contribution, descending
93  const sortRange = sheet.getRange('A3:G101');
94  sortRange.sort({column: 6, ascending: false});
95
96  // add goal
97  const goalrange = sheet.getRange('A1:D1');
98  const now = new Date();
99  goalrange.setValues([['Faction Goal:', `${formatNumber(total)} / ${formatNumber(FACTION_GOAL)}`, 'Last Updated:', `${now.toLocaleString('en-GB', { timeZone: 'UTC' })} TCT`]]);
100
101  // auto resize names and goal columns
102  sheet.autoResizeColumn(1);
103  sheet.autoResizeColumn(8);
104}
105
106// function to automatically get members' contributions before training month begins
107// uses a sheet called "Initial_auto" and appends data to the end of the sheet
108// can be used when you cannot collect initial contributions manually
109function updateInitialAuto() {
110  const ss = SpreadsheetApp.getActiveSpreadsheet();
111  const sheet = ss.getSheetByName('Initial_auto');
112  sheet.activate();
113
114  const data = callApi(KEY);
115
116  if (!data || !data.contributors || !data.contributors[GYM]) {
117    Logger.log('API error, no data:');
118    Logger.log(data);
119    return;
120  }
121
122  const now = new Date();
123  const gym = data.contributors[GYM];
124  let output = [];
125
126  for (const id in gym) {
127    const item = gym[id];
128    const values = getDataById(id);
129    output.push([values[0], id, item.in_faction, item.contributed]);
130  }
131  output.push([now.toUTCString(), '', '', '']);
132
133  const outputRange = sheet.getRange(sheet.getLastRow() + 1, 1, output.length, output[0].length);
134  outputRange.setValues(output);
135}
136