DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • OWASP TOP 10 API Security Part 2 (Broken Object Level Authorization)
  • Two-Way SSL Authentication Setup in Mule
  • Harnessing the Power of APIs: Shaping Product Roadmaps and Elevating User Experiences through Authentication
  • Keep Your Application Secrets Secret

Trending

  • Comprehensive Guide to Property-Based Testing in Go: Principles and Implementation
  • Cookies Revisited: A Networking Solution for Third-Party Cookies
  • Blue Skies Ahead: An AI Case Study on LLM Use for a Graph Theory Related Application
  • Concourse CI/CD Pipeline: Webhook Triggers
  1. DZone
  2. Data Engineering
  3. Databases
  4. How to Integrate Google Workspace With Oracle APEX and Oracle Database: Part 1

How to Integrate Google Workspace With Oracle APEX and Oracle Database: Part 1

Have you ever wondered about integrating your company's Google Workspace with Oracle APEX? We have good news: This is the ultimate guide to such integration.

By 
Wojciech Sowa user avatar
Wojciech Sowa
·
Dec. 07, 21 · Tutorial
Likes (1)
Comment
Save
Tweet
Share
8.0K Views

Join the DZone community and get the full member experience.

Join For Free

Many organizations base their functioning on tools from the Google Workspace package, formerly known as Google Suite. The wide range of useful tools encourages utilizing Google Workspace in every area of the company’s operations. So a key question arises — can Oracle APEX be integrated with Google Workspace in my company? Yes, of course! Recently, I have faced a challenge of this kind. The project was dedicated to pharmacy and one of the functionalities assumed fetching data from Google Sheets and downloading files from Google Drive.

In this extensive step-by-step tutorial, I will show you how to integrate Google Workspace with your Oracle Database and Oracle APEX applications. The tutorial consists of two parts, and each of them is divided into several sections.

What Is Google Workspace

Google Workspace includes office applications and other productivity tools. It is a set of services provided by Google as a cloud solution. It allows the commercial use of Google applications, such as Google Docs, Google Sheets, Google Drive, etc. After purchasing access to Google Workspace, the company receives a personalized space with its domain and access to services included in the selected subscription. Working with Google Workspace tools can be done on a computer, phone, or tablet.

Google Workspace has become an alternative solution to standard office applications, especially in cases where organizations have decided to stop using office suites installed locally on computers. It is really worth integrating with Google Workspace. Otherwise, the organization exposes itself to low application performance and forces end users to switch between the business application and their resources from the Google account. For many companies, Google Workspace has become one of the critical elements of the internal system infrastructure.

How Google APIs Work and How to Access Them

Google Workspace provides an extensive REST API, thanks to which it is possible to use the API methods from the custom application level. For example, the API allows you to add events to Google Calendar, download files from Google Drive, or read data saved in Google Sheets. The application we create may look like a Google Workspace tool due to the high level of integration.

Access to Google API is available on two primary levels. The choice of access method depends on the specific API functionality and is defined in the documentation. Both Google API access methods can be configured in the Google API Console.

  • If the API call does not refer to the user’s private data, we will use the API key. This is a string that does not change over time and does not require a periodic update. The API key is used for the identification of transactions between our application and the Google Auth server.
  • Otherwise, when we use the API to perform CRUD operations on users’ data, we will use the OAuth 2.0 protocol.
    • In this case, we use the access token to authorize API access, which is valid for one hour. After this time, your application should obtain a new access token to communicate with Google API.
    • You have to keep in mind that the OAuth 2.0 protocol implemented on the browser level is designed for applications that access APIs only while the user is present at the application.
    • The alternative is to use a service account and call Google API without confirmation of its identity. This approach will be described in the coming chapters.

What is important is that Google API refers to the file’s access rights of the calling user, so if you have access to the file (you are an owner of the file or it is shared with you), then you can access it using Google API. Otherwise, you can not.

Keywords board for integration steps
Keywords board for integration steps

When creating an application in Oracle APEX, it would be great to have native support in integration with the Google ecosystem. Still, it is not fully implemented by the built-in functionalities of the framework. We need to extend the existing mechanisms to make the integration with Google API effective. It can be implemented on two levels: JavaScript and PL/SQL. Each of these levels has its specificity and will be explained in the coming sections.

Configuring a Project in Google API Console

Okay, it’s time to configure a project in Google API Console to use the vast possibilities offered by Google API. One of the necessary elements will be implementing the OAuth 2.0 protocol, through which we can call APIs accessing users’ private data. To implement OAuth 2.0, we will use the "Google API Client Library for JavaScript" provided by Google. 

This JavaScript library will be responsible for authenticating the user with their Google account. It will allow you to get the access token needed to call the API. You can use this token in calls from JavaScript and PL/SQL. We will also create an API key required for API calls that do not access the user’s private data.

Go to https://console.developers.google.com/start. At this point, you can use the Google account in your organization’s domain to log in.

From the "APIs & Services" menu, select "Dashboard," and then select the available option "Create Project."

If you already have any other project created in your account, its name will be visible instead.

Google APIs -- conference option

Click on this, and in the pop-up window that opens, select the "New project" option in the top right corner.

New Project button

On the new project configuration screen, enter its name and click the "Create" button.

New Project creation

After creating the project, you will be automatically redirected to it. However, if this has not happened, select the newly created project from the project selector.

Selecting project from project selector 

From the menu on the left side, go to the "Library" tab. The entire library of available Google APIs appears. For our example, let’s say that we will need the "Google Drive API" library. Find it and then choose it. On the summary screen, click the "Enable" button.

Google Drive API -- Enable

After activating the API, you will be taken to the "Overview" section where there is more information about it. You will find information about the usage statistics of this API over various periods and links to documentation and tutorials.

Select the "Credentials" option from the menu available on the left, and then select the "+ Create Credentials" option from the top bar. From the available menu, select the ‘OAuth client ID’ option.

+Create Credentials option

In the first step of the wizard, you will be asked to configure the "consent screen." This screen will appear every time you try to authenticate with Google, and it will contain a list of saved Google accounts with the option of selecting one to continue user activities. Select the "Configure consent screen" button.

The wizard will ask you to select the type of user. We will focus on the internal type, limiting the possibility of integration to your organization. Click the "Create" button.

OAuth consent screen

On the "OAuth consent screen," we have several attributes that you can adapt to your application’s needs. The mandatory fields are:

  • Application name
  • User support email
  • Email address in "Developer contact information" section

Additionally, you can pick an image with the application logo or a link to the privacy policy. Click the "Save and Continue" button.

In step 2, "Scopes," you can select the scope of permissions for user data in Google Workspace that are applicable in your project. Using the “Add or remove scopes” option, select all relevant items from the list and click "Update." Then click "Save and Continue." You can learn about the scopes of permissions needed for your integration from the documentation for a specific Google API method.

In step number 3, "Summary," you can see a summary of our consent screen. After verification, click the "Back to Dashboard" button.

Let’s generate all necessary credentials to use Google API. Go to https://console.developers.google.com/start. At this point, you can use the Google account in your organization’s domain to log in. Pick the project that you created previously. Select the "Credentials" option from the menu on the left. Select the "+ Create Credentials" option from the top bar. Select "OAuth client ID" from the available menu.

From the available "Application type" list, select the "Web application" option. Enter the name of your client and go to the "Authorized JavaScript origins" option. In this option, select the main address to your application, in which you will use the integration with Google. In our example, if the APEX application is located in the public apex.oracle.com environment, enter this address: https://apex.oracle.com. For your local environment, enter http://localhost:8080. Click the "Create" button.

Authorized JavaScript origins

Warning: If you decide to add a new environment to the "Authorized JavaScript origins" address list sometime after your OAuth 2.0 client has been created, this environment will likely not be supported correctly by the Google API. It will cause an error visible in the browser console, which means no new environment in the list of authorized JavaScript domains. In this case, create a new OAuth 2.0 client for all environments, including the new one.

After creating a new OAuth 2.0 client, you will receive information that your client has received two essential elements: "Client ID" and "Client Secret." These two attributes are the login and password equivalents and will be used in the JavaScript library.

OAuth client created

The "Credentials" screen shows the created client. We can view its details, rename it, reset "Client ID" and "Client Secret," and add addresses in the "Authorized JavaScript origins" section.

Additionally, for integrations that do not require the OAuth 2.0 protocol, generate an API key. In the Credentials tab, select "+ Create Credentials" and select the API Key option. The key will be generated. You can choose restrictions on the API keys’ use due to the allowed application types that can use an API key. You can also select which of the activated Google API libraries in our project will be authorized to use the key. We use the generated key in Google API calls by adding the key=YOUR_API_KEY parameter to the request.

Complete Google Workspace
Google Workspace is set up!

Conclusion

We got it! We have finished configuring Google Workspace. The API needed to manage Google Drive is now ready for use in our APEX application. The next step is to prepare the database for communication with Google API and configure the JavaScript library in the APEX application itself.

Important: Besides implementing the OAuth 2.0 protocol based on the JavaScript library, there is also a second method based on a special account called service account. This type of account is not associated with any Google Workspace human user in your company. This type of account is useful when you integrate with Google API in systematic processes, e.g. database jobs responsible for data synchronization running at night. In these cases, you can implement the integration without any user action required, as with the consent screen. From the Oracle database perspective, the service account can be used thanks to the implementation of a JSON Web Token (JWT) and the appropriate cryptographic algorithm, which will allow proper communication with the Google token service. This approach will be described in the second part of this article.

And that's it for part one. In part two, I'll cover integration using JavaScript library and the service account. I'll also write about social sign-in.

Google (verb) Workspace Integration Database API application Oracle Database JavaScript library security authentication

Published at DZone with permission of Wojciech Sowa. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • OWASP TOP 10 API Security Part 2 (Broken Object Level Authorization)
  • Two-Way SSL Authentication Setup in Mule
  • Harnessing the Power of APIs: Shaping Product Roadmaps and Elevating User Experiences through Authentication
  • Keep Your Application Secrets Secret

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!