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 an 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?

Answer 1

The values you wish to display in the dropdown can be put in a formula that you write anywhere in your workbook and then use to accomplish this with Office 365.

The formula would be:

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

Where J2 holds the retrieved JSON string, "Oranges," "Apples," and "Mangoes." (For example, "localhost:8080/api/v1/list" results)

For instance, if you enter that in cell D2, you can navigate to Data > Data Validation and select List with the formula =$D$2#.

A more readable version of the formula might be:

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