0

I want a cell in Microsoft Excel (of type: dropdown list of strings) to fetch data from -

an API endpoint returning JSON response of array of strings (this format can be changed)

eg. response:

[
"Oranges",
"Apples",
"Mangoes"
]

I want something like : Set the formula of the cell to FetchList("localhost:8080/api/v1/list").

(FetchList is randomly written.)

How can I get started to achieve this ?

Thanks for any help!

Sagar Agarwal
  • 153
  • 2
  • 11

1 Answers1

0

One way to do this with Office 365 is place a formula somewhere in your workbook that contains the values that you want to present in the dropdown and then refer to that formula.

The formula would be:

=  TRANSPOSE(
       TEXTSPLIT(
               SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( J2, """", "" ), "[", "" ), "]", "" ),
               "," ) )

Where J2 contains the JSON string ["Oranges","Apples","Mangoes"] that you fetched. (i.e. the result of "localhost:8080/api/v1/list")

If you put that in cell D2 for example, then you can go to Data > Data Validation and choose List with the formula being =$D$2#.

enter image description here

enter image description here

A more readable version of the formula might be:

=LET( s, J2,
        s_cln, SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( s, """", "" ), "[", "" ), "]", "" ),
        TRANSPOSE( TEXTSPLIT( s_cln, "," ) ) )
mark fitzpatrick
  • 3,162
  • 2
  • 11
  • 23
  • I want that Excel should automatically send the api request to get data and then populate the list. So whenever I open the Excel workbook, it fetches the latest api data and populates – Sagar Agarwal Jan 30 '23 at 09:42
  • OK - this can be done a number of ways including VBA or PyXL, but it could be that the easiest is Power Query. You might be able to make the API connection directly through the API URL. A lot more would have to be known about the API. – mark fitzpatrick Jan 31 '23 at 11:44