Forum Discussion
Calling the Indeed API in PowerBI using Power Query M
* I will preface this post by saying I am very much a novice when it comes to Power query M*
Hello,
I have been racking my head trying to get this work for the better part of a week. I am trying to generate an access token in PowerBI from Indeed and calling this specific API, which uses O-auth 2.
I have already successfully generated an access token and called the API mentioned above in POSTMAN. I have pasted it below (in HTTP format), but omitted the "sensitive" information:
Generating the Access Token:
POST /oauth/v2/tokens HTTP/1.1
Host: apis.indeed.com
Content-Type: application/x-www-form-urlencoded
Accept: application/json
Cookie: SURF=HibVklQ4yZY24rS0AuOWqlIwnDJqRHG1; CTK=1g2ru0havptte801
Content-Length: 205
client_id="client_ID"&client_secret="client_secret"&grant_type=client_credentials&scope=employer_access
Calling the API I specified:
GET /ads/v1/campaigns/eb2ce79c18b45740/stats?startDate=2022-05-01&endDate=2022-05-09&merge=FALSE HTTP/1.1
Host: apis.indeed.com
Content-Type: application/x-www-form-urlencoded
Accept: application/json
Cookie: SURF=HibVklQ4yZY24rS0AuOWqlIwnDJqRHG1; CTK=1g2ru0havptte801
Content-Length: 279
client_id="client_id"&client_secret="client_secret&grant_type=client_credentials&scope=employer_access%20employer.advertising.campaign&employer=912e79a21b66f29151f06b0dba3bb07d
As I said, both of these are working fine in POSTMAN. Transferring them into PowerBI is where I am having an issue. Is there any way to easily translate HTTP to M?
I have been using this sample Pseudo code below:
let
AccessToken = Json.Document(Web.Contents("https://apis.indeed.com/oauth/v2/tokens",
[
Headers=[Accept="application/json", ContentType="application/x-www-form-urlencoded"],
Content=Text.ToBinary(
"grant_type=client_credentials&
client_id=CLIENT_ID&
client_secret=CLIENT_SECRET&
scope=ACCESS_SCOPE"
)
]))[access_token],
JsonData = Json.Document(Web.Contents("https://apis.indeed.com/ads/v1/campaigns/{campaignId}/stats",
[Headers=[Accept="application/json",#"Authorization"="Bearer " & AccessToken]])),
// Use the data
#"Converted to Table" = Table.FromList(JsonData, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
When I add in my values to this code, I am receiving the following error:
I am honestly stumped and would love some advice. The code above could have some tiny mistakes or be completely wrong. Any help is appreciated! Thank you!
6 Replies
- lbendlinSuper User
Content=Text.ToBinary( "grant_type=client_credentials& client_id=CLIENT_ID& client_secret=CLIENT_SECRET& scope=ACCESS_SCOPE" )needs to be rewritten as JSON with escaping of the double quotes. Curly brackets, colons, commas etc.
- hussaintmFrequent Visitor
Thanks for your response!
I have gone ahead and translated that portion into JSON. I think I have done it correctly, but I am still getting the same error as before. Please take a look at my code and let me know if you see something wrong.
FYI, all my fields are being stored as variables.Also, in POSTMAN, when I add the keys to the "body", I have "x-www-form-urlencoded" selected. Not sure if I am representing that correctly in my M code as all my variables are simply stored as strings.
let body = "{ ""client_id"": " & Client_ID & ", ""client_secret"": " & Secret_ID & ", ""grant_type"": " & grant_type & ", ""scope"": " & scope & " }", AccessToken = Json.Document(Web.Contents("https://apis.indeed.com/oauth/v2/tokens", [ Headers=[Accept="application/json", ContentType="application/x-www-form-urlencoded"], Content=Text.ToBinary(body) ]))[access_token], JsonData = Json.Document(Web.Contents("https://apis.indeed.com/ads/v1/campaigns/{campaignId}/stats", [Headers=[Accept="application/json",#"Authorization"="Bearer " & AccessToken]])), // Use the data #"Converted to Table" = Table.FromList(JsonData, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in JsonData- lbendlinSuper User
You are getting there. You need a couple more double double quotes around your data fields.