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.
Click on this, and in the pop-up window that opens, select the “New project” option in the top right corner.
On the new project configuration screen, enter its name and click the “Create” button.
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.
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.
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.
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.
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
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.
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.