[Solved] Import data from website that requires authentication

[AMANZ]

Hi

Would like to display data from a weblink. Once data is received, I can trim / concatenate the string.

==WEBSERVICE("link") seems to do the job for non-authenticated websites. For example

=WEBSERVICE("https://api.open-meteo.com/v1/forecast?latitude=52.3555&longitude=1.1743&current=temperature")

will return temperature 8.8 °C for England @ 52.3555°N, 1.1743°W

{"latitude":52.36,"longitude":1.1799998,"generationtime_ms":0.0209808349609375,"utc_offset_seconds":0,"timezone":"GMT","timezone_abbreviation":"GMT","elevation":35.0,"current_units":{"time":"iso8601","interval":"seconds","temperature":"°C"},"current":{"time":"2023-11-13T05:00","interval":900,"temperature":8.8}}

The above string can be processed for displaying data in different cells.

How do I go about getting data when site needs authentication

For websites requiring authentication, Excel is returning below message into cell that contains WEBSERVICE function

<!-- hasCustomLogin = true --><!-- https://............................

Perhaps there is another function similar to WEBSERVICE("")
or an initial command is required to authenticate

** I do have login credentials to the site ~ this is not about by-passing login

 

[Aniruddah Alam]

Hi

Would like to display data from a weblink. Once data is received, I can trim / concatenate the string.

==WEBSERVICE("link") seems to do the job for non-authenticated websites. For example

=WEBSERVICE("https://api.open-meteo.com/v1/forecast?latitude=52.3555&longitude=1.1743&current=temperature")

will return temperature 8.8 °C for England @ 52.3555°N, 1.1743°W

{"latitude":52.36,"longitude":1.1799998,"generationtime_ms":0.0209808349609375,"utc_offset_seconds":0,"timezone":"GMT","timezone_abbreviation":"GMT","elevation":35.0,"current_units":{"time":"iso8601","interval":"seconds","temperature":"°C"},"current":{"time":"2023-11-13T05:00","interval":900,"temperature":8.8}}

The above string can be processed for displaying data in different cells.

How do I go about getting data when site needs authentication

For websites requiring authentication, Excel is returning below message into cell that contains WEBSERVICE function

<!-- hasCustomLogin = true --><!-- https://............................

Perhaps there is another function similar to WEBSERVICE("")
or an initial command is required to authenticate

** I do have login credentials to the site ~ this is not about by-passing login

Dear AMANZ,
Unfortunately, I was unable to extract data from a URL that requires authentication using any other function. However, you can try using the "Get Data From Web" feature to see if it works for you. To do that,

• Go to Data/ Analysis tab, select From Web option, enter your url on the dialog box, click OK.
  
• Now, go to the Basic tab and put your User name and Password. Then click on Connect.

Let us know if it does the job for you. Good luck.

Aniruddah
Team Exceldemy

 

[AMANZ]

Hi
Thanks for your reply.

Basic authentication does not work. Most sites generate a cookie which needs to be referenced back.

So a bit stuck...

 

[Seemanto Saha]

Hi
Thanks for your reply.

Basic authentication does not work. Most sites generate a cookie which needs to be referenced back.

So a bit stuck...

Hello AMANZ,

Thanks for your feedback. Some authenticated sites generate a session cookie and send it back to your browser to maintain your authenticated session as you navigate through the site. The "From Web" feature that we suggested in our previous response doesn't automatically handle the cookies that the website may set after authentication.
If the website relies on cookies to maintain your session, you'll need to manage the cookies with VBA or Python programming. The requirements will vary based on the type of data available on the authenticated site.
Here is a sample VBA code.​

Sub GetDataFromAuthenticatedAPI()
    Dim internet_explorer As Object
    Dim html_doc As Object
    Dim element As Object
  
    Set internet_explorer = CreateObject("InternetExplorer.Application")

    IE.Navigate "https://example.com/login" ' Enter the login page url of the authenticated api

    Do While internet_explorer.Busy Or internet_explorer.readyState <> 4
        Application.Wait DateAdd("s", 1, Now)
    Loop

    internet_explorer.document.getElementById("username").Value = "your_username" ' enter your user name
    internet_explorer.document.getElementById("password").Value = "your_password" ' enter your password

    internet_explorer.document.getElementById("loginForm").submit

    Do While internet_explorer.Busy Or internet_explorer.readyState <> 4
        Application.Wait DateAdd("s", 1, Now)
    Loop

    Set html_doc = internet_explorer.document
    Set element = html_doc.getElementById("dataElementID")
  
    If Not element Is Nothing Then
        MsgBox "Data: " & element.innerText
    Else
        MsgBox "Element not found!"
    End If
  
    internet_explorer.Quit
    Set internet_explorer = Nothing
End Sub

I hope this solution will provide you with an idea of how to solve your problem. Let us know your feedback.
Regards,
Seemanto Saha
ExcelDemy