Excel: How to set a dropdown list cell to fetch list of strings from an API JSON response

Question

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!

Answer 1

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#.

A more readable version of the formula might be:

=LET( s, J2,
        s_cln, SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( s, """", "" ), "[", "" ), "]", "" ),
        TRANSPOSE( TEXTSPLIT( s_cln, "," ) ) )