Need Help to retrieve an API result in Worksheet

[Atri R]

Hello,

I am designing an excel file which will provide me the company website URL taking the Company name as Input. For this I have found out a free API which does this exact Name->Domain mapping.

The API goes like this:

https://autocomplete.clearbit.com/v1/companies/suggest?query=[C_NAME]

where C_NAME is the company names present in the worksheet column.

• The response of the API usually has 3 parameters (name, domain and logo). I would like to have the domain value only to be inserted in the excel column.
• At times, the response can be multiple sub nodes - in that case I would love to have the first domain only(which is possibly most relevant).
• Also, sometimes the response might be empty when the API is not able to lookup for the company name. In this case, the cell can be populated with a default value.

Can anyone help me write a macro/VBA code for this small function? It would be of a great help.

TIA

 

[Norie]

Can you provide some example company names?

 

[Atri R]

Can you provide some example company names?

Sure, few samples:

https://autocomplete.clearbit.com/v1/companies/suggest?query=Oracle
https://autocomplete.clearbit.com/v1/companies/suggest?query=Textlocal
https://autocomplete.clearbit.com/v1/companies/suggest?query=Trello

All the above cases, it will have multiple sub-nodes, In these cases I need the first 'domain' value from the response. You can quickly browse any of the URL to get an idea.

Thanks

 

[Atri R]

Sure, few samples:

https://autocomplete.clearbit.com/v1/companies/suggest?query=Oracle
https://autocomplete.clearbit.com/v1/companies/suggest?query=Textlocal
https://autocomplete.clearbit.com/v1/companies/suggest?query=Trello

All the above cases, it will have multiple sub-nodes, In these cases I need the first 'domain' value from the response. You can quickly browse any of the URL to get an idea.

Thanks

Anyone? Thanks!

 

[Norie]

I don't know if this is any use, it uses, Power Query but take a look - MrExcelPQCompanyThing.

 

[Atri R]

I don't know if this is any use, it uses, Power Query but take a look - MrExcelPQCompanyThing.

Hi Norie, the sample is how I wanted. Thanks.

Can I know the code details please?

 

[Norie]

There is no 'code' as such, well not VBA anyway.

The workbook uses two queries in Power Query to pull the data.

If you select a cell in the table with the results you should have an Edit option on the ribbon.

If you press that it should open Power Query and you should be able to see the queries.

 

[Atri R]

There is no 'code' as such, well not VBA anyway.

The workbook uses two queries in Power Query to pull the data.

If you select a cell in the table with the results you should have an Edit option on the ribbon.

If you press that it should open Power Query and you should be able to see the queries.

Hi Norie, Sorry - where's the edit option? A screenshot should really help!

Thanks a lot

 

[Atri R]

PS: I am using a Mac

 

[Norie]

Now you've got me, I'm not at a computer right now so can't recall where it is on a PC never mind a Mac.

I'll check it out and get back to you.

PS Does the workbook actually work for you?

What happens if you add more company names to the table on the first sheet and then right click the table on the second sheet and select Refresh?