These forums have been archived and are now read-only.

The new forums are live and can be found at

EVE Technology Lab

  • Topic is locked indefinitely.

Eve-Central Import to Google Spreadsheets addon

Chest Nisu
#1 - 2017-05-28 09:21:48 UTC  |  Edited by: Chest Nisu
[Edit 2017-06-10]: The example spreadsheet and source code for the add-on are available at
I had trouble registering as a developer in the Google's system so I will just be sharing the project via Google Drive. Seems I have to use my credit card to verify my identity to Google but their system does not accept my card Straight
In case someone needs the add-on or just wants to see the source code: open the link, right click the shared document (while logged in to your Google account) and select "Make a copy". This creates an editable copy under your own account and you can then test the add-on, make changes to the spreadsheet and view/copy/edit the add-on source code. Code can be viewed by clicking the "Tools > Script editor" menu while the copied document is open. Add-on can be executed from the "Eve Market" menu which appears shortly after opening the copied document.

[Original message]:

I did the thing and decided to learn how to write Google Spreadsheet add-ons. It took about two days and now my first one is done. I wrote it as a replacement to the sometimes buggy IMPORTXML due to it often just showing the text "Loading..." or "N/A" infinitely. My add-on works quite differently though.

My current design is to scan through all sheets in the opened spreadsheet and find a magic string in cell A2. If it is found, the add-on starts reading input parameters from the sheet. B2 can contain the system id to use in the data query and there are a few other cells reserved for parameters as well. A5, A6 etc. should contain type ids to be used as parameters.

The add-on reads the parameters, queries the EVE-Central API for JSON data and then populates the sheet with the headers and the actual data. I decided to go with a POST request instead of GET due to the typeid limitation, likely caused by the request URL getting too long. I tested with 250 type ids per request and it seems to work just fine. I can fetch all sell/buy prices with one request per system which reduced requests in my own spreadsheet from 30 down to 1.

Now I am thinking that maybe I should share this add-on script or even try to publish it as an Google Apps Script so everyone could just install it from the spreadsheet menu.

  • What I currently like about this add-on:
  • Install it once and forget
    Only contains a very limited set of functionality. No extra features I would never use.
    No need for learning the IMPORTXML formula or similar
    No type id limit (or is just a very high limit)
    Imports all market data at once, doesn't use separate request for sell/buy prices, volumes etc.
    If import fails, does not overwrite old imported data
    Imports can be undoed with CTRL+Z (requires multiple undoes, depending on the affected sheet count)
    Can be manually executed from the add-ons menu
    On successful import updates the "last updated" cell with current time

  • What I don't like:
  • System, region and types must be given as ids. There is no clear source to get these from so users have to first crawl through the forums searching for links to community created lists.

Personally I would prefer to publish the add-on through Google Spreadsheets so it is immediately available to everyone. I wonder if it would be OK to list links to Fuzzwork Excel Spreadsheets in a separate EVE forums thread and then link back to the thread from the Google Spreadsheets add-ons page. This way users can somewhat easily find the ids and moderators can remove the links if requested or maybe users can update them should I not be paying attention.

I also wonder if there is an initiative anywhere to publish the Fuzzwork Google Spreadsheets scripts as add-ons. In such a case maybe a collaboration approach would work better.

Does anyone have any comments to share?
guigui lechat
the no fock given
#2 - 2017-05-28 11:24:37 UTC
you can find the ids of system/constel/regions here :

eg to find jita press ctrl f then type "jita:"
what you want is the locationId.

if you want jita region you search "jita:" then you see its region is TheForge, you serach "theforge:"

what I do : I need row 0 to get requests and parameters.
for tritanium, on row 3
A3 = name,
F3 = typeid (34)
H3 = "&typeid="&F3

I do this for every item (up to row 71 in my case)

H1 contains concat of ids : =CONCAT($H3:$H71)
D1 contains the result of evecentral : =WEBSERVICE(""&options.$B$7&$H$1)

then I retrieve the item data on each row :

D2 = buy/max
D3 =FILTERXML($D$1;"number(//type[@id="&$F3&"]/"&D$2&")")

E2 = sell/min
E3 =FILTERXML($D$1;"number(//type[@id="&$F3&"]/"&E$3&")")

cols B and C are where I compute buyback value and import through jf value (price = % of BO in jita + part of volume),
col G contains the volume of the item (required for import value through jf)

I have two sheets for importing prices, both load under one second because I actually only have two requests.

BTW it's a character limit in the url.
Chest Nisu
#3 - 2017-06-10 10:37:44 UTC
2017-06-10: Updated original post with project download link to Google Drive and instructions for use.