API Query Error

[Anonymous]

Hi 

I'm just trying to do my first connection from Power Query using an API using the POST method and have the following code 

let
    url = "https://app.eresourcescheduler.cloud/rest/v1/utilization?",
    headers = [#"Content-Type" = "application/json",  #"Authorization"="Bearer xxx"],
    param = Json.FromValue([view="resource&start=2022-08-01&end=2022-08-31"]),
    web = Web.Contents(url,[Content = param , Headers = headers] ),
    result = Json.Document(web)   
in
result

I am getting an error message access to "Access to resource is forbidden". I have checked Authorization Key used against another API query which uses the GET method from the same source and which works ok. I'm thinking that I'm getting this message possibly because the syntax is incorrect, but I don't know what's wrong. I'd be grateful for any advice 

Example quoted for this query from the API documentation 

Example: Get utilization by resources

curl -X POST "https://app.eresourcescheduler.cloud/rest/v1/utilization?\
view=resource&start=2022-05-01&end=2022-05-31" \
-H "Content-Type: application/json" \
-H "Authorization: Bearer B8x5Vj1O65r6wnoV" \

A link to the API documentation follows https://apidocs.eresourcescheduler.cloud/#get-utilization 

many thanks

Corb

[Anonymous · Accepted solution]

Hi @Daryl-Lynch-Bzy 

Many thanks for your help with this and apologies for not responding sooner.

I have now managed to get the connection working. I ended up passing the parameters as part of the relative path and then for Content passing Text.ToBinary("") which I think is required because the method is  POST.

let
     startdate = Date.ToText(DateTime.Date(DateTime.LocalNow()),[Format = "YYYY-MM-DD"]), // set start date as today's date 
    enddate = Date.ToText(Date.AddDays(DateTime.Date(DateTime.LocalNow()),91),[Format = "YYYY-MM-DD"]), //  set end date as today plus 91 days 
    url = "https://app.eresourcescheduler.cloud", // set base URL 
    rpath = "/rest/v1/utilization?limit=25&start="& startdate &"&end=" & enddate, //set remainder of URL and parameters to be passed
    headers = [#"Content-Type" = "application/json",  #"Authorization"= #"API-Key"], //set content type and authorisation key format = "Bearer xxxxxxxxxxxxxxxxxxxxx"
    web = Web.Contents(
                   url ,
                    [
                            RelativePath = rpath,
                            Content = Text.ToBinary(""), // included as method is POST 
                            Headers = headers
                    ]
                                    
    ),
    result = Json.Document(web),
    #"Converted to Table" = Record.ToTable(result),
    Value = #"Converted to Table"{4}[Value],
    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "name", "total_planned_hrs"}, {"Column1.id", "Column1.name", "Column1.total_planned_hrs"})

in 
#"Expanded Column1"

There was a bit more to it than the above as there is a 25 record restriction with the API so I'm posting the full solution (which I'm sure is not model answer 🙂 but it works) in the hope that it might help someone.

I had to create the following function which is based on the above 

(OffsetValue) =>

let
 
    startdate = Date.ToText(DateTime.Date(DateTime.LocalNow()),[Format = "YYYY-MM-DD"]),
    enddate = Date.ToText(Date.AddDays(DateTime.Date(DateTime.LocalNow()),91),[Format = "YYYY-MM-DD"]),    
    url = "https://app.eresourcescheduler.cloud",
    
    headers = [#"Content-Type" = "application/json",  #"Authorization"= #"API-Key"],
    web = Web.Contents(
                   url ,
                    [
                            RelativePath = "/rest/v1/utilization?limit=25&offset="&Number.ToText(OffsetValue)&"&start="& startdate &"&end=" & enddate,
                            Content = Text.ToBinary(""),
                            Headers = headers
                    ]
                                    
    ),
    result = Json.Document(web),

    #"Converted to Table" = Record.ToTable(result),
    Value = #"Converted to Table"{4}[Value],
    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "name", "total_planned_hrs"}, {"Column1.id", "Column1.name", "Column1.total_planned_hrs"})

in 
#"Expanded Column1"
   

The offsetvalue in the above is generated from the following query which generates a list of values in increments of 25 and passes this to the above function to get the next batch of records

let
    Custom1 = List.Numbers(0 as number, 10 as number,25) as list,
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Invoked Custom Function" = Table.AddColumn(#"Converted to Table", "Query", each #"FX Utilisation"([Column1])),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"Query"}),
    #"Expanded Query" = Table.ExpandTableColumn(#"Removed Errors", "Query", {"Column1.id", "Column1.name", "Column1.total_planned_hrs"}, {"Query.Column1.id", "Query.Column1.name", "Query.Column1.total_planned_hrs"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Query",{"Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Query.Column1.name", "Resource Name"}, {"Query.Column1.total_planned_hrs", "Total Planned Hours"}, {"Query.Column1.id", "ID"}})

    in
    #"Renamed Columns"

 thanks and kind regards

CORB

[Anonymous]

Many thanks for your help @Daryl-Lynch-Bzy 

How would I format the line 

param = Json.FromValue([view="resource&start=2022-08-01&end=2022-08-31"]),

to pass the values correctly ? This was the part I was most unsure of and have tried several ways without success.

[Daryl-Lynch-Bzy]

I would have thought that this would work.

param = Json.FromValue([view="resource" , start="2022-08-01" , end="2022-08-31"])

Other examples that I have seen would suggest the following approach over Json.FromValue:

let
    url = "https://app.eresourcescheduler.cloud",
    path = "/rest/v1/utilization",
    headers = [#"Content-Type" = "application/json",  #"Authorization"="Bearer xxx"],
    param = "{""view"":""resource"", ""start"":""2022-08-01"", ""end"":""2022-08-31""}",
    web = Web.Contents(url,[RelativePath = path, Content = Text.ToBinary(param), Headers = headers] ),
    result = Json.Document(web)   
in
result

However upon looking at your CURL example again, the following might work.  But this is not Post??

let
    url = "https://app.eresourcescheduler.cloud",
    path = "/rest/v1/utilization?view=resources&start=2022-08-01&end=2022-08-31",
    headers = [#"Content-Type" = "application/json",  #"Authorization"="Bearer xxx"],
    web = Web.Contents(url,[RelativePath = path, Headers = headers] ),
    result = Json.Document(web)   
in
    result

[Daryl-Lynch-Bzy]

Hi @Anonymous - I don't think you need the ? in the first line of your code, and you should try to use a relativepath because the authetication will be first tested using 

"https://app.eresourcescheduler.cloud/rest/v1/utilization?"

Could you try with the following:

let
    url = "https://app.eresourcescheduler.cloud",
    path = "rest/v1/utilization",
    headers = [#"Content-Type" = "application/json",  #"Authorization"="Bearer xxx"],
    param = Json.FromValue([view="resource&start=2022-08-01&end=2022-08-31"]),
    web = Web.Contents(url,[RelativePath = path, Content = param , Headers = headers] ),
    result = Json.Document(web)   
in
result

If that does not work, it is possible that the param is not formatted as JSON.  I think the "&" will be wrong.  They would be appropriate if you were add this string to you RelativePath.  I would expect the JSON to look like this.

{
  "view":"resource",
  "start":"2022-08-01",
  "end":"2022-08-31"
}

but currently it is like this:

{
  "view":"resource&start=2022-08-01&end=2022-08-31"
}

[Daryl-Lynch-Bzy]

@Anonymous - sorry had a trouble posting that I am finished now.