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


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





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

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:


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":



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

Check it and click Finish:



Stambia will reverse the Sheet :



The mapping

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



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






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:





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


Here is the statistics after execution:



And this is the data written to the Google Sheet:






# 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


Suggest a new Article!