Importing On-Premises Oracle Data in to Google Spreadsheets

Introduction

Combining on-premises data with cloud technologies almost always raises immediate concerns about security, but the DataDirect Hybrid Data Pipeline lets you securely access data behind any firewall without the requirement to make complex network configurations such as SSH tunnels, reverse proxies or VPNs. 

In this tutorial, you'll learn how to easily extract your on-premises Oracle data to Google Sheets using Progress Hybrid Data Pipeline and Google App Scripts. We will be using OData(REST) generated by Progress Hybrid Data Pipeline service to load the data to Google Sheets.

Setting Up Progress DataDirect Hybrid Data Pipeline

  1. Install Hybrid Data Pipeline in your DMZ  or in the cloud by following the below tutorials.
  2. To connect to On-Premises databases, you need to install an On-Premises agent on one of your servers behind the firewall, that lets the Hybrid Data Pipeline Server communicate with the database.
  3. To Install Hybrid Data Pipeline’s On-Premise Agent and configure it the cloud service where you installed Hybrid Data Pipeline Server, please follow the below tutorials.
  4. Also install the Hybrid Data Pipeline’s JDBC driver which can be found on the same download page of Hybrid Data Pipeline Server and On-Premise Connector. Follow this documentation to install the driver.
  5. Once you have everything setup, navigate to http://<server-address>:8080/d2c-ui or https://<server-address>:8443/d2c-ui to view the Hybrid Data Pipeline’s UI.
  6. Log in with the default credentials d2cadmin/d2cadmin
  7. Once you have logged in, create a New DataSource, by clicking on New Data Source button as shown below.

  8. 1 Google Sheets OData

  9. You should now see list of all Data stores as shown below. Choose Oracle as your data store.

  10. 2 Google Sheets OData

  11. On the Configuration, fill out all the connection parameters that you would generally use to connect to your Oracle database and set the Connector ID. The Connector ID is the ID of the On-premises connector that you have installed for this server and account in Step – 3. If you have installed and configured the On-Premise connector, you should automatically see the Connector ID In drop down.

  12. 3 Google Sheets OData

  13. Now Click on Test Connection and you should now be able to connect to your Oracle database On-Premise.
  14. Go to OData tab and click on “Configure Schema” button. You should now see list of schema’s in your database, select the schema that you want to access via OData.
  15. Once you select the schema, you should now see all the tables in your schema as shown below. Select the tables that you would want to expose through OData (REST) API and click on Save button.

  16. 4 Google Sheets OData

  17. Now you should be back on OData config tab. Copy the OData Access URI and click on Update button to save all the configurations.
  18. Paste the OData Access URI into a new tab to test it. Give your Hybrid Data Pipeline credentials when prompted for authentication and you should now see the response from the server listing your tables as shown below.

Creating the Plugin for Google Sheets

  1. Download the Google Sheets OData reader  from GitHub.
  2. Go to Google Sheets -> Create New Sheet.
  3. From Menu bar -> Tools -> Script Editor and you should see a script editor where you can code.
  4. Create the code files with same name in the Script Editor and copy the code to respective file.
  5. Go back to the Google Sheet, and refresh the page. You should now see a new Menu Item named “Import On-Premises Data”

  6. 5 Google Sheets OData

  7. Click on Configure OData and you should see a window as shown below.

  8. 6 Google Sheets OData

  9. Fill in the OData URI, your Hybrid Data Pipeline’s Username and password from Step 14. Click on Submit.

     

  10. You should now see another window listing all your tables as shown below.

  11. 7 Google Sheets OData

  12. Choose a table and click on Select. You should now see all of your on-premises data in the oracle table loaded up.


  13. 8 Google Sheets OData

You can import any of your On-Premises data in any of your databases systems or Big Data systems using Progress DataDirect Hybrid Data Pipeline. Feel free to change the Google App Script Code per your needs and let us know if you have any questions or difficulties in getting this running.
OData, Cloud and Hybrid TUTORIAL

Importing On-Premises Oracle Data in to Google Spreadsheets

DataDirect
connectors

Connect any data source to any application

DataDirect
Tutorials

Discussions, tips and tricks for
DataDirect Connect drivers