Posted - 2011.07.29 04:17:00 - [1]

 

Ok a couple things:

Google Docs Spreadsheet - Jita Mineral Prices

1. This keeps on coming up so I figured I'd put up an *example* spreadsheet. By example I mean that it is available for people to *copy*. Please don't just bookmark the spreadsheet and then just re-use it. The reason why is that if eve-central.com gets hammered by 50,000 people all trying to refresh the same spreadsheet then that will just lock the whole thing up.

So please copy the spreadsheet and then go to town and do your own thing.

2. The primary function used is called "ImportXML()".

ImportXML() has two main requirements. One is the url for the XML data which in this case is "http://api.eve-central.com/api/marketstat" and the other is a way for Google to decode the XML data. The complete URL that I use is:

Quote:

---

http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=34&typeid=35&typeid=36&typeid=37&typeid=38&typeid=39&typeid=40&typeid=11399

---

to see the raw XML data: Linkage

Let's separate everything out into their components:

Quote:

---

http://api.eve-central.com/api/marketstat

---

This is the base URL

Quote:

---

?

---

This tells the website that everything that follows afterwards is a parameter or information that needs to be sent to the website.

Quote:

---

usesystem=30000142

---

This is CCP's unique ID number that identifies Jita solar system.

Quote:

---

&

---

This is a separator which tells the web server that there is a new parameter that needs to be separated out from the previous one.

Quote:

---

typeid=34

---

This is CCP's unique ID number that references Tritanium.

...

Ok now that is covered let's take a good look at the spreadsheet itself:

Column A: TypeId - this is the column that lists CCP's unique ID numbers for minerals
Column B: TypeName - This is the column that lists CCP's unique mineral names
Column C: Jita Median Price (buy & sell) - this is pretty self explanatory.
Column D: Jita Median Buy Price - same
Column E: Jita Median Sell Price - same

Now the workhorse is this:

Quote:

---

=ImportXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&JOIN("&typeid=",$A2:$A9), "/evec_api/marketstat/type/all/median")

---

The difficult part is this:

Quote:

---

"http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&JOIN("&typeid=",$A2:$A9)

---

The more difficult to understand part is probably this:

Quote:

---

&typeid="&JOIN("&typeid=",$A2:$A9)

---

"JOIN()" is a function in Google Docs spreadsheet that basically concatenates, or adds together, the content of a range of cells. In this case it is combining the TypeID numbers in the range "$A2:$A9" or Column A, cell 2 to cell 9. The "$" is a code that tells the spreadsheet to not change the Column to anything else if this gets copied.

The "&typeid=" part of the JOIN() function is what is called the delimiter. If you were to change it from "&typeid=" to say "&t=" then instead of seeing

Quote:

---

http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=34&typeid=35&typeid=36&typeid=37&typeid=38&typeid=39&typeid=40&typeid=11399

---

You would get instead

Quote:

---

http://api.eve-central.com/api/marketstat?usesystem=30000142&t=34&t=35&t=36&t=37&t=38&t=39&t=40&t=11399

---

The problem though is that Eve-Central.com doesn't like "&t=" and wants to see "&typeid=". Now instead of using the JOIN() function you could just manually write it all out. But it's easier to just use the JOIN() especially since it has been put in there already.