4

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.

Steve Gon
  • 347
  • 6
  • 18
  • Google-apps-script runs on Google's cloud, so it can't connect to your localhost, nor can it connect to a private network range like 10.x.x.x. I'm not sure if it is even possible to connect locally (hence comment, not answer), but it would require some form of virtual networking between google's datacenter and your local network if it is possible at all. – Mark Rotteveel May 24 '18 at 10:03
  • This might help you. https://stackoverflow.com/questions/18592903/user-with-grant-option-cant-grant-create-user – bop-a-nator May 24 '18 at 14:16
  • Mark Rotteveel. Google's instructions make it sound like you CAN connect to any database using JDBC, which would enable a local connection https://developers.google.com/apps-script/guides/jdbc. I have also seen people posting online that they have done it successfully. – Steve Gon May 24 '18 at 20:21
  • Thanks bop-a-nator, but I get an error granting the grant option. Error Code: 1410. You are not allowed to create a user with GRANT – Steve Gon May 24 '18 at 20:23

2 Answers2

3

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

Austin O
  • 39
  • 10
1

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

Sebastián Duque
  • 11
  • 4