ESI 1k row limit in Excel

[Tyrannicus]

Hey guys,

I’ve been building an excel spreadsheet for market analytics using the guide at https://web.archive.org/web/20190310221844/https://wiki.zansha.space/index.php/SSO_Authentication_in_Microsoft_Power_Query. I have successfully got it to pull market data from a particular player owned structure (using the GET /markets/structures/{structure_id}/ call).

However, my query is only giving me the first 1,000 rows of the market contents, and the structure in question has significantly more than that. I’m trying to figure out how to restructure my query in such a way as to pull down all the pages at once. I’ve seen references in various forum posts to tweaking the “X-Pages” header, and potentially iterating a script to increment the call x number of times until no further data has been returned, but haven’t been able to get any of them to work so far. Has anyone had any success with getting more than 1k results?

For the sake of reference, here’s my redacted excel query:

let
Source = Json.Document(Web.Contents(“https://esi.evetech.net/latest/markets/structures/[[[Structure ID]]]/?datasource=tranquility&token=[[[TOKEN]]]”)),

Appreciate any help that you guys can shed on this topic and/or helping me set this up in a better fashion!

[Blacksmoke16]

You don’t “tweak” X-Pages, you use it to determine how many pages there are total then you iterate that many times. I.e. so you do your first request and X-Pages comes back with 5. You already know the first page, so you only need to fetch the next 4. So you would iterate 4 times, adding a &page=n query param to each of those 4 requests, staring with 2; concatenating the results of each until you have one big array of data that includes all pages.

What the exact syntax is to do that in Excel, im not sure; but I imagine it’s doable.

[Donaldo_Duck]

Some time ago i played with the public ESI endpoints and multi page results.
The following page help me a lot and work for me - should work on the auth parts as well, but i haven’t tried.

SumProduct