Connecting NewBook REST API to Power BI

[KKailly]

Hi,

I am using a power query to connect NewBook Rest API with Power BI but no luck so far. Doesn't matter what I try I keep ending with the same errors:

"Use Anonymous for Web.contents' and if I use anonymous then "Couldn't authenticate using these credentials.

Any help will be much appreciated.

Rest API documentation is found here: https://developers.newbook.cloud/rest.php 

Below is my code snippet

let
BaseUrl = "https://api.newbook.cloud/rest/",
// Get Basic Auth Token using https://www.debugbear.com/basic-auth-header-generator and username / password
Token = "Replace_with_Basic_Auth_Header_Random_String_Only",
NB_Region = "au",
NB_APIKey = "Replace_with_API_Key",
EntitiesPerPage = 500,

GetJson = (Url,PageOffset) =>
let body = "{
""api_key"": """& NB_APIKey &""",
""region"": """& NB_Region &""",
""request_action"": ""instances_list"",
""data_offset"": """& Text.From(PageOffset)&""",
""data_limit"": """& Text.From(EntitiesPerPage)&"""
}",
Options = [Headers=[ #"Authorization" = "Basic " & Token, #"Content-Type" = "application/json" ], Content=Text.ToBinary(body)],
Json = Json.Document(Web.Contents("https://api.newbook.cloud/", [Headers=[ #"Authorization" = "Basic " & Token, #"Content-Type" = "application/json" ], Content=Text.ToBinary(body), RelativePath="rest"]))
in Json,

GetEntityCount = () =>
let Url = BaseUrl,
Json = GetJson(Url,0),
Count = Json[#"data_total"]
in Count,

GetPage = (Index) =>
let Skip = Text.From(Index * EntitiesPerPage),
Url = BaseUrl,
Json = GetJson(Url,Skip),
Value = Json[#"data"]
in Value,

EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(Number.From(EntityCount) / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
#"Converted to Table" = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

[swissjensenite]

I'm using Postman to fiddle with authentication but I keep getting a "

Newbook REST API authentication incorrect (username or password)" error and

I'm curious if there is something obvious that I'm missing.

URL: https://api.newbook.cloud/rest/api_keys

Method: POST

Authentication: Postman allows you to input your username and password it generates a Basic Auth token and inserts it into the request headers under "Authorization": "Basic XXXXXXXXXXXXXXXXXXX"

Any ideas on why I can't get it authenticate correctly?

[prospitality]

Hi Folk,

I know its been a while, but has anyone got this working more recently after the changes to base URL's in the NewBook API?

[KKailly · Accepted solution]

Thank you everyone for your input, I finally figured it out.

This code worked for me:

let
BaseUrl = "https://api.newbook.cloud/rest/",
// Get Basic Auth Token using https://www.debugbear.com/basic-auth-header-generator and username / password
Token = "<Generated Token>",
NB_Region = "au",
NB_APIKey = "<API Key>",
body = "{
""api_key"": """& NB_APIKey &""",
""region"": """& NB_Region &""",
""request_action"": ""bookings_list"",
""list_type"":""staying"",
""period_from"":""2019-01-01"",
""period_to"":""2023-12-31""
}",
Json = Json.Document(Web.Contents("https://api.newbook.cloud/", [Headers=[ #"Authorization" ="Basic " & Token, #"Content-Type" = "application/json" ], Content=Text.ToBinary(body), RelativePath="rest"]))

in Json

[mahoneypat]

Your body line does not seem to have the right syntax.  It is easier to generate your content with this approach instead and then use Json.FromValue to convert it to JSON syntax for your web call.

let
body = [
api_key = NB_APIKey,
region = NB_Region,
request_action = "instances_list",
data_offset = Text.From(PageOffset),
data_limit = Text.From(EntitiesPerPage)],
bodyjson = Json.FromValue(body),
in

bodyjson

Pat

[KKailly]

Hi Pat,

Thank you for the tip but I still keep getting one of the authentication errors i.e. can only use anonymous for web.contents and if switched to anonymous then couldn't authenticate with credentials provided. I'm still learning to write queries so it is possible that I'm making some silly mistake.

This is my code as of now:

let
BaseUrl = "https://api.newbook.cloud/rest/",
Token = "Replace_with_Basic_Auth_Header_Random_String_Only",
NB_Region = "au",
NB_APIKey = "My API Key",
EntitiesPerPage = 500,

GetJson = (Url,PageOffset) =>
let body = [
api_key = NB_APIKey,
region = NB_Region,
request_action = "instances_list",
data_offset = Text.From(PageOffset),
data_limit = Text.From(EntitiesPerPage)],
bodyjson = Json.FromValue(body),
Json = Json.Document(Web.Contents("https://api.newbook.cloud/", [Headers=[ #"Authorization" = "Bearer " & Token, #"Content-Type" = "application/json" ], Content=bodyjson, RelativePath="rest"]))
in Json,

GetEntityCount = () =>
let Url = BaseUrl,
Json = GetJson(Url,0),
Count = Json[#"data_total"]
in Count,

GetPage = (Index) =>
let Skip = Text.From(Index * EntitiesPerPage),
Url = BaseUrl,
Json = GetJson(Url,Skip),
Value = Json[#"data"]
in Value,

EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(Number.From(EntityCount) / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
#"Converted to Table" = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

[lbendlin]

Shouldn't that say

#"Authorization" = "Bearer " & Token

?

[KKailly]

Hi Ibendlin,

I did try that but still no luck.

If I select anonymous it says couldn't authenticate using these credentials and if I select Basic, it says can only connect using anonymous.