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