This article provides information on how to generate a JDBC URL for Stambia's Google Spreadsheet Metadata, relying on Google's oAuth2 authentication API.
Basically, the steps are:
- Create a Project on Google's website
- Generate Google IDs for this project on Google's website
- Execute Stambia's Google Driver utility to generate the JDBC URL.
Why using oAuth2 instead of Username/Password
Before 2015, it was possible to authenticate programmatically on Google Drive with a Username and Password, or with the oAuth2 API. Stambia's Google Driver can work with both authentication systems (oAuth2 since version 17.2.12).
Since early-2015, Google migrates the Google Drive API and authentication: the Username/Password authentication is removed, and remains the oAuth2 API (see https://developers.google.com/identity/protocols/OAuth2).
Incidently, Stambia's Google Driver cannot connect using a Google username and password as this is no longer supported by Google (although this may still work on some accounts, because Google upgrades accounts and spreadsheets progressively).
Thus, Stambia users are invited to change their Google Spreadsheet Metadata for using an oAuth2 JDBC URL.
Requirements
The required Stambia Runtime version for using oAuth2 is 17.2.14 or higher (included in Stambia Designer S17.2.14 or higher, and S18.0.3 or higher).
If your Runtime is older, you can also keep your current Runtime and install the Stambia's Google Driver extracted from Runtime 17.2.14. Please contact the Support Team to know if your version is compatible.
Creating a Google Project
Note : Stambia provides this information for the convenience of people who are not familiar with oAuth2. If you encounter issues when manipulating your Google account, projects or IDs, you will have to contact Google's Support, not Stambia's Support.
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.
Executing Stambia's Google Driver utility
On a command line, change directory (cd) to the stambiaRuntime/lib/jdbc directory.
stambiaRuntime\lib\jdbc>java -jar stambia.jdbc.driver.google.jar
Oauth2 (y/n):
y
Client Id:
Paste here your Google Project's Client ID.
Client Secret:
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:
https://accounts.google.com/o/oauth2/auth?response_type=code&scope=https%3A%2F%2Fspreadsheets.google.com%2Ffeeds%2F&redirect_uri=urn%3Aiet.........
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...
Access Token:
ya29.gQFRhqz_37IUTkJCkEb1xlj04NBZIaYOKk1Y6hPLwypfUebuQVqhb8VkrVwDXpiEFEktydBedKnA
Refresh Token:
1/WYSsY7qONvvWRXfOhv8wPwkvssSw-qXLQs-H81-5qJQMudVrK5jSpoR30zcRFq6
Connected
URL with refresh token:
jdbc:stambia:google:spreadsheet?oauth2RefreshToken=1/AfC64Y-nBloNrn...........
URL with access token:
jdbc:stambia:google:spreadsheet?oauth2AccessToken=ya29.hgEpcknEa9B..........
You will need to copy and paste the "URL with refresh token" in your Google metadata.
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 Metadata
Using the JDBC URL in Stambia
Simply paste this JDBC URL (with refresh token) in your Google Metadata's server node.
The username and password are not necessary anymore (since early-2015), and can be removed.
Known issues
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"