How to get data from web using Cell values to assemble URL to pull from? Getting Data from web using cell references

Question

How to get data from web using Cell values to assemble URL to pull from?

How do I go about using cell references to assemble a URL that excel can use to get data from the web with? Basically I'm trying to use cell values to assemble a full URL with query string to return the query result into another cell.

I have a worksheet with API URLs in one column and query strings in the next column over. I want to take say cell $B$2 (URL) and cell $C$2 (query string) to get URL results from =$B$2&$C$2

Most of the results would just be a number value and not have to be interpreted while other results are similar to JSON results and have to be navigated/burned down to the correct position to the desired result.

Get Data from web in Excel does a good job of this if I give it the URL natively... but.. the issue is trying to use that functionality in excel but instead just having it assemble the URL from cell references instead of putting in the URL natively in that get data from web form.

Any ideas? Thank you

Edit:
I reached out to one of my old professors that taught excel, he said he wasn't sure but suggested that a VBA script could be an option.

Solution

As far as I understand, you'll have to create a query table for each row, because the data URL for each row will be different/unique. Alternatively, you could use a single query table, and iterate over all rows, updating the table's connection URL in each iteration, and retrieving the fetched data into the appropriate row, using a VBA snippet like the following (disclaimer: haven't tested this):

' assuming your data starts at row 2
i = 2
Do While Not IsEmpty(Sheet1.Cells(i, 2))
    url = "TEXT;" & Sheet1.Cells(i, 2) & Sheet1.Cells(i, 3)
    With Sheet1.QueryTables
        If .Count < 1 Then
            .Add url, Sheet1.Range("Z1")
        Else
            .Item(1).Connection = url
        .Item(1).Refresh
        ' assuming column D would hold the results
        Sheet1.Cells(i, 4) = Sheet1.Range("Z1")
        ' do any further manipulations of the fetched Sheet1
    End With
    i = i + 1
Loop