Configuration of Oracle Database and APEX application – use of Google API Client Library for JavaScript. The ‘Google Workspace friendly application’ series.

18 grudnia 2020 Wojciech Sowa

The ‘Google Workspace friendly application’ series – table of contents

  1. What is Google Workspace, and why should we integrate with it?
  2. Project configuration in Google API Console – use of Google API Client Library for JavaScript – Part I.
  3. Project configuration in Google API Console – use of Google API Client Library for JavaScript – Part II.
  4. Configuration of Oracle Database and APEX application – use of Google API Client Library for JavaScript.
  5. Project configuration in Google API Console – use of Google Service Account.
  6. Configuration of Oracle Database and APEX application – use of Google Service Account.

Introduction

We can start the configuration of the Oracle Database and APEX application. By this time, we created a Google API project with configured Consent Screen, and Google Drive API enabled. In this article, we will add an entry to the ACL access list with the address to Google API. Then we will create an Oracle Wallet, where we will put the certificate responsible for communication with Google API via HTTPS. By the way, I will mention a crucial detail that should be remembered when creating a wallet.

Step by step guide

The first step will be to add a new permission to the ACL list, which will associate the database user 'APEX_200200′ (for APEX 20.2 version) with the address of the Google API service. However, you can type the address of the specific API. The following code creates a separate ACL, but you can use an existing ACL. Pay attention to the address format in the v_acl_host variable – it has no HTTP(s) and www prefixes.

Create an Oracle Wallet or use an existing one to add a certificate responsible for HTTPS communication with Google services. Activate the 'Auto Login' feature at the configuration stage. You can download the proper certificate directly from your browser or visit https://pki.goog/repository/.

Warning! After creating the wallet, ensure that the system user ‘oracle’ has access to the wallet files. Otherwise, Google API calls from PL/SQL will raise an exception!

Let’s analyze the google-api-client.js script code. The code was inspired by examples provided in Google documentation and on Google github. This script contains all functions needed for the initialization of the library. Remember that the behavior of your APEX application, as well as the code of the sample script presented in this article, can be freely modified and adapted to the characteristics of your application. The most important function is called 'updateSigninStatus()' and determines how Access Token is handled in your APEX application. You can store the token wherever you want.

Modify the google-api-client.js script. Fill in API_KEY, CLIENT_ID, APP_ID, SCOPES constants. These constants' values can be found in our project in the Google API Console and the Google API documentation.

It is important to mention a fundamental matter – in the implementation presented, the user’s login status in Google is maintained in the application (see documentation). In other words, our APEX application will ask you to log in to Google only the first time you log in, or when this status expires, or when you clear your browser data. In other cases, authentication with Google is invisible to the user, and we have a valid Access Token available.

Side topic – refreshing Access Token

When communication with Google API is feasible, one important thing stays not addressed – how to refresh the Access Token after 3600 seconds, which is a standard period when it is valid. Let’s analyze this problem and answer how to solve it:

  • According to the official documentation of the library, it performs caching and refreshing tokens in the background. If you want to get the current Access Token, just call a library function:
  • Still, you can adjust how your application will identify logged user and, for example, display the Consent Screen to pick the right Google account again – see the documentation.
  • However, if you want to force refreshing the Access Token, it is possible by calling another library function (and then call getToken() function to get the latest Access Token):

Sample usage

Now it is time to prepare a test application. JavaScript code listed above assumes that on Page 0, we have the region with 'page-0-google-region' Static ID.

Upload the modified script in our application as a Static Application File. Copy the reference to the script to the clipboard.

On the APEX application page responsible for integrating with Google API, in the JavaScript -> File URLs section, paste the reference from the previous step, and before it, paste the link to the Google library – https://apis.google.com/js/api.js.

On the APEX page, create a button called ‘Google Sign In’, then add the dynamic action to it of type 'Execute Javascript Code' with calling the 'signIn()' function. At the page level, in the JavaScript -> 'Execute when Page Loads' section, put the call to the 'loadGapiLibraries()' function.

At the page level, in the JavaScript -> 'Execute when Page Loads' section, put the call to the 'loadGapiLibraries()' function.

Create an APEX Item called P0_GOOGLE_API_TOKEN, which will store the current Access Token. It should be of type Hidden with no value protection. Of course, as it was mentioned before, the method of keeping a token is entirely up to you.

Launch the application. You should see the region responsible for connecting to your Google account, in which we placed our button.

After clicking, you should see Consent Screen, where you can select your Google account. Pick the account associated with the domain in which the Google API project was created.

After correct verification, Consent Screen asks us to allow Google API to manage data in our account by the scope of access rights defined in the SCOPES constant.

After accepting, the region should disappear, and in the APEX session, you should have a current Access Token saved, which will be valid for the next hour. Each re-entry of the home page will result in the authentication being performed again, this time invisible to the user – see the documentation.

Having a valid Access Token, you can start integrating our APEX application with the Google API selected in your project in Google API Console. In my case, the Access Token is stored in a JavaScript variable OAUTHTOKEN and an APEX item, so you have to reference it in your code or select another way of keeping the current token in the session. A sample call from PL/SQL looks like this:

And from JavaScript using AJAX:

All configuration activities are already finished – you can freely use the extensive possibilities of Google API!

This article closes the topic of integrating APEX with Google Workspace using Google API Client Library for JavaScript. But, still, we have another integration method to be discussed. It will be related to Service Account, allowing us to integrate with Google API on the back-end side fully. Besides, we won’t need the user to log in to the application. Stay tuned!

Tagi: , , , ,

Zapraszamy do kontaktu!

Pretius jest firmą tworząca oprogramowanie wspierające biznes.
Tworzymy aplikacje webowe wykorzystując: Java, Oracle DB, Oracle Apex, AngularJS.
Skontaktuj się z nami, aby porozmawiać o tym jak możemy pomóc w realizacji Twojego projektu!