8grzfSZH

1function myFunction() {
2
3
4    var sh = SpreadsheetApp.getActiveSpreadsheet(); //Getting the spreadsheet which is active
5    var ss = sh.getSheetByName("Log"); //get the worksheet inside the spreadsheet
6    var sd = sh.getSheetByName("Key");
7
8    ss.sort(1, false);
9
10    var last_entry = ss.getRange("A2").getValue();
11
12    var api = sd.getRange('B1').getValue(); // get value of API key
13
14    var faction_id_ = sd.getRange('B3');
15    if (faction_id_.isBlank()) {
16        var call_url = 'https://api.torn.com/user/?selections=&key='+api;
17        var profile = JSON.parse(UrlFetchApp.fetch(call_url));
18        var faction_id = profile["faction"]["faction_id"];
19        faction_id_.setValue(faction_id);
20
21    } else {
22        var faction_id = faction_id_.getValue();
23    }
24
25
26    //API call
27    var url = "https://api.torn.com/faction/?selections=revives&key=" +  api;
28
29    var content = UrlFetchApp.fetch(url);
30
31
32
33
34
35
36    //Reading new data from API
37    var apidata = JSON.parse(content);
38
39
40
41    var place = {};
42    place["revives"] = {};
43
44
45    for (k in apidata["revives"]) {
46        if (apidata["revives"][k]["timestamp"] > last_entry) {
47            if (apidata["revives"][k]["reviver_faction"] == faction_id) {
48                place["revives"][k] = apidata["revives"][k];
49            }
50        }
51    }
52
53    var log = JSON.stringify(place);
54
55
56    var test = JSON.parse(log);
57
58
59
60
61
62
63
64
65    //Converting Timestamp in revive data to readable time
66    for (var i in test["revives"]) {
67        test.revives[i]["time"] = Utilities.formatDate(new Date(test.revives[i]["timestamp"]*1000), "GMT", "dd MMMM yyyy  hh:mm:ss a") + "  TCT";
68    }
69
70    var final_a = JSON.stringify(test);
71
72
73
74
75
76
77
78
79
80
81    var final = JSON.parse(final_a);
82
83    var array = []; //creating an empty array to count total number of revives
84
85    for (var m in final["revives"]) {
86        array.push(m);
87    }
88
89
90    var elem = array.length;
91    if (elem > 0) {
92
93        ss.insertRowsBefore(2, elem);
94
95        //Parsing JSON to write it in the spreadsheet
96        var dataSet = final.revives;
97        var rows = [];
98
99
100        for (var i in dataSet) {
101            var revives = dataSet[i];
102            rows.push([revives.timestamp, revives.reviver_id, revives.reviver_name, revives.reviver_faction, revives.reviver_factionname, revives.target_id, revives.target_name, revives.target_faction, revives.target_factionname, revives.time]);
103        }
104        var datarange = ss.getRange(2, 1, elem, 10);
105        datarange.setValues(rows) //writing data
106
107        ss.sort(1, false);
108
109    }
110
111
112
113
114
115
116
117    var timestam = new Date().getTime();
118    var uptime = Utilities.formatDate(new Date(timestam), "GMT", "dd MMMM yyyy  hh:mm:ss a") + "  TCT";
119
120    var upcell = sd.getRange("B2").setValue(uptime);
121
122}
123
124//adding button to Spreadsheet
125function onOpen(e) {
126    var menu = SpreadsheetApp.getUi().createMenu('Run Manually')
127
128    menu.addItem('Update Sheet', 'myFunction').addToUi();
129
130}