Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with Authorization token in Google Analytics

Hi!

I'm trying to connect to Google Analytics, but I've got problem with the Authorization token in Developer tools. 

1. https://developers.google.com/apis-explorer/#s/analytics/v3/  -> I generated my ID in analytics.management.accounts.list

2. I used it in analytics.data.realtime.get

3. I received a link,, I typed it in the field query and value and test connection was OK.

4.  I wanted to repeat the test, but then I got an error of expired token.

5.  I know that tokens can be generated again in Credentials in Google Developers Consols, but I tried to make a variety of ways and it doesn't works.

What should I do to have not to change the token each time I connect?

How can I generate a token like this?

10 Replies
Bjorn_Wedbratt
Former Employee
Former Employee

Hi Jan,

Working with Google APIs could be quite tricky and requires one to understand the process for granting access to applications accessing the data.

First step is to authorize access which needs to manually be approved (step one in OAuth 2.0 Playground ).

Once authorization is approved you will retrieve an Authorization code.

This code can be exchanged for an Access Token together with a Refresh Token. (step 2). Note: the Authorization code is only valid once and cannot be reused!

The Access token is only valid for a limited time (3600 seconds) and needs to be renewed after that time period. This is where the Refresh Token comes into play. By sending a request to https://www.googleapis.com/oauth2/v4/token including the Refresh Token, a new Access Token can be requested (which again will expire after 3600 seconds and the process needs to be repeated).

The Refresh Token does not expire (unless the user revokes authorization) and can be reused to request new Access Tokens, so it's essential to take a note on this when authorizing access.

Here's an example script on how to renew the Access Token if expired (requires you to have the Refresh Token available, together with Client Id and Client Secret):

SET vClient_id = '';

SET vClient_secret = '';

SET vRefresh_token = '';

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

The new Access Token will be available in the variable vAccessToken.

Now, to include the token when connecting to the Google API, one can use WITH CONNECT in the SELECT statement generated by the REST Connector to alter the HTTP header for Authorization and inject the token from the variable:

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"

// ADD WITH CONNECT STATEMENT TO INJECT access_token

WITH CONNECTION (

  HTTPHEADER "Authorization" "Bearer $(vAccessToken)"

)

;

Hope this helps

Best,

Bjorn

Not applicable
Author

Hi Bjorn,

You mention retrieving an Authorization code, where would you get this?

Andy

Bjorn_Wedbratt
Former Employee
Former Employee

Hi Andrew,

You request access using Google's OAuth 2.0 endpoint at https://accounts.google.com/o/oauth2/v2/auth which will give you an Authorization code back. This can be done programmatically in various ways using client libraries for PHP, Python, Ruby or crafting the request manually.

You can also use OAuth 2.0 Playground to authorize access to the API and get the Authorization code (it will show in Step 2.):

Untitled.png

Anonymous
Not applicable
Author

Hello,

I've found a way, if you want, you can see here:

http://bit.ly/1PQZ0Wj

Best Regards,

Anonymous
Not applicable
Author

Hello,

I've found a way, if you want, you can see here:

http://bit.ly/1PQZ0Wj

Best Regards,

Azfar
Contributor
Contributor

Hi Bjorn,

Thanks for the steps provided. I've been trying to follow the steps to automate the process but still failed. I want to ask about a part in your script as below

PaginationType=None;XUserId=IWRHaYA;XPassword=dAXccDB;";

What UserId & Password should I provide? Is it the google account or qlik account credentials?

Regards,

Azfar

Bjorn_Wedbratt
Former Employee
Former Employee

Hi @Azfar 

Sorry about the delay, for some reason I didn't get a ping on this post.

About the XUserId;XPassword you can simply ignore/remove them. They got auto generated when I created the connect statement, but are not used when authenticating against google, as you're using Oauth and tokens for authentication/authorization.

Please ping me in private if you need further assistance.

Best,

Bjorn

Azfar
Contributor
Contributor

Hi @Bjorn_Wedbratt ,

I have tried the method that you have suggested. Unfortunately, it does not work. I have sent you a private message asking you few questions. Appreciate if you could look into those questions.

Regards,

Azfar

Luisanabria
Contributor III
Contributor III

Hi, this is a good article, but someone know if this script may work for Business Central Dynamics 365??

 

Because l have the same problem there, token invalid every X time