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.

Buy me a coffee

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

Buy me a coffee

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.

Please post your comments/queries here.

For customization work please contact: pradeepbheron.com