Integrating Ffxiah Data Into A Spreadsheet

Apologies if this is old news, i'm sure at least a few people have figured it out (some credit) but I doubt its common knowledge.

You can get real time pricing data into a spreadsheets, trick #1 is to use the ?sid=<your server> in the URL, and trick #2 is to use use the HTML/XML parsers in the program.

Here's a <url redacted> comparing copper vouchers gil value.

I put the server id in S1, (7 is fenrir, I don't know the rest)

Couple things to note: for the most part you can paste Columns B-O to another row and just put the cookie based url you get from your browser in column A and it should work. Although a few items for both FFXIAH and the original wiki (to get the NPC price) have 2-3 different formates so some of the table indexing might need to be adjusted slightly. Columns D, E, F, I have 2-3 slightly different formats.

FWIW i'm basically combinging the "Rewards" links in the left side of this site, but it checks the sales rate snd recommends the NPC price when appropriate.

Each row is doing multiple lookups so its kinda slow, Probably best to delete the non-interesting data (which is arguably all of it once you learn the best items to AH or NPC) Smaller sheets do perform reasonably I have a 2nd tab where I was just watching the AH for relic upgrade items/mats and it was handy to see it in one place without having to do multiple searches.

AI tools like grok/chatGPT/google search can do a decent (far from perfect) job of helping you parse the xpaths (i'm slow but I can figure it out)

IF there's any specific questions I can try and answer, otherwise if I hope its helpful,

I think the issue here isn't that it's difficult to accomplish this, but that there isn't an API for you to pull their data. You're scraping over 100 pages from xiah and 65 from xiclopedia every time this updates (maybe more, depending on if sheets uses cacheing since you import from the same url 3x). It's incredibly disrespectful to the hosts and other users to scrape in this manner, especially from a passion project like XIAH.

I'm sure Rooks or the admins will weigh in on whether they think this is ok at some point, but the problem is you've already shared it. If they decide that it's not ok and others have copied the sheet, you've potentially created a small scale DDoS against this website. You should ask before creating anything that automatically interacts with a web server, especially one that may not have the resources of a large company.

I mean the best case scenario is I implement rate limiting, which I am loath to do because that kind of thing is always either tuned too high to not matter or so low it impacts legitimate site users.

If you want the data that bad, write your own scraper. Wireshark tells you everything you need to know, so hop to it.

I'm certainly not trying to cause any trouble and I can delete the sheet if so.

I have no idea how google caches its stuff. It seems it does not query the URL unless the browser is open and in that tab. I've noticed this with this and other sites I import live data from, as when I click on the tab the values change a second later (seeing the previous value initially) I am guessing it does multiple queries for the different INDEX() wrappers, (I'm sure it would be better to do that once and reference the output locally but that would be a pain from the human side - need a new tab for each item)

I think the bottle neck is sheets itself though, not the resultant websites (sheets works pretty well with only a few rows in it, and even with all the rows in sheets it can be slow but manually going to those pages in ah/wiki works fine).

I mean it sounds like what you actually want is for the rewards section to have NPC prices, in which case, why not just ask for that?

A solution to a problem that doesn't exist. A product nobody asked for.

Sheets queries the website at least once every time any user opens the sheet, and (at least) once more hourly while the tab remains open in addition to other triggers. If you leave the tab open overnight, you've probably put more demand on xiah servers than an average person does in a week. That's before getting into the possibility of 50 folks sharing the same sheet and leaving it open.

If XIAH wanted automated tools pulling it's data, they'd have offered an API so that you could just retrieve the numbers you want instead of taking an entire page of html and throwing it out to get one number. They don't offer that, so you can reasonably infer their goal is not to exist as a backend for these sorts of tools.

Obviously, I can't speak for Rooks or Cliff. But, Rooks is here. He just told you to write your own scraper if you want the data. Seems pretty clear cut that they don't want this sort of thing.

The issue isn't with how well your sheet works. You're putting extra load on the servers hosting the data and sharing it allows it to rapidly scale upward. The impacted party is the web server, not you.

Well kinda (I probably would not have jumped down this rabbit hold if it was there), and did not know I could.

I appreciate this site and not trying to cause trouble, so thank you.

Thats why the site was so laggy lately!

And here I was thinking it was because the sewer rat was slacking at work…

Every time you search an item in FF11AH, Rooks has to go into FF11 and look the item up manually. Give him a break.

Good idea !

Good idea !

I think my google sheets give already this feature since many years (maybe 7 or 8 years). And it's still works.

But it's not an automatic request, we put a "update" button, so it will check the price only when i click on it.

I didn't wrote this function, and i'm not developper so it's a little obvious for me, but i wanted share my experience.