Silver’s Gw2spidy API Script v1.3

2015-03-13

There will be a bit of a ramble followed by the setup instructions, I don’t blame you if you skip…

As some people may be aware I have a fairly comprehensive Gw2 Spreadsheet (here) which I based the scripting (and the original idea) off EggBaron’s sheet (here). I originally created my sheet because there there was missing sections (Common promotions) and the amount of images kept crashing the page for me, not only that but there had been no further development on the tool, EggBaron last updated it over two years ago (he is back now tough).

When I started working on the sheet I had to wade in and learn what each part of the script does and I have improved it since then. The original script pulled the entire category of Crafting Materials (1149 items) for 157 items. The first modification I made was for it to load up multiple categories, this was just as inefficient but it allowed me to load stuff like Upgrade Components. The second version was a way to have multiple categories easier as well as adding in gems to the sheet/script. Now I have it setup to pull only the data that you need, this also ensures that after it is setup that it is almost foolproof to use.

Time for the instructions! To setup:

Go to the Template sheet (link).
“File” => “Make a Copy”.
Open up your new sheet (you can close the template).
“Tools” => “Script editor”.
“Resources” => “Current project’s triggers”.
“No triggers set up. Click here to add one now.” => (Default option is “refresh”) Change the timing to suit (eg. 30min or 1hr) => “Save”.
Repeat 6 only this time, select the “clearLog” script and set it to run once a week or so (a month max).
Below “Resources” select “Select Function” => “refresh” => Run (on the left, triangle) => Close that page.

Now that that is set up you should have no need to return to it. You can now focus on setting up the sheet for use, and it is far easier than the above instructions.

Fill in the ID’s you want in Column A of the Data page
Sheet Tools => Refresh.
When creating stuff just reference the item in Data.

With regard to step 3 above I have found that there are two ways to reference, a direct reference or a lookup. Here is both ways looking up information about item 19721. The direct reference links directy to the cell that contains: “=Data!C8”. However if anything in the Data page is moved around it breaks this. The second way avoids this problem entirely, it takes the ID and searches for it (B8 is referencing the ID). “=vlookup(B8, Data!A:O,3 ,false)”. While method two may seem longer it allows you to have a list of ID’s and then copy-paste down the formula to get the same bit of data about each of them. Experiment with both and see what works for you.

I have updated my Github project, if you want to branch it and develop it further go ahead.

Future plans.

I am currently working on turning this into a google script library which would allow automatic updates in the future, that will more than likely be v2.
I have up to now focused on how the ID’s are selected, I would like to improve on the main part of the code and tidy it up.

Recovered comments (manually typed word for word, my own comments are missing)


Henahax henahax.de 2015-04-20:
Thank you a lot for this, I just started building my own sheet and it works great!


Henahax henahax.de 2015-04-20:
It is quite messy at the moment mixed with bilingual craziness :D

But at the moment for example it lists me the ~10 cheapest rare runes by buy order and the average profit I make by throwing 4 of them into the mystic forge by comparing the cost to the averafe rune price of rare and exotic runes.


Silver Golden 2015-04-20:
Would you mind if I emailed ye?


Henahax henahax.de 2015-04-20:
Please feel free.