Connecting Google Spreadsheets to MySQL w/ JDBC

Question

I have been digging deep to try and find the answer here and on Google...hopefully someone can help.

What I am trying to do is connect to MySQL from a Google Spreadsheet script found here:

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

First I set up the server following these instructions:

https://help.ubuntu.com/community/JDBCAndMySQL

I get this error: "Failed to establish a database connection. Check connection string, username and password. (line 2, file "Code")Dismiss"

Line 2 looks like this:

var conn = Jdbc.getConnection("jdbc:mysql://65.65.65.65:3306/dbname", "user", "password")

Then I find out that there is a bug in the JDBC however it is due to Hostnames and the solution is to use the IP instead. I am already using the IP so I don't think this bug is

Mentioned here: Google Apps Script - JDBC Connection Failed

and here: What is the root error behind "Failed to establish a database connection. Check connection string, username and password."

Confirmed and detailed here: https://code.google.com/p/google-apps-script-issues/issues/detail?id=1856

.......now I don't believe my problem is with the above mentioned bug. I think its due to mysql being only accessible via SSH?

If that is the case then what I believe I need help with is modifying my code to connect over SSH.

Or maybe I am wrong and I need to explore making mysql public...but that just doesn't feel right.

Any and all thoughts are more than welcome.

Answer 1

Sigh...The solution was right in front of my face. I needed to grant the Google App Server IPs. Im sure you could add more code to connect over ssh and its more secure but this is fine for now.

Here are IP address range:

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

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

Answer 2

I know this was a firewall issue but I am sharing in case others want a free JDBC tool that is able to pull in data into your Google Spreadsheet. You are able to create queries and refresh your content all in one tool.

You can find it here. Google Spreadsheet JDBC Connector