Need excel API guide

Communications Center General Discussion
1

Hi, does anyone know of a guide that shows how to use an API to make a dynamic market spreadsheet in EXCEL that is beginner friendly?

I have looked at a few including fuzzworks but they all seem to assume a certain amount of knowledge which I do not have, and I also do not know whether the information is up to date or not anyway.

I have a small amount of experience making a working google sheet last year using typeID’s but I don’t remember how to do it, and I want to use excel this time because google sheets gets too laggy for my purposes. I have no experience of how to do it in excel.

2

did you look at https://market.fuzzwork.co.uk/api/ ?

1 Like
3

It was this page I looked at https://www.fuzzwork.co.uk/2013/06/22/importing-price-data-into-spreadsheets/

I’ll try the one you have linked, thanks.

4

I’ve got it working now thanks. I should be able to figure out how to adapt it for my needs.

5

I do have one question.

I want to query about 100 items, so I want

https://market.fuzzwork.co.uk/aggregates/?station=60003760&types=1,2,3,.…,99,100

Is there a way to append the 100 numbers which correspond to the items I want without writing them explicitly at the end of the link? I tried to google but I don’t really know what I am looking for in order to achieve this.

6

you mean like referring to a list on one of the sheets of your workbook?

It’s possible. You want to create a url from cell references :smiley:

Assuming you’re using excel (as you said)

You’re going to have to replace the entries in the url with a reference to something.

You’ll be selecting a list of ids, and creating a named range from it. (formulas ribbon, define name)

Excel.CurrentWorkbook() lets you access your workbook.

Then you refer to the range, get the data in it, eliminate empty values, convert it to text (as it’s probably numbers), convert it to a list, them combine it with commas.

Took me a little time to add that all together.

You need to add

Text.Combine(Table.ToList(Table.TransformColumnTypes(Table.SelectRows(Excel.CurrentWorkbook(){[Name="typelist"]}[Content], each ([Column1] <> null)),{{"Column1", type text}})),",")

At the end of the url you’re using. When it’s run, it’ll prompt for data privacy, for the access to the sheet. You may as well set them all public for this kind of data.

The easiest way I’ve found to do it is to put it into the advanced editor

For the contents. It requires named range called typelist. I just put my IDs into a single column on a specific sheet, selected it, and hit define name on the formula ribbon. Then called it typelist

1 Like
7

I’ll give this a go tomorrow. Thank you for your time :):grinning:

8

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.