Connect to a Google Spreadsheet Source

Version 4

    Connect to a Google Spreadsheet Source

    >>> Back to Examples....

     

    This article will guide you through an example of connecting to a Google Spreadsheet source, using Teiid Designer.  It is assumed that you have already installed the latest Teiid Designer.  For this example, we will consume a simple Google Spreadsheet as a source.  The spreadsheet in this example is TestSheet.  It has three columns (A, B and C) with 5 rows of data - as shown below:

    GoogleSheet.png

      IMPORTANT: If you have a google spreadsheet containing only string data, please add a dummy numeric column.  At time of this article update, we have an open issue - TEIID-2998 .  Problems have been observed with sheets containing only string data.  For now, the workaround is to add a dummy non-string column to the sheet.

     

    1. Start your local teiid server instance

    • Teiid Designer requires a running Teiid server for Data Preview.  We will use the Data Preview capability to test our Google Spreadsheet source.

     

    2. In Teiid Designer, use the 'Teiid Connection' importer to create a source model

    • Select File > Import... > Teiid Connection >> Source Model , then click Next

    DesignerSelectTeiidConnImporter.png

    • On the first page of the wizard, click New... to create a new Data Source
    • On the 'Create DataSource' dialog:
      • Enter GoogleSheetDS for Name:
      • In the Available Drivers list, select google
      • For the Authentication Method property, enter ClientLogin
      • For the Google Account Username and Google Account Password properties, enter the username and password of the google account.
      • For the Name of the Spreadsheet... property, enter TestSheet
      • then click OK

    CreateDSDialog.png

    Note: The OAuth2 Authentication method is also allowed (if preferred).  Please see Teiid Docs for more information.

    • Back on the first wizard page, the GoogleSheetDS should show up in the data sources list.  Select it, then click Next
    • On the next page of the Teiid Connection import wizard
      • Select google-spreadsheet for the translator
      • NOTE: If the google-spreadsheet translator is not available, stop the server.  Then edit the server's standalone-teiid.xml configuration file, adding this line in the 'translators' section:

     

    <translator name="google-spreadsheet" module="org.jboss.teiid.translator.google"/>
    
    
    
      • Click Next
      • click NextImporterPg2.png
    • On the next page of the wizard, enter GoogleSheetMdl for the target model Name.  click Next

    ImporterPg3.png

    • The next page of the wizard should display the schema DDL.  click Next

    ImporterPg4.png

    • The final page of the wizard shows the table to be created, and allows you to select which tables will be imported.  Leave the Sheet1 table selected.  Click Finish.

    ImporterPg5.png

    • The relational model GoogleSheetMdl should now be created in the ModelExplorer view

    ModelExplorer.png

     

    3. Preview the Google Spreadsheet table

    • In ModelExplorer, expand the model and select the Sheet1 tableClick the 'Running man' icon in the ModelExplorer toolbar.  The preview query should execute, with results displayed in the SQL Results View.

    SQLResults.png