Welcome Guest! Log in
Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.

The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…

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:

  1. Google Project preparation
    • Create a Project on Google's website
    • Generate Google IDs for this project on Google's website
  2. 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.

 

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..........

 

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.

 

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"

 

 

Articles

Suggest a new Article!