Forum Discussion
New API connection
Hi kushanNa
Thanks, you code works great for schedule but unfortunately we don't use schedule and it's empty so I cannot really see any data. I'm interested to check 2 things:
1. It's possible to detach connections to the import of a table: I would like to have a unique script for connection and then all the other separate scripts using that token.
2. One table I'm sure it's with data is Job Materials. I tried to amend your script but it's not working. Could you please help me to understand what's I'm doing wrong? Job Materials is for sure one table I need to achieve to pull.
Thanks in advance for the help!!!!
Super UserHi Anonymous
To answer your second question first:
When you said you tried to amend it, did you simply replace the URL withapi_url = "https://api-jb2.integrations.ecimanufacturing.com/api/v1/job-materials"
If so it should work , what error did you receive when you tried it?
For your first question:
Yes, you can pass the token parameter from one query to another, as shown below . However, it is generally better to keep the token generation within the same query unless you have a specific requirement. Even a slight delay between query refreshes can cause issues if the token expires or refreshes out of sync.
GetToken query
let
client_id = "YOUR_CLIENT_ID",
client_secret = "YOUR_CLIENT_SECRET",
token_url = "https://api-user.integrations.ecimanufacturing.com/oauth2/api-user/token",
tokenBody =
"grant_type=client_credentials"
& "&scope=openid"
& "&client_id=" & client_id
& "&client_secret=" & client_secret,
tokenResponse =
Json.Document(
Web.Contents(
token_url,
[
Headers = [
#"Content-Type" = "application/x-www-form-urlencoded",
Accept = "application/json"
],
Content = Text.ToBinary(tokenBody)
]
)
)
in
tokenResponse
JobMaterials query
let
// Read token from GetToken query
AccessToken = GetToken()[access_token],
api_url = "https://api-jb2.integrations.ecimanufacturing.com/api/v1/job-materials",
apiResponse =
Json.Document(
Web.Contents(
api_url,
[
Headers = [
Authorization = "Bearer " & AccessToken,
Accept = "application/json"
]
]
)
),
// API usually returns a list of records
AsList =
if apiResponse is list then
apiResponse
else if apiResponse is record and Record.HasFields(apiResponse, "data") then
apiResponse[data]
else
{},
TableOut =
if List.Count(AsList) > 0 then
let
tbl = Table.FromList(AsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
firstRecord = tbl{0}[Column1],
expanded = Table.ExpandRecordColumn(tbl, "Column1", Record.FieldNames(firstRecord))
in
expanded
else
#table({}, {})
in
TableOut
Hi kushanNa,
Token Query works like a clock. As you can see the default timeout is 3600min more than enough to pull all the data I need.
JobMaterial Query is not working instead. 2 issues has been highlighted:
1. Token value cannot be converted.
2. Formula.Firewall issue on apiresponse say 'JobMaterials' query reference to other queries.
Thanks again for your help!!
Avaiable to Team call in case it can help to tackle the issue faster.
Giovanni
- kushanNa
okay maybe you will need to pass the token as a function then , try to created the following function and query
open blank query and copy past , name the function as GetTokenNew
() as text => let // ----------------------------- // ECI OAuth Token Request // ----------------------------- client_id = "YOUR_CLIENT_ID", client_secret = "YOUR_CLIENT_SECRET", token_url = "https://api-user.integrations.ecimanufacturing.com/oauth2/api-user/token", tokenBody = "grant_type=client_credentials" & "&scope=openid" & "&client_id=" & client_id & "&client_secret=" & client_secret, tokenResponse = Json.Document( Web.Contents( token_url, [ Headers = [ #"Content-Type" = "application/x-www-form-urlencoded", Accept = "application/json" ], Content = Text.ToBinary(tokenBody) ] ) ), // Extract raw token access_token = tokenResponse[access_token], // Return as "Bearer xxxx" BearerToken = "Bearer " & access_token in BearerTokencreate another query and past
let // Call token function BearerToken = GetTokenNew(), api_url = "https://api-jb2.integrations.ecimanufacturing.com/api/v1/job-materials", apiResponse = Json.Document( Web.Contents( api_url, [ Headers = [ Authorization = BearerToken, Accept = "application/json" ] ] ) ), // Convert list/record into table AsList = if apiResponse is list then apiResponse else if apiResponse is record and Record.HasFields(apiResponse, "data") then apiResponse[data] else {}, TableOut = if List.Count(AsList) > 0 then let tbl = Table.FromList(AsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), firstRecord = tbl{0}[Column1], expanded = Table.ExpandRecordColumn(tbl, "Column1", Record.FieldNames(firstRecord)) in expanded else #table({}, {}) in TableOutbut it's better if you change the get-schedule url(in the first working query) and get the job-materials value in one query first , then try above method
Hi kushanNa ,
Almost there I think.
Everything works until the apiResponse with the error below. Any advice?
Best regards
Giovanni
Hi kushanNa,
It's still required the pages as variable (and if I entry some values it go in error). What I need is a loop from page 1 to the end to pull all the pagination. Hope it make sense.
Giovanni
- kushanNa
Hi Anonymous
Can you try this code and send me a screenshot of the first step you are getting an error?
let // ----------------------------- // 1. OAuth Token Request // ----------------------------- client_id = "YOUR_CLIENT_ID", client_secret = "YOUR_CLIENT_SECRET", token_url = "https://api-user.integrations.ecimanufacturing.com/oauth2/api-user/token", tokenResponse = Json.Document( Web.Contents( token_url, [ Headers = [ #"Content-Type" = "application/x-www-form-urlencoded", Accept = "application/json" ], Content = Text.ToBinary( "grant_type=client_credentials" & "&scope=openid" & "&client_id=" & client_id & "&client_secret=" & client_secret ) ] ) ), access_token = tokenResponse[access_token], // ----------------------------- // 2. Paging Function (calls API) // ----------------------------- PageSize = 200, GetPage = (Skip as number) => let url = "https://api-jb2.integrations.ecimanufacturing.com/api/v1/job-materials" & "?take=" & Number.ToText(PageSize) & "&skip=" & Number.ToText(Skip), response = Json.Document( Web.Contents( url, [ Headers = [ Authorization = "Bearer " & access_token, Accept = "application/json" ] ] ) ), items = if response is list then response else if response is record and Record.HasFields(response, "data") then response[data] else {} in items, // ----------------------------- // 3. Generate list of pages until empty // ----------------------------- Pages = List.Generate( () => [Skip = 0, Data = GetPage(0)], each List.Count([Data]) > 0, each [Skip = [Skip] + PageSize, Data = GetPage([Skip] + PageSize)], each [Data] ), AllItems = List.Combine(Pages), // ----------------------------- // 4. Convert to Table // ----------------------------- Output = if List.Count(AllItems) = 0 then #table({}, {}) else let tbl = Table.FromList(AllItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error), expanded = Table.ExpandRecordColumn(tbl, "Column1", Record.FieldNames(tbl{0}[Column1])) in expanded in OutputThe function is in there not for us to enter data manually actually it's in there to add data automatically .
Hi, kushanNa
First step as blocker is Get Page step: is asking a parameter: as I said before an anutomatic loop would be better.
Even If I enter a parameter (I tried 1) the invoked function created end in error.
As I said before if it could help I'm avaiable to a Team session. Just let me know.