Featured

    Featured Posts

  • Relation Between Games and Studies
  • Best Screen Protector for Nintendo Switch Available So Far
  • Gamer Problems: Then vs. Now [Comic]
  • Facebook Messenger May Soon Get Reactions, Dislike Button
  • What Happens When You Stop Smoking?

How To Pull and Sync Data Between Google Doc Spreadsheet and MySQL

You can pull data from MySQL database to Google doc spreadsheet auto with just click of a button or scheduled time. This is great use for retrieving data in spreadsheet format.

Step 1: Create a spreadsheet within your Google Drive.
Step 2: Insert script in newly created spreadsheet.
  • How to insert script
Step 2.1: To insert the script in Google spreadsheet go to Tools > Script Editor.
Step 2.2: On opening script editor you will find a Google Apps Script pop-up Close it.
 Step 2.3: On closing pop-up you will also find some pre-written function tags. Remove them completely.

Step 2.4: Paste the script code provided below in script Editor.

Step 3: Adding your MySQL database credentials and correct table names

Jdbc.getConnection('jdbc:mysql://127.0.0.1:3306/employee_db', 'username', 'pass')

Step 3.1: Replace the place holder credentials (host, database, user, pass) for MySQL, Also change MySQL query as per your MySQL database.

Step 4: Run the Script.
Step 4.1: Run the script from top menu "Run" and it will pull data from your database.
Note: It will ask for script authorization on first run accept.

You are all set to run the script!!

 Script will take some time to pull data from database depending on connection speed and data size.  

Additional Step: Connection error & White listing Google IP’s
Script from Google Docs connects to your 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.

Below is the list of Google IP’s you need to white-list. More details:
  • 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 simple query but can be customized to any extend. It can also pull data from other tables.
 

From Author: Please contact for custom script and queries at pradeepbheron@gmail.com

www.CodeNirvana.in

Copyright © New Techie