When working with Google Sheet Component, you will need to generate a token to be able to authorize communications and operations with Google Sheet.
This article provides information on how to generate a JBBC URL with OAuth2 token for Stambia's Google Sheet Component.
Why do I need to generate an OAuth2 token?
Performing operations on Google Sheets requires to be authorized to.
OAuth2 tokens are wildly used today to give the ability to application to perform operations on behalf of a user.
Stambia's Google Sheet Component needs therefore this kind of token to be able to communicate with Google Sheet.
You can find below a summary of the steps to follow to be able to generate tokens:
- Google Project preparation
- Create a Project on Google's website
- Generate Google IDs for this project on Google's website
- Generation of OAuth2 Tokens
- Execute Stambia's Google Driver utility to generate the JDBC URL.
Google Project Preparation
Accessing Google Sheets from external applications such as Stambia requires a configured Google Project.
We described in the next steps some entry points to perform that on your side.
Note however that Stambia provides this information for the convenience of people who are not familiar with oAuth2.
The procedure may change on time and the screenshots may not be accurate if Google's website changes other time.
If you encounter issues when manipulating your Google account, projects or IDs, you will have to contact Google's Support, not Stambia's Support.
We can help on generating OAuth2 tokens from the Stambia utility, but the configuration of Google's Project you should refer directly to Google's documentation for further information.
Creating a Google Project
Log into the Google Account which has access to the desired spreadsheets.
Navigate to the Google Developer Console: https://console.developers.google.com/
Create a new Project, giving it a Project name and a Project ID.
Generating Client ID and Client secret
Once this is done, click on APIs & auth > Credentials.
Under the OAuth section, click Create new Client ID and choose Installed application. Click Configure consent screen.
Select the Google Account's Email address and set a Product name, for example "MyStambiaApplication". Click Save.
Select Installed application and the "Other" application type.
Click Create Client ID.
Google will display your Client ID and Client secret. Please keep them safely because they are your new credentials.
Generation of OAuth2 Tokens from Stambia utility
Your Google Project being ready, you can now use Stambia's utility to generate a complete JDBC URL with OAuth2 token which will be used in Google Sheet Component to perform operations on Google Sheet.
Google Sheet Component is providing for this a command line utility which can be used to generate tokens.
The next section explains how to use it.
Generating token for Google Sheet Component
Generating tokens for Google Sheet Component will be performed through the dedicated command line utility.
First step is to open a command line prompt, and change directory (cd) to the directory where the utility is located.
Depending on Google Sheet Component version and Stambia DI version, it is shipped at a different location.
|Designer Version||Google Sheet Component Version||Location||Utility Name|
|Stambia DI 2020 (S20.x.x) or higher||Google Sheet Component 2.1.0 or higher||<Google Sheet Module folder>||stambia.jdbc.udriver.gsheets.jar|
|Stambia DI 2020 (S20.x.x) or higher||
Google Sheet Component 2.0.x
|<Google Sheet Module folder>||stambia.jdbc.driver.google.jar|
|Stambia DI S17, S18, S19||Google Sheet plugin 1.0.0 or higher||<Designer installation folder>/stambiaRuntime/lib/addons/google_spreadsheet/||stambia.jdbc.udriver.gsheets.jar|
|Stambia DI S17, S18, S19||
No Components installed. Originally, the old driver was included in default installation.
|<Designer installation folder>/stambiaRuntime/lib/jdbc/||stambia.jdbc.driver.google.jar|
Executing the utility
You can now start the utility with the following command
java -jar <Utility Name>
For instance, with latest Google Sheet Component version on Stambia DI 2020:
stambiaRuntime\modules\Google Sheet\>java -jar stambia.jdbc.udriver.gsheets.jar
For instance, with latest Google Sheet Component version on Stambia DI S17, S18, S19
stambiaRuntime\lib\addons\google_spreadsheet\>java -jar stambia.jdbc.udriver.gsheets.jar
Following utility procedure
You can now follow the steps of the command line utility.
Paste here your Google Project's Client ID.
Paste here your Google Project's Client secret.
After this step, the console is going to display an URL:
Go to this url and copy paste the code into the console:
Copy this URL and paste it in your web browser. Google will display a new code. Copy it and paste it when prompted like this:
Enter the code: Paste here the code displayed by Google
Getting access token and refresh token...
URL with refresh token:
URL with access token:
And "voilà", the console displays important information which you should keep:
- The Google Access Token
- The Google Refresh Token
- The Stambia JDBC URL for your Google Sheet Metadata (URL with refresh token)
Using the JDBC URL in Stambia
Your tokens and URLs being generated, you can now use them in your Stambia Google Sheet Metadata.
On your Google Sheet Metadata, on the JDBC URL value which is prompted when using the wizard, or which can be found on server node, you can use the "URL with refresh token" value generated previously.
Since Google limited the connectivity to the oAuth2 protocol, we noticed a new issue. Please read this article for more information: Google Spreadsheet Driver raises error "com.google.gdata.util.ServiceException: Bad Gateway"