REST CONNECTOR locate the Authorization token

[#1]

I am trying to connect to google analytics by following this guide http://help.qlik.com/Connectors/en-US/connectors/#../Subsystems/REST_connector_help/Content/1.0/Crea...

But I get stuck on step 7. Open Developer tools in Google Chrome and locate the Authorization token

How do i locate the token?

[Bjorn_Wedbratt · Accepted solution]

Hi Karl,

Sorry about the delay in responding. I've been playing around a bit with this in Qlik Sense as well, and I managed to alter the parameters for the connection using WITH CONNECTION, in a similar way as above. This worked even in Legacy mode (which was a surprise to me).

There is one big hurdle though and that is when creating Data connections; the connection must be successful or you cannot save the settings. Even when pressing "Save" the connection is checked and if it fails settings won't be saved.

When working with APIs you cannot hardcode parameters in the URL when setting up the connection, basically because not all parameters may be known at this stage.

To get around this I simply created two connections using the REST Connector, one for GET (LIB: Google Analytics API) and one for POST (LIB: Google Authorization) and used an open JSON server at jsonplaceholder.typicode.com wheen creating the connections (you cannot alter the Method GET/POST using WITH CONNECTION). I then followed the same process as above, and used WITH CONNECTION to alter the LIB settings and pointed the URL to the entry-points for Google APIs. Here's a small example script for Sense:

IF vTokenExpires <= now() THEN
  LET vRequestBody ='';
  LET vRequestBody = vRequestBody & 'grant_type=refresh_token';
  LET vRequestBody = vRequestBody & '&client_id=' & '$(vClient_id)';
  LET vRequestBody = vRequestBody & '&client_secret=' & '$(vClient_secret)';
  LET vRequestBody = vRequestBody & '&refresh_token=' & '$(vRefresh_token)';

  LIB CONNECT TO 'Google Authorization';

  access_token:
  SQL SELECT
  "access_token",
  "token_type",
  "expires_in"
  FROM JSON (wrap on) "root"
    WITH CONNECTION (
    URL "https://www.googleapis.com/oauth2/v4/token",
    HTTPHEADER "Content-Type" "application/x-www-form-urlencoded",
  BODY "$(vRequestBody)"
  );
  LET vExpiresIn = peek('expires_in',0,'access_token');
  LET vAccessToken = peek('access_token',0,'access_token');
  LET vTokenExpires = timestamp(now() + $(vExpiresIn)/86400);



ENDIF  

Next step would be to dynamically render the URL to query Google Analytics, using variables. Not done here yet, so will get back to you with that part.

[#3]

Hi Björn,

Thanks for the response. This is what I have done so far.

1. I have created a OAuth Client credential.
2. I added the playground URI to the Authorized redirect URIs.
3. I configured the OAuth2 playground to use own OAuth credentials and set to Offline.
4. I Authorized the Analytics API
5. Created a refresh token

But I get this Error message when I try to use the refresh token in Qlik Sense.

'Bearer' authentication schema provided by the web-service is not supported or your credentials are not valid.Try using the 'Force basic authentication' connection option if the server accepts the OAuth authentication schema.

What Authorized redirect URIs should I set when using this from Qlik Sense server?

[Bjorn_Wedbratt]

Ahh, Try and use a Custom OAuth endpoint instead of Google in OAuth2 Playground when setting up custom credentials. That will support a bearer token for the credentials.

• In the OAuth 2.0 configuration, select OAuth endpoints: Custom
• Fill in the following values:
  • Authorization endpoint: https://accounts.google.com/o/oauth2/auth
  • Token endpoint: https://www.googleapis.com/oauth2/v4/token
  • OAuth Client ID: <the Client ID from Google Developer Console)
  • OAuth Client secret: <the Client secret for the OAuth client, found in Developer Console)
• Press Close to save Oauth 2.0 configuration

See if that works

[#5]

I did that in point 3.

What I missed was a simple typo. A space character got copied as the client-id.

Now it works.

I looked in the documentation but I can't find anything about WITH CONNECTION. It's a powerful command.

Thank you very much Björn for your time and all the help.

[Bjorn_Wedbratt]

Anytime, Karl and great you sorted it out (those typo's they drive you mad sometimes ).

Btw, WITH CONNECTION is documented on http://help.qlik.com/Connectors/en-us/connectors/#../Subsystems/REST_connector_help/Content/1.0/Load...

At least part of it, but BODY is missing as a parameter though which has been reported.

[#7]

Hi again,

I gotten this far, but I having trouble pivoting the result into a straight table.

SET vStartDate = '2015-01-01';  
SET vEndDate = '2015-01-02';  
SET vMetrics = 'ga:transactions';  
SET vDimensions = 'ga:date,ga:transactionId';  

  LET vRequestBody ='';  
  LET vRequestBody = vRequestBody & 'ids=ga:XXXXXXX';  
  LET vRequestBody = vRequestBody & '&start-date=' & '$(vStartDate)';  
  LET vRequestBody = vRequestBody & '&end-date=' & '$(vEndDate)';  
  LET vRequestBody = vRequestBody & '&metrics=' & '$(vMetrics)';  
  LET vRequestBody = vRequestBody & '&dimensions=' & '$(vDimensions)';  

 LET vURL  = 'https://www.googleapis.com/analytics/v3/data/ga?' & '$(vRequestBody)';

LIB CONNECT TO 'Google Analytics API';

RestConnectorMasterTable:

SQL SELECT
  "ga:date",
  "ga:transactionId"
FROM JSON (wrap off) "rows"
    WITH CONNECTION (  
    URL "$(vURL)",  
    HTTPHEADER "Authorization" "Bearer $(vAccessToken)"
  );  

SQL SELECT

  "ga:date",

  "ga:transactionId"

FROM JSON (wrap off) "rows"

This doesn't work and if I use the default load script I get it all in different tables. I thought about using Generic load but there must be an easier way to do this.

Do you have any snippets you can share?

[#8]

Hi,

I am facing the same problem Auth 2.0 for linkedin. while trying to test connection i get error message HTTP protocol error 401 (Unauthorized) requested resource requires authorization.

[#9]

I got it working with a Generic load and a merge of all the tables. But it really slows down exponentially when the number of dimensions is increased. 4 works fine for a small data set, but over that it get's really slow.

[#10]

I fixed it just by looking at the resulting table and realized I needed to load distinct __FK_rows_u0 to avoid a cross join

[columnHeaders]:
LOAD    Mid([name],3) AS [name],
         RowNo() AS RowNumber
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_columnHeaders]);


[rows]:
LOAD    [@Value_u0] AS [Value],
     [__FK_rows_u0],
  if([__FK_rows_u0]=peek([__FK_rows_u0]),peek(RowNumber)+1,1) as RowNumber
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_rows_u0]);

LEFT JOIN (rows)
LOAD
    name,
    RowNumber    
RESIDENT columnHeaders
;

DROP TABLE RestConnectorMasterTable;
 DROP TABLE columnHeaders;

Flags:
Generic
LOAD
    [__FK_rows_u0],
    name,
    Value
RESIDENT rows;

DROP TABLE access_token;

MergeTable:
LOAD distinct [__FK_rows_u0]
RESIDENT rows;

DROP TABLE rows;

FOR i = NoOfTables()-1 to 0 STEP -1 
  LET vTable=TableName($(i)); 
  IF WildMatch('$(vTable)', 'Flags.*') THEN 
    LEFT JOIN (MergeTable) LOAD * RESIDENT    [$(vTable)]; 
    DROP TABLE  [$(vTable)]; 
  ENDIF 
NEXT i

DROP FIELD [__FK_rows_u0];

[lars_plenge]

Hi

Is there a max lenth for the "with connection"?

WITH CONNECTION ( 

URL "$(vURL)",

  HTTPHEADER  "Authorization" "Bearer $(vAccessToken)" 

);

this is working:

LET vURL ='https://www.googleapis.com/analytics/v3/data/ga?ids=ga%3A98567028';

LET vURL = vURL & '&start-date='& date(now(),'YYYY-MM-DD')&'&end-date='& date(now(),'YYYY-MM-DD')&'&metrics=ga%3Atransactions';

This is NOT working:  --- but it is working if is inside the connector string.

vURL ='https://www.googleapis.com/analytics/v3/data/ga?ids%2ga%%3A98567028&start-date%22016-02-02&end-date%...