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

0 votes

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?

Jan 30, 2023 in Others by Kithuzzz
• 38,010 points
729 views

1 answer to this question.

0 votes

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

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, "," ) ) )
answered Jan 30, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
0 answers

how to list the contents of a asset into an event

May 29, 2019 in Others by anonymous
458 views
0 votes
1 answer
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,420 points
535 views
0 votes
1 answer

How to insert a new row in the specified cell only, of Excel sheet using c#?

I have this worksheet with a matrix ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,420 points
1,882 views
0 votes
1 answer

Tableau For Loop a String

In the case of [ { ...READ MORE

answered Feb 20, 2023 in Others by Kithuzzz
• 38,010 points
348 views
0 votes
1 answer

Power Query : making HTTP POST request with form data

Try Using Uri.BuildQueryString and Json.Document let ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
5,665 views
0 votes
0 answers

Excel .json Import Error: The Type of Current Preview Value is Too Complex to Display

Main Problem: I'm trying to import .json to excel ...READ MORE

Mar 15, 2020 in Python by Tarık
• 120 points
1,347 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,447 views
0 votes
1 answer

How to stick an embedded document in a specific cell of an excel

Solution Select the documents (you can use the ...READ MORE

answered Oct 18, 2022 in Others by narikkadan
• 63,420 points
430 views
0 votes
1 answer

How to trick an Excel function that wants a column as input to accept a list of values as if these were in a column

Use VSTACK: vstack to make an array: Use it as value ...READ MORE

answered Mar 18, 2023 in Others by narikkadan
• 63,420 points
289 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP