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

You can write 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 in script Editor from here

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!!

Watch live script & demo

Please find below script:

function myMySQLFetchData()
{
var conn = Jdbc.getConnection('jdbc:mysql://127.0.0.1:3306/employee_db', 'username', 'pass');{codeBox}

Replace the place holder credentials for MySQL, Also change MySQL query as per your MySQL database.
host : 127.0.0.1
database: employee_db
username: username
pass: password

var stmt = conn.createStatement();
var start = new Date();{codeBox}

capture script starting time

var rs = stmt.executeQuery('SELECT id,emp_name, emp_code FROM employee_details GROUP BY 1 LIMIT 1000');{codeBox}  

It sets the limit of the maximum number of rows in a Result Set object
Change table name as per your database structure

var doc = SpreadsheetApp.getActiveSpreadsheet();{codeBox}

Returns the currently active spreadsheet, or you can hardcode this.

var cell = doc.getRange('a1');
var row = 0;
var getCount = rs.getMetaData().getColumnCount();{codeBox}

This code will count Mysql table column name.

for (var i = 0; i < getCount; i++)
{
cell.offset(row, i).setValue(rs.getMetaData().getColumnName(i+1));
}{codeBox}

MySQL table column name will be fetch and added in spreadsheet.

var row = 1;
while (rs.next())
{
for (var col = 0; col < rs.getMetaData().getColumnCount(); col++)
{
cell.offset(row, col).setValue(rs.getString(col + 1));{codeBox}

Mysql table column data will be fetch and added in spreadsheet.

}
row++;
}
rs.close();
stmt.close();
conn.close();
var end = new Date();{codeBox}

Get script ending time

Logger.log('Time elapsed: '+ (end.getTime() — start.getTime()));
}{codeBox}

To generate script log. To view log click on View -> Logs.

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.