Main Forum

Need some help with Google spreadsheets

4 replies
Deioth AdminRevBnSBnSTERA
Posts:
573
Votes:
+421
ROSHI
Admins
Rev Manager
BnS Ex Manager
BnS Retired
TERA Ex Manager
TERA Retired
Donator
Hey everyone,

While this is mainly for the BnS roster, maybe it can also help the WS roster? Help from anyone is welcome.
In short, I started to integrate some functions to get base values so I can focus on improving one aspect our fighting strength (gear) for PvX and GvG.


I am using the basic commands that some of you linked me from here, such as:
Copy
=IMPORTXML("http://eu-bns.ncsoft.com/ingame/bs/character/profile?c="&D2,"(//span[@class='stat-point'])[1]")

wm8aq4C.png

However, these seem to fall apart randomly and I end up with this:
u8rfKk2.png

One poster mentioned the following but frankly I don't know enough about coding to figure it out:
Azuriel wrote:
Great writeup and thanks for compiling this!

However, I'd suggest you use UrlFetchApp API provided by google instead of sheet function ImportXML, since ImportXML is well known to have "issues" for instance, multiple calls from the function will just break the function completely.
Azuriel wrote:
UrlFetchApp is a javascript class included in google API. You can see them here. It does exactly the same thing as ImportXML, That is both are used to fetch an entire page from a given URL, and read the data. However unlike ImportXML you need to actually write the parser for the data themselves. But this also means that you can make a more reliable and lighter script. Problem is you need you need some code-warrioring skills.


Would any of you please be able to help me? If some of you are experienced and willing to teach me some other things I am very open for any knowledge, any help in improving efficiency is very welcome.


Cheers,
Deioth
Posted Apr 13, 16 · OP · Last edited Apr 13, 16
Elth BnSTERA
Posts:
40
Votes:
+3
BnS Retired
TERA Retired
Maybe it wont work when servers are during maintenance. Same as here http://eu-bns.ncsoft.com/bns/common/error/error. Did you try to check it before maintenance? It started early today (7AM UTC)
And this examble spreadsheets also dont shows anything now https://docs.google.com/spreadsheets/d/1E_qryMTo-3uN2eVN7H9gOn5fCF3pU4WmQvM0T9ic6b0/edit#gid=0

Im not experienced with stuff like that but this is my only idea.
Posted Apr 13, 16 · Last edited Apr 13, 16
Ankah BnS
Posts:
67
Votes:
+40
LEGENDARY
BnS Retired
No, it's not solely due to the maintenance (even if you're right it can't work at the moment seems the website maintenance is over now) even when the server is up for some data it won't load and you need to refresh.

I'll try to look into your links Dei, I come back to you tomorrow night if I found a better solution ;)
Posted Apr 13, 16 · Last edited Apr 13, 16
Ankah BnS
Posts:
67
Votes:
+40
LEGENDARY
BnS Retired
Ok, it'll work, had to tinker around to make it work since NCsoft can't even make a valid html document for their webpage, it wouldn't parse, had to use and old (and not maintained anymore by google) function to bypass the parsing error.
It'll need a bit of work to parse every single stat from the page but I can do a quick version with some stats for you to work with.
Please share me your spreadsheet so I can see how to integrate it.

The problem imo with importxml is that it was making too many requests to the ncsoft server and it couldn't follow with the response so some times out (could be because of an average/bad internet connetion too since the timout for the response seams really low). There was this much requests because it was making a cal for every single stat for every single person.

As I did in the script I just developped is that I'm making one call only by character and parsing all the data by character.
So it's a lot less request and should be faster.

Welp gonna get back to work now >.> It's your fault Dei if I get yelled at by my boss, you knew I would want to find the solution :p
Posted Apr 13, 16 · Last edited Apr 13, 16
Deioth AdminRevBnSBnSTERA
Posts:
573
Votes:
+421
ROSHI
Admins
Rev Manager
BnS Ex Manager
BnS Retired
TERA Ex Manager
TERA Retired
Donator
Man that's awesome thanks Ankah <3
I'll make a copy and give you access to the super secret Management spreadsheet.
Posted Apr 13, 16 · OP · Last edited Apr 13, 16
NoticeNotices