-
Notifications
You must be signed in to change notification settings - Fork 33
/
Copy pathEveCentralPrices.gs
160 lines (140 loc) · 6.84 KB
/
EveCentralPrices.gs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
/*
Takes a bunch of typeids from a list (duplicates are fine. multidimensional is fine) and returns a bunch of rows
with relevant price data.
TypeID,Buy Volume,Buy average,Buy max,Buy min,Buy Std deviation,Buy median,Buy Percentile,
Sell Volume,Sell Average,Sell Max,Sell Min,Sell std Deviation,Sell Median,sell Percentile
I'd suggest loading price data into a new sheet, then using vlookup to get the bits you care about in your main sheet.
loadRegionPrices defaults to the Forge
loadSystemPrices defaults to Jita
=loadRegionPrices(A1:A28)
=loadRegionPrices(A1:A28,10000002)
=loadRegionPrices(A1:A28,10000002,47)
=loadSystemPrices(A1:A28)
An example below:
https://docs.google.com/spreadsheets/d/1f9-4cb4Tx64Do-xmHhELSwZGahZ2mTTkV7mKDBRPrrY/edit?usp=sharing
*/
/*
* Loads prices for a given set of typeIDs for a specific region using Eve-Central's data.
* @param priceIDs A range where the item typeIDs are found.
* @param regionID The region to query.
* @param {number} cachebuster Increment this variable to refresh the data.
* @return The price data in multiple columns in the following order: TypeID,Buy Volume,Buy average,Buy max,Buy min,Buy Std deviation,Buy median,Buy Percentile,Sell Volume,Sell Average,Sell Max,Sell Min,Sell std Deviation,Sell Median,sell Percentile. This is suitable for use with VLOOKUP.
* @customfunction
*/
function loadRegionPrices(priceIDs,regionID,cachebuster){
if (typeof regionID == 'undefined'){
regionID=10000002;
}
if (typeof priceIDs == 'undefined'){
throw 'need typeids';
}
if (typeof cachebuster == 'undefined'){
cachebuster=1;
}
var prices = new Array();
var dirtyTypeIds = new Array();
var cleanTypeIds = new Array();
var url="http://api.eve-central.com/api/marketstat?cachebuster="+cachebuster+"®ionlimit="+regionID+"&typeid=";
priceIDs.forEach (function (row) {
row.forEach ( function (cell) {
if (typeof(cell) === 'number' ) {
dirtyTypeIds.push(cell);
}
});
});
cleanTypeIds = dirtyTypeIds.filter(function(v,i,a) {
return a.indexOf(v)===i;
});
var parameters = {method : "get", payload : ""};
var o,j,temparray,chunk = 100;
for (o=0,j=cleanTypeIds.length; o < j; o+=chunk) {
temparray = cleanTypeIds.slice(o,o+chunk);
Utilities.sleep(100);
var xmlFeed = UrlFetchApp.fetch(url+temparray.join("&typeid="), parameters).getContentText();
var xml = XmlService.parse(xmlFeed);
if(xml) {
var rows=xml.getRootElement().getChild("marketstat").getChildren("type");
for(var i = 0; i < rows.length; i++) {
var price=[parseInt(rows[i].getAttribute("id").getValue()),
parseInt(rows[i].getChild("buy").getChild("volume").getValue()),
parseFloat(rows[i].getChild("buy").getChild("avg").getValue()),
parseFloat(rows[i].getChild("buy").getChild("max").getValue()),
parseFloat(rows[i].getChild("buy").getChild("min").getValue()),
parseFloat(rows[i].getChild("buy").getChild("stddev").getValue()),
parseFloat(rows[i].getChild("buy").getChild("median").getValue()),
parseFloat(rows[i].getChild("buy").getChild("percentile").getValue()),
parseInt(rows[i].getChild("sell").getChild("volume").getValue()),
parseFloat(rows[i].getChild("sell").getChild("avg").getValue()),
parseFloat(rows[i].getChild("sell").getChild("max").getValue()),
parseFloat(rows[i].getChild("sell").getChild("min").getValue()),
parseFloat(rows[i].getChild("sell").getChild("stddev").getValue()),
parseFloat(rows[i].getChild("sell").getChild("median").getValue()),
parseFloat(rows[i].getChild("sell").getChild("percentile").getValue())];
prices.push(price);
}
}
}
return prices;
}
/*
* Loads prices for a given set of typeIDs for a specific region using Eve-Central's data.
* @param priceIDs A range where the item typeIDs are found.
* @param systemID The system to query.
* @param {number} cachebuster Increment this variable to refresh the data.
* @return The price data in multiple columns in the following order: TypeID,Buy Volume,Buy average,Buy max,Buy min,Buy Std deviation,Buy median,Buy Percentile,Sell Volume,Sell Average,Sell Max,Sell Min,Sell std Deviation,Sell Median,sell Percentile. This is suitable for use with VLOOKUP.
* @customfunction
*/
function loadSystemPrices(priceIDs,systemID,cachebuster){
if (typeof systemID == 'undefined'){
systemID=30000142;
}
if (typeof priceIDs == 'undefined'){
throw 'need typeids';
}
if (typeof cachebuster == 'undefined'){
cachebuster=1;
}
var prices = new Array();
var dirtyTypeIds = new Array();
var cleanTypeIds = new Array();
var url="http://api.eve-central.com/api/marketstat?cachebuster="+cachebuster+"&usesystem="+systemID+"&typeid=";
priceIDs.forEach (function (row) {
row.forEach ( function (cell) {
if (typeof(cell) === 'number' ) {
dirtyTypeIds.push(cell);
}
});
});
cleanTypeIds = dirtyTypeIds.filter(function(v,i,a) {
return a.indexOf(v)===i;
});
var parameters = {method : "get", payload : ""};
var o,j,temparray,chunk = 100;
for (o=0,j=cleanTypeIds.length; o < j; o+=chunk) {
temparray = cleanTypeIds.slice(o,o+chunk);
var xmlFeed = UrlFetchApp.fetch(url+temparray.join("&typeid="), parameters).getContentText();
var xml = XmlService.parse(xmlFeed);
if(xml) {
var rows=xml.getRootElement().getChild("marketstat").getChildren("type");
for(var i = 0; i < rows.length; i++) {
var price=[parseInt(rows[i].getAttribute("id").getValue()),
parseInt(rows[i].getChild("buy").getChild("volume").getValue()),
parseFloat(rows[i].getChild("buy").getChild("avg").getValue()),
parseFloat(rows[i].getChild("buy").getChild("max").getValue()),
parseFloat(rows[i].getChild("buy").getChild("min").getValue()),
parseFloat(rows[i].getChild("buy").getChild("stddev").getValue()),
parseFloat(rows[i].getChild("buy").getChild("median").getValue()),
parseFloat(rows[i].getChild("buy").getChild("percentile").getValue()),
parseInt(rows[i].getChild("sell").getChild("volume").getValue()),
parseFloat(rows[i].getChild("sell").getChild("avg").getValue()),
parseFloat(rows[i].getChild("sell").getChild("max").getValue()),
parseFloat(rows[i].getChild("sell").getChild("min").getValue()),
parseFloat(rows[i].getChild("sell").getChild("stddev").getValue()),
parseFloat(rows[i].getChild("sell").getChild("median").getValue()),
parseFloat(rows[i].getChild("sell").getChild("percentile").getValue())];
prices.push(price);
}
}
}
return prices;
}