Forum Discussion

hussaintm's avatar
hussaintm
Frequent Visitor
4 years ago

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

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

    • hussaintm's avatar
      hussaintm
      Frequent 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

       

      • lbendlin's avatar
        lbendlin
        Super User

        You are getting there. You need a couple more double double quotes around your data fields.