topic REST CONNECTOR locate the Authorization token in Connectivity & Data Prep

REST CONNECTOR locate the Authorization token

Tue, 21 Dec 2021 13:58:50 GMT

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/Create-REST-connection/C…

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

How do i locate the token?

Re: REST CONNECTOR locate the Authorization token

hemhund2016 — Thu, 03 Dec 2015 16:47:54 GMT

Hi,

Which browser you are using?

Re: REST CONNECTOR locate the Authorization token

Thu, 03 Dec 2015 19:14:54 GMT

‌chrome and firefox.

Re: REST CONNECTOR locate the Authorization token

Mon, 21 Dec 2015 09:02:02 GMT

I have the exact same problem. Can anyone please answer to this question?

Re: REST CONNECTOR locate the Authorization token

Maria_Halley — Mon, 11 Jan 2016 13:42:32 GMT

After reading a lot of googles help on this it seems like you have to use OAuth 2. Here you get a token that are valid for a certain time and then expires.

The token is generated by google on request when asked for by the client.

I can generate a token from the OAuth Playground but I can't get that to work using the rest connector in Qlik View.

Is it still possible to connect to google APIs?

Re: REST CONNECTOR locate the Authorization token

Bjorn_Wedbratt — Wed, 27 Jan 2016 09:31:36 GMT

Hi Maria,

That's correct, you need to use OAuth2 and get an access token to access google APIs. One can use OAuth 2.0 Playground as mentioned above to request an access token, together with a refresh token.

For a full understanding of he OAuth2 process there is great documentation at Using OAuth 2.0 to Access Google APIs | Google Identity Platform | Google Developers

Now, there are some challenges with some of the steps in the process when using the REST Connector, although the playground will get you started.

One is how to deal with the refresh token. As you pointed out, the access token will expire after 3600 seconds and needs to be renewed. To request a new access token, basically a POST request needs to be sent to https://www.googleapis.com/oauth2/v4/token including:

refresh_token
client_id
client_secret
grant_type (with value = refresh_token)

This will return a new access token with an expire time:

{

"access_token":"1/fFBGRNJru1FQd44AzqT3Zg",

"expires_in":3920,

"token_type":"Bearer",

}

A script in QlikView using the REST Connector to perform the above could look like:

SET vClient_id = 'your client id';
SET vClient_secret = 'your client secret';
SET vRefresh_token = 'your refresh token from OAuth playground';


IF vTokenExpires <= now() THEN // if access_token expired request a new one using the refresh_token

  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)';

  CUSTOM CONNECT TO "Provider=QvRestConnector.exe;
  url=https://www.googleapis.com/oauth2/v4/token;
  timeout=30;method=POST;
  autoDetectResponseType=0;
  keyGenerationStrategy=0;
  useWindowsAuthentication=false;useCertificate=No;certificateStoreLocation=CurrentUser;
  certificateStoreName=My;
  queryHeaders=Content-Type%2application/x-www-form-urlencoded;
  PaginationType=None;XUserId=IWRHaYA;XPassword=dAXccDB;";

  access_token:
  SQL SELECT 
  "token_type",
  "access_token",
  "expires_in"
  FROM JSON (wrap on) "root"
  WITH CONNECTION (
  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

You know have a script the checks expires_in to see if the access_token is still valid, if not a new token is requested.

According to Google documentation, the access token can be used to make calls to Google API on behalf of a user or service account. To do this the access token should be included in the request as an Authorization: Bearer HTTP header.

To use our refreshed access token when making calls to Google API, we need to include it in the request. This can be achieved using the WITH CONNECT statement in the SELECT statement generated by the REST CONNECTOR. As we have our access token in a variable (vAccessToken) from above, we can inject this using a script similar to:

RestConnectorMasterTable:
SQL SELECT 
  "kind" AS "kind_u0",
  "username",
  "totalResults",
  "startIndex",
  "itemsPerPage",
  "__KEY_root",
  (SELECT 
  "id",
  "kind",
  "selfLink",
  "name",
  "created",
  "updated",
  "__KEY_items",
  "__FK_items",
  (SELECT 
  "__KEY_permissions",
  "__FK_permissions",
  (SELECT 
  "@Value",
  "__FK_effective"
  FROM "effective" FK "__FK_effective" ArrayValueAlias "@Value")
  FROM "permissions" PK "__KEY_permissions" FK "__FK_permissions"),
  (SELECT 
  "type",
  "href",
  "__FK_childLink"
  FROM "childLink" FK "__FK_childLink")
  FROM "items" PK "__KEY_items" FK "__FK_items")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (
  HTTPHEADER "Authorization" "Bearer $(vAccessToken)"
);

Hope the above helps on how to use the access token and refresh token when connecting to Google APIs

Re: REST CONNECTOR locate the Authorization token

Wed, 27 Jan 2016 10:27:37 GMT

Great post Björn,

The custom connect approach doesn't seem to be implemented in Qlik Sense. "CONNECTs other than LIB CONNECT are not available in this script mode"

I got it working in Qlik Sense, using the OAuth2 Playground before, but I wasn't able to set the parameters in the script so I could change the period I requested so I stopped trying. I needed to change the parameters manually in the connection, so using a schedule task didn't work.

Any ideas?

Vänta på funktioner att bli implementerade är Qlik Senses användarens vardag.

Re: REST CONNECTOR locate the Authorization token

Wed, 27 Jan 2016 10:35:28 GMT

Legacy mode is not an option, since I am running Qlik Sense Server

Re: REST CONNECTOR locate the Authorization token

chrisbrain — Wed, 27 Jan 2016 10:42:49 GMT

Is Legacy mode not available on Qlik Sense server? I thought it was.

Re: REST CONNECTOR locate the Authorization token

Wed, 27 Jan 2016 10:47:37 GMT

I don't want to enable it since the security implications.

Re: REST CONNECTOR locate the Authorization token

Bjorn_Wedbratt — Wed, 03 Feb 2016 08:20:59 GMT

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.

Re: REST CONNECTOR locate the Authorization token

Wed, 03 Feb 2016 10:08:44 GMT

Hi Björn,

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

I have created a OAuth Client credential.
I added the playground URI to the Authorized redirect URIs.
I configured the OAuth2 playground to use own OAuth credentials and set to Offline.
I Authorized the Analytics API
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?

Re: REST CONNECTOR locate the Authorization token

Bjorn_Wedbratt — Wed, 03 Feb 2016 14:22:06 GMT

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

Re: REST CONNECTOR locate the Authorization token

Wed, 03 Feb 2016 16:02:52 GMT

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.

Re: REST CONNECTOR locate the Authorization token

Bjorn_Wedbratt — Wed, 03 Feb 2016 16:06:00 GMT

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-REST-data/Select-RE…

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

Re: REST CONNECTOR locate the Authorization token

Thu, 04 Feb 2016 13:27:12 GMT

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?

Re: REST CONNECTOR locate the Authorization token

Thu, 04 Feb 2016 15:31:59 GMT

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.

Re: REST CONNECTOR locate the Authorization token

Thu, 04 Feb 2016 16:45:08 GMT

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.

Re: REST CONNECTOR locate the Authorization token

Fri, 05 Feb 2016 16:12:04 GMT

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];

Re: REST CONNECTOR locate the Authorization token

lars_plenge — Wed, 10 Feb 2016 11:09:38 GMT

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%22016-02-09&metrics%2ga%%3AtransactionRevenue&dimensions%2ga%%3Amedium%%2C@ga%%3Acampaign&filters%2ga%%3Amedium%%3D%%3Demail&sort%2-ga%%3AtransactionRevenue';