CODEWITHSUNDEEP
mysqljdbcgoogle-apps-script
Steve Gon
Steve Gon

Reputation: 462

Connect to local MySQL database from Google Apps Script

I am trying to connect a Google Apps Script to a local MySQL (8.0.11) database on MacOS. I have setup the DB, my.cnf file, installed MySQL Workbench. I have created a new db and can insert rows and query. However, when I try to connect in Apps Script, I keep getting the error:

Failed to establish a database connection. Check connection string, username and password.

Failing consistently at this line of code:

var conn = Jdbc.getConnection('jdbc:mysql://localhost:3306/mydb', 'root', 'pass');

I have tried a variants of the connection string, e.g.:

var conn = Jdbc.getConnection('jdbc:mysql://10.10.10.10:3306/mydb', 'root', 'pass');

I have confirmed use of port 3306. I have been unsuccessful at whitelisting Google's IP address, and consistently get syntax errors with:

GRANT ALL PRIVILEGES ON *.* TO 'root@64.%.%.%';

Getting error:

Error Code: 1410. You are not allowed to create a user with GRANT

What am I missing?

Edit: gave up and was successful using Python and a few manual steps. Works much faster than Google Apps Script.

Upvotes: 4

Views: 8034

Answers (2)

Austin O
Austin O

Reputation: 39

While your question is old, I think this answer of mine will help anyone still googling for a solution. Like everyone else, I added the whitelist IPs (https://www.gstatic.com/ipranges/goog.txt), made sure I was using MySQL 5.7, and all of the other solutions. The answer seems to be NOT using Google's syntax they provide in their JDBC documentation. I swapped all single quotes to double quotes and I can finally connect to my webserver's MySQL DB through Google Sheets Script Editor. Here's my connection syntax:

// Replace the variables in this block with real values.
var server = "123.456.789.0"
var port = "3306"
var db = "db_name"
var user = "user_name"
var pwd = "password"
var url = "jdbc:mysql://" + server+":"+port+"/"+db;

/**
 * Write one row of data to a table.
 */
function writeOneRecord() {
  var conn = Jdbc.getConnection(url, user, pwd);
  Logger.log(conn);

  var stmt = conn.prepareStatement('INSERT INTO test' + ' (test) values (?)');
  stmt.setString(1, 'test');
  stmt.execute();
  conn.close();

Full disclosure, I followed this video to figure this out: https://www.youtube.com/watch?v=npq2zRrPtP8

Upvotes: 3

Sebastián Duque
Sebastián Duque

Reputation: 11

Edit: Attending jnovack's comment, I edited this message to be more explicit.

MySQL database server only runs on the IP 127.0.0.1 or the hostname localhost by default. So, it is not accessible from other computers on the internet. In order to make MySQL accessible from other computers or the internet, you have to do a little bit of configuration.

If you want to expose MySQL to the internet, then you will need a public IP address. Otherwise, your server won’t able accessible from the internet.

The previous information (and other steps) can be found on: https://linuxhint.com/expose_mysql_server_internet/

Second approach

Another way to expose the MySQL service is to use an external service so you can expose the localhost to the web, like ngrok.

In this repo you will find the code necesary to pull this off: https://github.com/jenizar/googlesheet-to-mysqldb-local-pc

Upvotes: 1

Related Questions