Welcome Guest! Log in


This article provides an introduction to Stambia's JDBC driver for reading and writing Google Sheets.

Adding the driver to Stambia Designer

The driver is included in your Stambia Runtime's installation, inside the stambiaRuntime/lib/jdbc directory. It is named "stambia.jdbc.driver.google.jar".

In order to run SQL queries from the Designer (Consult data, for example), you need to add this driver to the Designer:

- Open the Preferences panel and search for the JDBC section

01-jdbcpref

- Click on the Add button and set the new driver properties:

  • Name: Stambia GoogleSheet Driver
  • Example URL: jdbc:stambia:google:spreadsheet
  • Go to the "Extra Class Path" tab and click "Add JARs..."
  • In the popup window, locate the "stambiaRuntime/lib/jdbc/stambia.jdbc.driver.google.jar" file and click OK
  • Click the "List Drivers" => the Driver Class Name property should be automatically set to "com.stambia.jdbc.driver.google.SpreadsheetDriver"

02-newdriver

 

 

 

Reading a Google Sheet in a mapping

Creating the Google Sheet "TC0020_source"

First, we create 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:

03-TC0020 source

 

The Google Sheet Metadata

Create a new Stambia Project

Add a Google Spreadsheet Metadata:

04-md1

Name it "GoogleDrive" and click Finish.

The Server Wizard appears.

Get a JDBC URL with the help of this article: Getting a Google oAuth2 JDBC URL.

Make sure "User name is not required for this database" is checked, and click Connect.

 

Click the "Refresh Values" button and choose the document name : "TC0020_source". Then click "Next":

06-serverwiz2

 

Click "Refresh" to list the sheets => the "$People" sheet should be listed.

Check it and click Finish:

07-serverwiz3

 

Stambia will reverse the Sheet :

08-reverse

 

The mapping

This mapping reads data from the Google Sheet, and populates a Mysql table.

09-map1

 

There is nothing particular on this mapping. The execution poplates the target table with the three rows of data :

10-stats

 

11-select

 

 

Writing to a Google Sheet

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

There are some requirements when writing to a google sheet:

  • The mapping must be set to "NO_LOAD" mode. This ensures that Stambia will not try to create Load tables.
  • The Integration template must be set to "Append mode"

 

Setting the "NO_LOAD" mode is done by adding a "Criteria" on the mapping and clicking the "+" button.

Please see the screenshots below:

12-mapnoload

 

13-append

 

The "Truncate target table" mode is optional. You can uncheck it if you want.

 

Here is the statistics after execution:

15-stat

 

And this is the data written to the Google Sheet:

14-data

 

 

 

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!