Opencart to Google docs: Pull and Sync data through scripting

Opencart to Google docs: Pull and Sync data through scripting

You can pull data from an Opencart shop to Google doc spreadsheet auto with just click of a button. This is of great use for retrieving data in spreadsheet format.

Here is a step by step Guide:

The demo below explains how to retrieve product data from Opencart on to Google spreadsheet

Step-1   Create a spreadsheet within your Google Drive

 

Step -2 Create a new spreadsheet and insert the script provided in below link.

https://docs.google.com/document/d/1iA3284ZrSEAGdwJQeb5dJXLRTkECMb52z4KwBqxUBZQ/edit?usp=sharing

How to insert script

To insert the script in Google spreadsheet go to Tools> script Editor

On opening script editor you will find a Google Apps Script pop-up Close it.

 

On closing pop-up you will also find some pre-written function tags. Remove them completely

Paste the script provided in script Editor.

 

Step -3: (Important step) Adding your MySQL database credentials and correct table names

Jdbc.getConnection('jdbc:mysql://demo.transpacific.me/opencart_db', 'admin', 'pass123')

Replace the place holder credentials (host, user, pass) for mysql

Also change place holder table names in script oc_product and oc_product_description as per your MySQL database (correct table names you can check through your cpanel phpmyadmin)

Step-4 Run the Script

You are all set to run the script!!

Run the script from top menu <Run> and it will pull the product data from your Opencart shop.

(it will ask for  script authorization on first run accept.)

Script will take anywhere between 20 seconds to 1 minute to pull data depending on connection speed and data size.  

Step -5 (Important step) connection error & White listing Google ip’s 

 

To Pull data; Script from Google Docs connects to your Opencart MySQL database. In some cases your MySQL database will not allow this unless you white list the visiting servers ip.

Google has provided a list of ip’s you need to white list. (this can be done through your servers  cpanel )

Below is the list of Google ip’s you need to whitelist. More details on:

https://developers.google.com/apps-script/guides/jdbc#authorization

216.239.32.0 - 216.239.63.255

64.233.160.0 - 64.233.191.255

66.249.80.0 - 66.249.95.255

72.14.192.0 - 72.14.255.255

209.85.128.0 - 209.85.255.255

66.102.0.0 - 66.102.15.255

74.125.0.0 - 74.125.255.255

64.18.0.0 - 64.18.15.255

207.126.144.0 - 207.126.159.255

173.194.0.0 - 173.194.255.255

Scope for customization:

The script is written for a default Opencart but can be customized to any extend. It can also pull data from order table, from abandon cart table and any additional tables as required.

Its of great help to quickly backup the OC data on your Google spreadsheet environment.

Please post your comments/queries here.

For customization work please contact: [email protected]