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
.png "Former Employee"){kind=icon}
