Introduction

Welcome to my post on integrating an APEX application with Google Drive (or any Google API in general) using a JavaScript approach. If you are asking yourself what kind of problem I am going to solve, please read my introduction post which describes the use case. As my solution is packed in APEX Plug-In, it will be also nice knowledge base about how to create plugin (especially of type Dynamic Action).

As some of you may know, I described Google integration aspects few years ago. Here you can expect sum of my lessons learned, more insights and more advices on what you should be really careful about.

Motivation

Like described in my introduction post, the objective was to find images on Google Drive by parent folder URL passed in APEX page item. Plugin should do all this stuff, and save the results in APEX Collection. It was our definition of done as Steven decided to take care of further processing on PL/SQL side.

By saving results in APEX Collection, I mean adding one member row with JSON array data in CLOB001 column in following format:

{
    "items": [
        {
            "sequenceNumber": "1",
            "id": "someIdOfGoogleDriveElement",
            "name": "1 begin",
            "thumbnailURL": "https://drive.google.com/thumbnail?id=someIdOfGoogleDriveElement&sz=w300"
        }
    ]
}

The idea of creating an APEX Plug-In sounds pretty fair for me. At the end of the day, I want to hide the complexity of the integration from the developers on one hand, but on the other I want to make it easy to copy over pages and look like almost native APEX option.

Prerequisites

In order to authenticate with Google we utilize Google Identity Services JavaScript library. It allows to get valid access token necesary for making API calls. Also, we use this feature every time when existing access token is expired. This is a sign that we need to request another one. Normally, token is valid for one hour (3600 seconds).

Since there is a lot AJAX calls in many places, the JavaScript code is kept in async/await manner for better readability and ease to understand the flow. Also, it gives you an impression that the code you are reading is running one by line really.

Some of you could ask: "Why not to use the Social Sign-In feature in APEX?". Well, the simplest answer is that Steven's application utilizes different Authentication Scheme. But I agree - that's extremely tempting because APEX can handle and manage the expiration of access tokens. In this case, the only thing we need to implement, is just calling the proper API endpoint without pointing to authorization elements anywhere. APEX just does it for you.

const GOOGLE_DRIVE_FILES_URL = "https://www.googleapis.com/drive/v3/files";
const API_KEY = 'MY_API_KEY';
const ORDER_BY = 'orderBy=name';
let PARENT_FOLDER_ID = configObj.folderId;
let FIELDS = "fields=files(id,name)";
let tokenState = getCurrentAccessToken();

let REQUEST_URL = GOOGLE_DRIVE_FILES_URL + "?" + 
                  ORDER_BY + "&" + 
                  "q='" + PARENT_FOLDER_ID + "' in parents and trashed=false" + "&" + 
                  FIELDS + "&" +
                  "key=" + API_KEY;

let googleDriveAjaxResult = await $.ajax({
      headers: {
        'Authorization': tokenState.token_type + ' ' + tokenState.access_token
      },
      url: REQUEST_URL,
      type: 'GET'
    });

Plug-In Structure

There are few assumptions and functionalities that should be available in my Plug-In. It is time to describe them in more detail for better understanding what should be included in such solution.

Render Function

The render PL/SQL function is very short - it uses the APEX_JAVASCRIPT API to run JavaScript function defined in separate script file. Also, the same API creates a const variable storing the Plug-In's AJAX identifier. Now, both properties of T_DYNAMIC_ACTION_RENDER_RESULT are set, which ends the definition of render function.

  apex_javascript.add_inline_code(
    p_code => 'const PLUGIN_AJAX_IDENTIFIER = ' || apex_javascript.add_value(apex_plugin.get_ajax_identifier, false) || ';'
  );  

  l_return.javascript_function := 
  'async function() {await runLookup('|| 
    -- googleDriveFolderURLItem
    apex_javascript.add_value( apex_util.get_session_state(p_dynamic_action.attribute_01), true )  ||
  '); apex.da.resume( this.resumeCallback, false );}';

  l_return.ajax_identifier     := apex_plugin.get_ajax_identifier;

AJAX Function

This function has several responsibilities, because I have identified four different use cases where I need to do some PL/SQL code to be called from JavaScript.

• There are two VARCHAR2 constants keeping the names for APEX collections. First is dedicated for storing data about access token got from Google. Second is responsible for holding the API call results in one of CLOB columns.

• Once I receive the access token needed for the API calls, I decided to save it in APEX Collection. This AJAX call creates a collection (or truncates the existing one) and adds one member with the token data. All in all, this approach allows me to reuse the token got from the request and grab it on any APEX application page. Another positive thing is that I can use the 60 minutes period for which the token is valid - that means I do not have to show the Google account selector every time when application user wants something from Google resources.

• I need to fetch the token data from APEX collection explained above. This is just SELECT from APEX_COLLECTIONS view. As normal, I have to be ready to handle NO_DATA_FOUND exception when no token is stored in collection yet.

• For cleaner code and easier debugging, I have created another PL/SQL code which truncates the APEX collection with API results. To be clear, I don't want to present wrong results or results from the previous API call once the latest one returned an error.

• Last PL/SQL block saves the API results in dedicated APEX collection. And this is the expected Plug-In output for Steven. He just loops through the query based on APEX_COLLECTIONS view and JSON_TABLE() function to retrieve the properties from JSON structure.

function f_ajax (
  p_dynamic_action in apex_plugin.t_dynamic_action,
  p_plugin         in apex_plugin.t_plugin
) return apex_plugin.t_dynamic_action_ajax_result
is
  l_return apex_plugin.t_dynamic_action_ajax_result;

  c_token_collection_name                   constant varchar2(50) := 'GOOGLE_ACCESS_TOKEN';
  c_google_drive_api_output_collection_name constant varchar2(50) := 'GOOGLE_DRIVE_API_OUTPUT';

  l_token_type   varchar2(200);
  l_access_token varchar2(500);
begin

  case apex_application.g_x01
    when 'saveTokenInCollection' then
      apex_collection.create_collection(p_collection_name    => c_token_collection_name,
                                        p_truncate_if_exists => 'YES');

      apex_collection.add_member(p_collection_name => c_token_collection_name,
                                 p_c001            => apex_application.g_x02,
                                 p_c002            => apex_application.g_x03);                                        

      apex_json.open_object();
      apex_json.write('isOk', true);
      apex_json.close_object();

    when 'getTokenFromCollection' then
      begin
        select c001,
               c002
          into l_token_type,
               l_access_token
          from apex_collections
         where collection_name = c_token_collection_name
           and seq_id = 1;

        apex_json.open_object();
        apex_json.write('token_exists', true);
        apex_json.write('token_type', l_token_type);
        apex_json.write('access_token', l_access_token);
        apex_json.close_object(); 
      exception
        when no_data_found then
          apex_json.open_object();
          apex_json.write('token_exists', false);
          apex_json.write('error_message', '');
          apex_json.close_object(); 
        when others then
          apex_json.open_object();
          apex_json.write('token_exists', false);
          apex_json.write('error_message', sqlerrm);
          apex_json.close_object(); 
      end;
    when 'saveGoogleDriveResultsInCollection' then
      apex_collection.create_collection(p_collection_name    => c_google_drive_api_output_collection_name,
                                        p_truncate_if_exists => 'YES');

      apex_collection.add_member(p_collection_name => c_google_drive_api_output_collection_name,
                                 p_clob001         => apex_application.g_x02);       

      apex_json.open_object();
      apex_json.write('isOk', true);
      apex_json.close_object();
    when 'truncateCollectionOfResults' then
      if apex_collection.collection_exists(p_collection_name => c_google_drive_api_output_collection_name) then
        apex_collection.truncate_collection(p_collection_name => c_google_drive_api_output_collection_name);    
      end if;

      apex_json.open_object();
      apex_json.write('isOk', true);
      apex_json.close_object();      
  end case;

  return l_return;
end f_ajax;

Step by Step Setup

Setup Your Google Project

Basically, this article does not describe how to set up the project in Google API Console. Anyway, I have created a short checklist which you can compare with what have done so far and see what is already missing.

1. Enable Google Drive API.

2. Configure Consent Screen.

3. Generate Login Credentials - API Key and OAuth 2.0 Client. Here, remember about correct form of URL passed in Authorized JavaScript origins section. For example:

    https://your-atp-cloud-instance.adb.eu-frankfurt-1.oraclecloudapps.com
   

Plug-In Definition

Once I described the render() and ajax() functions, lets take a look at other settings applied to my Plug-In. They can be changed any time you want during your Plug-In's lifetime.

• Standard Attributes - I checked two options: For Button and Has "Wait For Result" attribute. Then talking about the second one, this property is set to True value by default, so please consider that and check if it work in your scenario.

  

• Attribute Groups - I added my own group with name 'Session State'. I think it is a good practice to organize your Plug-In settings into meaningful sections and make it self explaining to developers as possible.

  

• Custom Attributes - I added one of Page Item type. It helps to select which APEX page item stores the URL of Google Drive folder. This will be treated as an input to my Plug-In.

  

  💡

  As software developers, we should be aware of vulnerabilities and ways to manage them correctly. If you are giving custom attributes to be filled by users, be suspicious and use API packages like APEX_ESCAPE, DBMS_ASSERT, etc. to eliminate opened doors in security of your Plug-In.

• Files - there are two JavaScript files. File called 'script.js' is responsible for utilizing the Google Identity Services library and performing the API call. The second one stores set of functions producing the Plug-In's output - collection of matched images in JSON format. Minified versions of scripts are created by APEX by default when saving the normal script versions.

  

• File URLs to Load - I have three references here, each in separate line. First is a link to the Google Identity Library. I believe you can download it from web and upload the static script when performance of your application is the key. Other than that, I entered the references to minified versions of scripts created by myself.

  

JavaScript Functions

Access Token Handling

Some Tips and Advices

When thinking about using access tokens, there are couple of things that you have to remember. You can find them listed below with few words of explanation.

• Security - once I got the access token I can use it then to call those APIs which are referencing to Google account's content by implementing OAuth2. So it should be stored in a safe place which should be hidden from smart users inspecting the application, like reading the HTML, etc.. I decided to store current access token in another APEX collection. First advantage is that it cannot be accessed by user in a simple way. The second is that this token can be easily taken from the collection any time from any APEX page - we decrease the number of requests for new access token to Google side.

• Scope - always request for minimal API scope necessary to do your job with API. In this case, even though someone would steal the token, there is a chance that the minimal scope will not destroy anything. But, it is not always the case, that is why the way of securing the access token is absolutely important.

  💡

  The following code is an extracted part of MVP, so it may be oversimplified in some areas.

    function initTokenClientAndRequestAccessToken() {
      let client = google.accounts.oauth2.initTokenClient({
        client_id: 'client-id.apps.googleusercontent.com',
        scope: 'https://www.googleapis.com/auth/drive.metadata.readonly',
        callback: tokenResponseCallback
      });
  
      client.requestAccessToken();
    }
  
    async function tokenResponseCallback(tokenReponse) {
      let saveTokenState = await saveTokenInCollection(tokenReponse);
  
      await getFilesByParentFolder();
    }
  
    async function getFilesByParentFolder() {
      let tokenState = await getTokenFromCollection();
  
      if (!tokenState.token_exists) {
        initTokenClientAndRequestAccessToken();
        return;
      }
  
      let googleDriveAjaxResult;
  
      const GOOGLE_DRIVE_FILES_URL = "https://www.googleapis.com/drive/v3/files";
      const API_KEY = 'some-api-key-string';
      const ORDER_BY = 'orderBy=name';
      let PARENT_FOLDER_ID = configObj.folderId;
      let FIELDS = "fields=files(id,name)";
  
      let REQUEST_URL = GOOGLE_DRIVE_FILES_URL + "?" + 
                        ORDER_BY + "&" + 
                        "q='" + PARENT_FOLDER_ID + "' in parents and trashed=false" + "&" + 
                        FIELDS + "&" +
                        "key=" + API_KEY;
  
      try {
        googleDriveAjaxResult = await $.ajax({
          headers: {
            'Authorization': tokenState.token_type + ' ' + tokenState.access_token
          },
          url: REQUEST_URL,
          type: 'GET'
        });
  
        if (googleDriveAjaxResult.files.length == 0) {
          apex.message.showErrors([{
            type: "error",
            location: "page",
            message: "API call returned no results. Check your folder URL or names of images."
          }]);
  
          apex.da.cancel();
        }
  
        let outputArray = transformGoogleDriveResponse(googleDriveAjaxResult);
  
        if (outputArray.length == 0) {
          apex.message.showErrors([{
            type: "error",
            location: "page",
            message: "Pairing algorithm has not found any images to match."
          }]);
  
          apex.da.cancel();      
        }
  
        let output = {};
        output.items = outputArray;
  
        let res = await saveGoogleDriveResultsInCollection(output);
  
      } catch(e) {
        if (e.readyState === 4 && e.status === 401) {
          initTokenClientAndRequestAccessToken();
        } else {
          console.log("unhandled error", e);
          apex.message.showErrors([{
            type: "error",
            location: "page",
            message: e.status + " " + e.responseJSON.error.message
          }]);
  
          apex.da.cancel();        
        }
      }
    }
  
    // example of AJAX function to some DB stuff
    async function saveTokenInCollection(tokenResponse) {
      let pData = {
        x01: 'saveTokenInCollection',
        x02: tokenResponse.token_type,
        x03: tokenResponse.access_token
      };
  
      let pOptions = {
        success: function(data) {
          return data;
        },
        error: function(jqXHR, textStatus, errorThrown) {
          return jqXHR;
        } 
      };
  
      let result = await apex.server.plugin(PLUGIN_AJAX_IDENTIFIER, pData, pOptions);
  
      return result;
    }
  

• Token's reusability - some of you could say that maybe it is too much of effort to store current access token in APEX collection and why do I really think about that. Well, there are some issues to be aware of. First, APEX app is integrated with Google on JavaScript side. Second, Google Identity Library displays the Google account selector every time the user requests a new token, which could not be user friendly for some of you. Thirdly, not using the full time of 60 minutes, for which access token is valid, could lead us to the Google security limits given for a particular timeframe. In other words, Google will protect itself from too many token/API requests in a short period of time. You have to be very stubborn do achieve that, but it is feasible.

Access Token Flow in My Plug-In

1. User goes to APEX page which does the Google Integration. Then page button is clicked by the user. It is associated with my Plug-In on Dynamic Action level.

   

2. First, the APEX collection is queried to see if there is an access token already (we do not know if it is still valid at this moment, we are going to check that later).

3. If the query raised NO_DATA_FOUND exception in point 2, we know that we have to request for a new access token. This situation is a normal case when you have just logged in to the application with a new APEX session. When token request is finished, we save it in the collection first, and then we are trying to call the Google API. Otherwise, when the query returned some existing token, we are calling the API and adding the access token to the Authorization request header.

      try {
        let googleDriveAjaxResult = await $.ajax({
          headers: {
            'Authorization': tokenState.token_type + ' ' + tokenState.access_token
          },
          url: REQUEST_URL,
          type: 'GET'
        });
    } catch(e) {
        if (e.readyState === 4 && e.status === 401) {
            // request for new access token
        }
    }
   

4. In this step, the API call can return HTTP status 200 or non-200. Status code 200 means that we are done and can process the response as we wish. Other kinds of codes return additional information about the problem that occurred. From the token flow perspective, the crucial code is 401, which means Unauthorized. That shows us that the token we got in the APEX collection is out of date and cannot be used in API calls anymore. In such a case, request a new access token, then save it in our APEX collection and try to call the API again, but this time using the fresh token. For token storing activities, I am calling particular AJAX functions defined in the definition of my Plug-In as explained above.

Side Topic - What is the thumbnailURL?

Google Drive could be a nice option to store images asociated with your APEX application. Once both are talking to each other, displaying some images from Google Drive is a pretty common use case.

You can use files.list method to get necessary metadata. Just be aware of some properties available to fetch, like id, webViewLink, mimeType etc.

https://developers.google.com/drive/api/reference/rest/v3/files/list

Since Google Drive can keep any content (where images are rather ones that come into our minds so often), is there any chance to display the image just using <img> HTML tag? At first glance, docs do not mention about such property which could be ready to use in no time.

However, that is possible! But, you need to be careful. Here is why: the following URL format was fine to use it in the URL property for a long time. Many Stack Overflow questions could be a proof that developers were looking for the answer.

<img src="https://drive.google.com/uc?export=view&id=#FILE_ID#"/>

However, if you try this in May 2024, it seems it does not work anymore and it made many developers confused.

Happily, one of the forum discussions started at the beginning of 2024 brings us the latest format of the URL which works so far (see it allows to define the size of the image):

<img src="https://drive.google.com/thumbnail?id=#FILE_ID#&sz=w500-h300">

Unfortunately, I was not able to find this answer in the official documentation. But, I rather want to say that I am just missing something.

Side Topic - Map of Responses from Google Drive API

Depending on the scenario, response got from Google Drive API can be different and can be quite confusing for some of you trying that for the first time. I aggregated everything that you have to know in the following table.

The thing which was not so intuitive for me was that for no access to folder, the response is still with code 200 and empty list of available resources. But maybe it is just me :-)

Pros and Cons of Plug-In Based Solution

• If you are going to enable 'Has "Wait For Result"' attribute, please consider the flow of your Dynamic Action Plug-In. You have to know that this feature has to be managed by the developer, who has to define scenarios in which Plug-In finished its job with success or failure. It is extremely important if you want to add more actions following your Plug-In action. For those purposes, use apex.da namespace from APEX JavaScript API.

• What if we say that anyone should be able to see the images from Google Drive, even if particular user does not have Google account at all? Well, this case shows a significant downside of this solution - any time you want to talk to Google, you have to receive the access token first, what is possible only when you have an account there. And of course, as Google user, the resources should be shared with me by one of possible methods.

Summary

I had lots of fun and inspiring question marks in my head when doing the implementation. It was also an opportunity to learn something new about APEX Plug-Ins and how to do it right. Since we started from JavaScript approach plus the awarness about its limitations, we concluded that it is better to go for solution based on Service Account. Anyway, we were able to build a working beta solution which solves real business problem. And obviously, it still requires polishing and deeper testing. Anyway, I really hope that this case study will be helpful for some of you when dealing with Google integrations.