Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Odata feed URL basic authentication

Hi,

 

I created OData.Feed and manualy insert my username and password into basic authentication. It works fine, but there is my credentials all the time in the same excel workbook.

My idea is ...  when somebody refresh Odata connection, he has to insert his own credentials. For example username and password will be in URL for data feed and it will be a variable. Something like example below for variables for date_from and date_to in URL.

 

Is it possible add here some authentication? or any idea?

 

let
    Source1 = Excel.CurrentWorkbook(){[Name="date_from"]}[Content],
    from = Source1{0}[date_from],
    Source2 = Excel.CurrentWorkbook(){[Name="date_to"]}[Content],
    to = Source2{0}[date_to],
    Source3 = Excel.CurrentWorkbook(){[Name="profile"]}[Content],
    pr = Source3{0}[profile],

    Zdroj = OData.Feed("http://x2-tst/tss/ProfileData(path='" & pr & "')?contextFrom=" & from & "&contextTo=" & to, null, [Implementation="2.0"])
IN

 

Thank you

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    It is my final solution:

     

    ...

    Source4 = Excel.CurrentWorkbook(){[Name="encode"]}[Content],
    options = Source4{0}[encode],

    Zdroj = OData.Feed("URL, null, [Headers = [#"Authorization"=options]]),

...

    variable "options" is store in cell in excel sheet.

    Options is "Basic dXNlcm5hbWU6cGFzc3dvcmQ=" is the base64 encoded string of sample credential username: password . Between username and password is no space.

     

    It works perfectly.

     

    This is function for excel for encoding string from username: password to base64 encoded string. May be useful.

    Function EncodeBase64(text As String) As String
  Dim arrData() As Byte
  arrData = StrConv(text, vbFromUnicode)
  Dim objXML
  Dim objNode
  Set objXML = CreateObject("MSXML2.DOMDocument")
  Set objNode = objXML.createElement("b64")
  objNode.DataType = "bin.base64"
  objNode.nodeTypedValue = arrData
  EncodeBase64 = objNode.text
  Set objNode = Nothing
  Set objXML = Nothing
End Function

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    It is my final solution:

     

    ...

    Source4 = Excel.CurrentWorkbook(){[Name="encode"]}[Content],
    options = Source4{0}[encode],

    Zdroj = OData.Feed("URL, null, [Headers = [#"Authorization"=options]]),

...

    variable "options" is store in cell in excel sheet.

    Options is "Basic dXNlcm5hbWU6cGFzc3dvcmQ=" is the base64 encoded string of sample credential username: password . Between username and password is no space.

     

    It works perfectly.

     

    This is function for excel for encoding string from username: password to base64 encoded string. May be useful.

    Function EncodeBase64(text As String) As String
  Dim arrData() As Byte
  arrData = StrConv(text, vbFromUnicode)
  Dim objXML
  Dim objNode
  Set objXML = CreateObject("MSXML2.DOMDocument")
  Set objNode = objXML.createElement("b64")
  objNode.DataType = "bin.base64"
  objNode.nodeTypedValue = arrData
  EncodeBase64 = objNode.text
  Set objNode = Nothing
  Set objXML = Nothing
End Function

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you v-lili6-msft , but do you think, is it possible to do it in Excel Power Query, becasue I work with Excel.

      I thought something like this:

      [Headers =[#"Authorization"="Basic username: pasword"]]

      but it doesn't work for me and I don't know why.

       

      Palo