m1A1zWgB

1// Version 0.2. (c) Lugburz.
2
3// custom menu
4function onOpen() {
5  var ui = SpreadsheetApp.getUi();
6  ui.createMenu('Torn Menu')
7  .addItem('Refresh', 'refresh')
8  .addToUi();
9}
10
11function callApi(key, id) {
12  var url = 'https://api.torn.com/market/' + id + '?selections=bazaar,itemmarket&key=';
13  var response = UrlFetchApp.fetch(url + key, {'muteHttpExceptions': true});
14  var json = response.getContentText();
15  var result = null;
16
17  try {
18    result = JSON.parse(json);
19  } catch (e) {
20    Logger.log(e);
21  }
22
23  return result;
24}
25
26function process(data, price, extra, isBazaar) {
27  let profit = 0;
28  let total_quantity = 0;
29  let av_price = 0;
30  let bazaars = 0;
31
32  for (let i = 0; i < data.length; i++) {
33    if (Number(data[i].cost) == Number(price)) // skip own bazaars
34      continue;
35    else if (Number(data[i].cost) >= Number(price) + Number(extra)) // cut-off price
36      break;
37
38    av_price = (Number(av_price) * Number(total_quantity) + Number(data[i].cost) * Number(data[i].quantity)) / (Number(total_quantity) + Number(data[i].quantity));
39    total_quantity = Number(total_quantity) + Number(data[i].quantity);
40    profit = Number(profit) + (Number(price) + Number(extra) - Number(data[i].cost)) * Number(data[i].quantity);
41    if (isBazaar) bazaars++;
42  }
43
44  return [total_quantity, av_price, profit, bazaars];
45}
46
47function pad(num, size) {
48    return ('000000000' + num).substr(-size);
49}
50
51function formatDateTime(date) {
52    return pad(date.getUTCHours(), 2) + ':' + pad(date.getUTCMinutes(), 2) + ':' + pad(date.getUTCSeconds(), 2) + ' - ' +
53      pad(date.getUTCDate(), 2) + '/' + pad(date.getUTCMonth()+1, 2) + '/' + date.getUTCFullYear();
54}
55
56function refresh() {
57  const ss = SpreadsheetApp.getActiveSpreadsheet();
58
59  const profitSheet = ss.getSheetByName('Profits');
60  const mostProfitsSheet = ss.getSheetByName('Most Profits');
61  const inputSheet = ss.getSheetByName('Input');
62  const historySheet = ss.getSheetByName('History');
63  const key = inputSheet.getRange(1, 2).getValue(); //B1
64
65  const startRow = 4; //A4
66  const ids = inputSheet.getRange(startRow, 1, inputSheet.getLastRow()-startRow+1).getValues(); //A
67  const names = inputSheet.getRange(startRow, 2, inputSheet.getLastRow()-startRow+1).getValues(); //B
68  const prices = inputSheet.getRange(startRow, 3, inputSheet.getLastRow()-startRow+1).getValues(); //C
69  const extras = inputSheet.getRange(startRow, 5, inputSheet.getLastRow()-startRow+1).getValues(); //E
70
71  profitSheet.getRange(3, 1, profitSheet.getLastRow() > 1 ? profitSheet.getLastRow()-1 : 1, profitSheet.getLastColumn()).clearContent();
72  for (let i = 0; i < ids.length; i++) {
73    const id = ids[i];
74    const name = names[i];
75    const price = prices[i];
76    const extra = extras[i];
77
78    const data = callApi(key, id);
79    if (data == null) {
80      Logger.log('id: ' + id + ': API error, no data');
81      continue;
82    }
83
84    const bazaarProfit = process(data.bazaar, price, extra, true);
85    const marketProfit = process(data.itemmarket, price, extra, false);
86    const total_quantity = Number(bazaarProfit[0]) + Number(marketProfit[0]);
87    const average_price = total_quantity > 0 ? (Number(bazaarProfit[0]) * Number(bazaarProfit[1]) + Number(marketProfit[0]) * Number(marketProfit[1])) / (total_quantity) : 0;
88    const total_profit = Number(bazaarProfit[2]) + Number(marketProfit[2]);
89    const bazaars = Number(bazaarProfit[3]);
90    Logger.log(name + ': ' + bazaarProfit[0] + ', ' + marketProfit[0] + ', ' + bazaarProfit[1] + ', ' + marketProfit[1] + ', ' + bazaarProfit[2] + ', ' + marketProfit[2] + ' ' + bazaarProfit[3]);
91
92    let quantityStr; 
93    if (bazaars > 1) quantityStr = total_quantity + ' (in ' + bazaars + ' bazaars)';
94    else if (bazaars > 0) quantityStr = total_quantity + ' (in ' + bazaars + ' bazaar)';
95    else quantityStr = total_quantity;
96    profitSheet.appendRow([name.toString(), quantityStr, total_profit]);
97
98    // record when profit from an item exceeds $1m
99    if (Number(total_profit) > 500000) {
100      const now = new Date();
101      historySheet.appendRow([now.toUTCString(), formatDateTime(now), name.toString(), total_profit])
102    }
103  }
104
105  // set format to plain text
106  profitSheet.getRange(3, 2, profitSheet.getLastRow() > 1 ? profitSheet.getLastRow()-1 : 1).setNumberFormat("@");
107
108  // yesterday
109  var d = new Date(Date.now());
110  d.setTime(d.getTime() - 24*60*60*1000);
111
112  // get most profits for the last day
113  let lastDayProfits = [];
114  const profits = historySheet.getRange(2, 1, historySheet.getLastRow() > 1 ? historySheet.getLastRow()-1 : 1, historySheet.getLastColumn()).getValues();
115  for (let i = profits.length-1; i > -1; i--) {
116    const _date = Date.parse(profits[i][0]);
117    if (_date < d.getTime())
118      break;
119    lastDayProfits.push(profits[i]);
120  }
121  Logger.log(profits.length + ' ' + lastDayProfits.length);
122
123  // add them to the sheet
124  mostProfitsSheet.getRange(2, 1, mostProfitsSheet.getLastRow() > 1 ? mostProfitsSheet.getLastRow()-1 : 1, mostProfitsSheet.getLastColumn()).clearContent();
125  for (let i = 0; i < lastDayProfits.length; i++) {
126    mostProfitsSheet.appendRow([lastDayProfits[i][1], lastDayProfits[i][2], lastDayProfits[i][3]]); // do not print col A
127  }
128  // sort by profit (col C)
129  let range = mostProfitsSheet.getRange(2, 1, mostProfitsSheet.getLastRow() > 1 ? mostProfitsSheet.getLastRow()-1 : 1, 3);
130  range.sort({column: 3, ascending: false});
131  // remove rows after the top three
132  mostProfitsSheet.getRange("A5:C").clearContent();
133}