Welcome Guest! Log in


As explained in the presentation article, Stambia can perform operations on Google Sheets such as reading or writing data into Google Sheets.

To accomplish this, you'll first have to install the dedicated Component and prepare your environment.

You can find below all the explanations and instructions to install and set up everything to get ready to work with Google Sheets.

This article is dedicated to Stambia DI S17, S18, S19.

if you are using Stambia DI 2020 (S20.x.x) or higher, please refer to this article.

 

Prerequisites

This article explains how to work with latest Google Sheet Component, which supports Google "v4 API":

  • Google Sheet Plugin 1.0.0 or higher (Stambia DI S17, S18, S19)

 

If you are using a prior version, we highly suggest to migrate to the new Component as previous versions were using Google "v3 API" which is getting removed by Google.

Prior versions will therefore stop working when Google will remove the "v3 API".

Refer to the following migration article which explain how to migrate.

 

Component Installation

Installation

You must install Google Sheet Component to be able to work with those technologies in Stambia DI.

You can find Components download section and installation procedure at this location.

Follow installation instruction corresponding to the version you are using.

 

SQL Explorer JDBC Driver Configuration

Depending on Designer version you are using and from which you created your workspace, you may need to manually add the Google Sheet JDBC entry in SQL Explorer Preferences.

To check if it is already present and add it if not, you can proceed as follow.

Open the following menu and look for the "Google Spreadsheet Driver": Window > Preferences > SQL Explorer > JDBC Drivers

sql explorer google sheet driver

 

Note that if you are on an existing workspace, and / or if you are on a prior version than S19.0.22, the driver will not be listed, and you will have to add it manually.

For this, click on the "Add" button, which will display a window to create a new driver.

Then define the properties as follow:

 

Property Value
Name Google Spreadsheet Driver
Example URL jdbc:stambia:google:spreadsheet?<TOKEN_URL_INFORMATION>
Driver Class Name com.stambia.udriver.googlesheets.GSheetsDriver

 

Finally, in "Extra Class Path" tab, you need to add all the libraries which have been generated by the Component, by clicking on "Add JARs..." button.

They are generated at the following location:

<Designer Installation Folder>/stambiaRuntime/lib/addons/google_spreadsheet

 

sql explorer google sheet driver extra class path

 

Getting necessary token to communicate with Google Sheets

When Stambia DI Google Cloud Sheet Component is installed and configured, before going further and creating your first Google Sheet Metadata, you'll need to configure your Google Project and generate a token for Stambia Component to be able to communicate with Google Sheets.

You can refer to the following article which explains how to perform that.

 

Metadata definition

You have now installed Google Sheet Component and retrieved the token information.

You can therefore start creating your first Google Sheet Metadata.

This Metadata is will be used to reverse the Google Sheets you want to perform operations on.

 

Metadata creation

Create the Metadata, as usual, by selecting the technology in the Metadata Creation Wizard:

metadata new

Click next, choose a name, a Module, and click on finish.

 

Metadata Configuration

A new Wizard will open to define connection information.

This is where you will specify the JDBC URL with refresh token you previously generated.

Specify the JDBC URL, click on Connect, and then on Finish.

metadata configuration

 

You are now ready to start reversing Google Sheets.

 

Reversing a Google Sheet

Google's "v4 API" does not allow to retrieve all the sheets of a given user as it was possible before.

Impact of this is that we cannot reverse for now all Google Sheets of a given user at once, as we cannot list them from Google's "v4 API" as it was possible before.

We need for now to know the exact identifier of a Google Sheet to be able to perform operations on it.

 

To reverse a Google Sheet, first add a DataSchema, which will represent a Google Sheet, and define a label on it:

metadata schema

 

Then, you have to put the identifier of the Google Sheet you want to access in the "Physical Name".

The Spreadsheet Identifier can be extracted from its URL.

This ID is the value between the "/d/" and the "/edit" parts of the URL.

 

metadata sheet id

 

Finally, you can right click on the schema, choose Actions > Properties.

Then you can go to the "Reverse" tab to reverse this Google Sheet

 

metadata reverse

 

The Google Sheet is now reversed:

 

metadata reversed

 

 

Reading a Google Sheet in a mapping

Example of Google Sheet source data

We created a Google Sheet with the following data.

The sheet tab is named "People".

ID FIRST_NAME LAST_NAME
501 Bobby FISCHER
502 Peter TRUWAVE
503 Steven ROOTHERS

 

Here is a screenshot of the Google Sheet:

example sheet

 

Reading data from Mapping

Reading data from Google Sheet in a Mapping is no different than any other Mapping.

Drag and drop the Google Sheet datastore on the Mapping and map the fields on a target datastore.

In the following example, the Mapping reads data from the Google Sheet, and populates a table in HSQL demo database.

example read mapping

 

The execution populates the target table with the three rows of data in target table:

 

example read mapping result

 

Writing to a Google Sheet

Now, we are going to add content to our Google Sheet, from the Hotel Demo Database.

Same as for reading, this is a simple Mapping working as usual.

Drag and drop the sources from which to load data into Google Sheet and the target Google Sheet, then map the fields.

example write mapping

 

And this is the data written to the Google Sheet:

 

example write mapping result

 

 

 

Comments  

# Nicolas Verscheure 2018-03-27 14:56
Does the Google Sheet Driver works with an SQLOperation ?
# Guillaume Duez 2018-07-02 09:11
Is it possible to update any field in a Spreadsheet?
# Thomas BLETON 2018-07-02 09:30
Hi, SQL operations can be used for SELECT and INSERT statements. Update is not supported at the moment.

You have no rights to post comments

Articles

Suggest a new Article!