Need excel API guide

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