Query API URL with Security Token Header?

Copper Contributor

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!

 

 

 

 

3 Replies

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...

One more bump to see if anyone has ever encountered this situation before.
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!