Forum Discussion

ERLoft's avatar
ERLoft
Copper Contributor
Mar 29, 2022

Query API URL with Security Token Header?

Any help here would be greatly appreciated.  I'm Director of Revenue Management for a hotel company and have been using queries to pull API competitive shop date into the workbook used for forecasting and rate analysis.

 

I've been informed by our shop vendor, OTA Insight, that they are changing how the API works.  Up to now, they've used a simple URL with my authentication token as part of the URL.  For example, the current URL for the query looks like this:

 

 

https://api.otainsight.com/v2/rates?token=MyToken&subscriptionId=MyID&ota=branddotcom&changeDays=2,5,7&shopLength=90&format=csv

 

 

They're requiring a move to this, but everything I've tried thus far results in an invalid URL message from Excel:

 

 

curl -H "X-Oi-Authorization: <MyToken>" https://api.otainsight.com/v3/rates...rue&changeDays=3,5,7&shopLength=90&format=csv

 

 

So, how can I implement his in Excel so I can continue to pull API shop data into Excel directly?

 

Thanks much!

 

 

 

 

excel

3 Replies

  • ERLoft's avatar
    ERLoft
    Copper Contributor
    Apr 01, 2022
    One more bump to see if anyone has ever encountered this situation before.
    • ERLoft's avatar
      ERLoft
      Copper Contributor
      Apr 14, 2022
      Got this worked out and posting solution for anyone who searches in the future.

      Headers can be added to queries via the Advanced option when creating a new Web query. However, once the query is created, you can't get back to that same screen - your entries are translated into what you see in the Advanced Editor for the query. Inserting the header information here. Here's samples of one without and one with the headers:

      Without Header:
      XML:
      let
      Source = Csv.Document(Web.Contents(functionGetNamedRange("URLLow")),[Delimiter=",", Columns=20, Encoding=1252, QuoteStyle=QuoteStyle.None]),
      #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
      #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"HotelID", Int64.Type}, {"HotelName", type text}, {"ExtractDateTime", type datetime}, {"ArrivalDate", type date}, {"Los", Int64.Type}, {"Value", type number}, {"Currency", type text}, {"Cancellation", type logical}, {"VatIncl", type logical}, {"CityTaxIncl", type logical}, {"OtherTaxesIncl", type logical}, {"RoomName", type text}, {"RoomType", type text}, {"MaxPersons", Int64.Type}, {"MealTypeIncluded", Int64.Type}, {"Message", type text}, {"ShopCurrency", type text}})
      in
      #"Changed Type"

      With Header:
      XML:
      let
      Source = Csv.Document(Web.Contents(functionGetNamedRange("URLBar"),[Headers=[#"X-Oi-Authorization"=functionGetNamedRange("APIKey")]]),[Delimiter=",", Columns=20, Encoding=1252, QuoteStyle=QuoteStyle.None]),
      #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
      #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"HotelID", Int64.Type}, {"HotelName", type text}, {"ExtractDateTime", type datetime}, {"ArrivalDate", type date}, {"Los", Int64.Type}, {"Value", type number}, {"Currency", type text}, {"Cancellable", type logical}, {"VatIncl", type logical}, {"CityTaxIncl", type logical}, {"OtherTaxesIncl", type logical}, {"RoomName", type text}, {"RoomType", type text}, {"MaxPersons", Int64.Type}, {"MealTypeIncluded", Int64.Type}, {"Message", type text}, {"ShopCurrency", type text}})
      in
      #"Changed Type"

      Hope this helps someone in the future!
  • ERLoft's avatar
    ERLoft
    Copper Contributor
    Mar 30, 2022

    Not looking good for an answer here.  If no one here knows, probably explains why I'm not getting any replies from the vendor about the API changes...

Resources