Home » Blogs » TPS2015's blog » 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
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:
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.
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
create-spreadsheet.jpg
Step -2 Create a new spreadsheet and insert the script provided in below link.
https://docs.google.com/document/d/1iA3284ZrSEAGdwJQeb5dJXLRTkECMb52z4Kw...
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.
close-the-popup.jpg
On closing pop-up you will also find some pre-written function tags. Remove them completely
remove-pre-written-function-tags.jpg
Paste the script provided in script Editor.
script.jpg
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
mysql_JDBC_connection_error.jpg
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]