Integration of Google API into Qlik Sense data connection

[Darren1]

We are attempting to pull in the Google Enterprise licensing API and create a data connection in Qlik Sense Enterprise for Windows. Our goal is to visualize the Google Enterprise license usage in a Qlik Sense Dashboard. Can anyone provide the necessary steps for this connection?

Currently we have Qlik Web Connectors service running using the instructions found here however this is still a work in progress. The Qlik Web Connectors service is related to an effort to create a Google Drive & Spreadsheets connector. I am not sure if there is any overlap with Google Enterprise licensing API connector and creating the Google Drive & Spreadsheets connector. But I thought it was worth mentioning

Thanks in advance for any assistance

@jbchurchill 

[AustinSpivey]

Hey Darren and JB!

Here's how I would do it:

Step 1: Create a new credential in Google Cloud

Follow step 1 in this guide on using Postman to make authenticated OAuth calls to the Google APIs. You should use Postman to make this whole process way easier, as it is a API testing tool with an easy-to-use interface.

The above guide starts off with creating a project in Google Cloud -- you probably can use an existing one, hopefully your team has some foresight into that. Once an existing project is chosen or a new one is created, you should select and enable the requisite API product. The guide uses the Google Sheets API for its example, however you will want to find and enable the Enterprise License Manager API:

 

Follow step 2 of the guide. When it gets to the part about adding scopes, find the Manually add scopes section and then add this scope:

https://www.googleapis.com/auth/apps.licensing

It should look like this:

Finish step 2 of the guide. Once you get to step 3, jump ahead to the part where it tells you to "navigate to the Authorization tab in your Postman account and select OAuth 2.0 as your authorization type."

Remember to use https://www.googleapis.com/auth/apps.licensing as the scope for that step!

Also, make sure to follow the note in this section:

As it directs, make sure to use this as your Auth URL:

https://accounts.google.com/o/oauth2/v2/auth?access_type=offline

Once you follow all of the step 3, you should now have your token (which should start with something like ya29 or whatever). NOTE:  you will also need the provided Refresh Token as well later on, so you should keep both on hand.

Step 2: Create the Enterprise License Manager API data connection in Qlik

At this point, we can now create our data connection in Qlik. Rather than using any of the out-of-the-box Google connectors, we'll actually just use the REST connector to achieve this. Here's how to set it up:

• URL: https://licensing.googleapis.com/apps/licensing/v1/product/Google-Apps/users
• Certificate validation: Skip Server Certificate Validation
• Query parameters:
  • customerId | *Put your Google customer ID here. Here's a help page if you don't know what it is.*
• Query headers:
  • Authorization | Bearer *your token here*
  • Accept | application/json
• ☑️ Add missing query parameters to final request
• Security
  • ☑️ Allow HTTPS only

That URL above uses /Google-Apps/ but you can use any of the licensed products found on this help page.

Name it something appropriate and then save.

Now when you load it, you should be able to see those license assignments:

Step 3: Add script for getting new refreshing the access tokens

We were able to use Postman to get our initial access token in step 1 of this post, but you will need to account for that token, and future ones, expiring after about an hour. Here's some Qlik script that you can use to achieve this:

// ===================================================================
//  GET NEW TOKEN
// ===================================================================

SUB GetGoogleToken
    LIB CONNECT TO 'Google Refresh Token';

    [Refresh Token Response]:
    SQL SELECT 
        "access_token",
        "expires_in",
        "scope",
        "token_type"
    FROM JSON (wrap on) "root";

    [Refresh Token]:
    LOAD
        Now() as [token_acquired_ts],
        [access_token],
        [expires_in],
        [scope],
        [token_type]
    RESIDENT [Refresh Token Response];
    DROP TABLE [Refresh Token Response];
    
    Store [Refresh Token] into 'lib://DataFiles/google_token.qvd'(QVD);
    Let vToken = peek('access_token', 0, 'Refresh Token');
    Drop Table [Refresh Token];
END SUB


// ===================================================================
//  TOKEN CHECK
// ===================================================================

SUB TokenCheck
    Let vNow = Now();
    Let vQVD_ts = Coalesce(timestamp#( QvdCreateTime('lib://DataFiles/google_token.qvd')  + (60/60/24) - ((60/60/24/60/60)*5) ), 0);
    Let vCompare = timestamp('$(vNow)') > timestamp('$(vQVD_ts)');
    
    IF timestamp(Now()) > timestamp(Coalesce( QvdCreateTime('lib://DataFiles/google_token.qvd')  + (60/60/24) - ((60/60/24/60/60)*5), 0) ) THEN
        Trace #### Graph token expired...getting new token now.;
        Call GetGoogleToken
    ELSE
        Trace #### Graph token still good.;
        
        [Access Token]: LOAD [access_token] FROM 'lib://DataFiles/google_token.qvd'(QVD);
        Let vToken = peek('access_token', 0, 'Access Token');
        Drop Table [Access Token];
    END IF
END SUB

In order for this script to work, you will need to create another REST connection in Qlik, like so:

• URL: https://oauth2.googleapis.com/token
• Method: POST
• Request body:

client_id=whatever-your-client-id-is.apps.googleusercontent.com&client_secret=whatever-your-client-secret-is&refresh_token=whatever-your-refresh-token-is&grant_type=refresh_token

• Query headers:
  • Content-Type | application/x-www-form-urlencoded
• Security
  • ☑️ Allow HTTPS only

This should result in a new access token that Qlik can use to continue getting the Google licensing info, at least until the refresh token itself expires (not sure what the default it, that may be set by organization policy).

Notes about the Qlik script

That script has been tested and logically works, but my implementation may be a bit tortured or lazy in some ways, just an FYI. It uses Subroutines to define reusable procedures called GetGoogleToken and TokenCheck (if you're unfamiliar with subroutines, they're basically like functions you write in Qlik). You can use those subroutines like this:

CALL TokenCheck;

LIB CONNECT TO 'Google Licensing 1';

RestConnectorMasterTable:
SQL SELECT 
	"kind" AS "kind_u0",
	"etag",
	"__KEY_root",
	(SELECT 
		"kind",
		"etags",
		"productId",
		"userId",
		"selfLink",
		"skuId",
		"skuName",
		"productName",
		"__FK_items"
	FROM "items" FK "__FK_items")
FROM JSON (wrap on) "root" PK "__KEY_root"

  WITH CONNECTION (
    HTTPHEADER "Authorization" "Bearer $(vToken)"
);

[items]:
LOAD
	[kind],
	[etags],
	[productId],
	[userId],
	[selfLink],
	[skuId],
	[skuName],
	[productName],
	[__FK_items] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
  WHERE NOT IsNull([__FK_items])
;

[root]:
LOAD
	[kind_u0] AS [kind_u0],
	[etag],
	[__KEY_root]
RESIDENT RestConnectorMasterTable
  WHERE NOT IsNull([__KEY_root])
;

DROP TABLE RestConnectorMasterTable;

The script above will:

• Call the TokenCheck subroutine, which will get the token that we have stored to a QVD and check to see if it's expired -- if it is expired, it calls the GetGoogleToken subroutine to go refresh that token and get a new one, which gets stored back to that same QVD. It will then set that token value to a variable called vToken. If, however, the token in the QVD is not expired, it will simply set it to the vToken variable.
• Now that we have our token, we use the WITH CONNECTION command to dynamically update our Authorization header with our unexpired token using the vToken variable.

Further enhancements to this process

You could take this all a step further by looping through the list of Google products to get the license use for all of your relevant products.