CODEWITHSUNDEEP
mysqlgoogle-apps-scriptgoogle-sheets
maxhugen
maxhugen

Reputation: 1944

Connect Google Sheets to MySQL on Localhost

Just installed MySQL locally, and created a db and one simple table. I'm now trying to connect to it from Google Sheets, but it fails with err:

{ [Exception: Invalid argument: url] name: 'Exception' } undefined

Much googling later, it seems that because Sheets runs on google servers, it can't resolve localhost as a Host.

Is there a way around this?

var HOST      = 'localhost';
var PORT      = '3306';
var USERNAME  = 'xxxx';
var PASSWORD  = 'xxxx';
var DATABASE  = 'investment';
var DB_TYPE   = 'mysql';
var MAXROWS   = 1000;

function testConnection(){
    var sql = doc.getRange('query!a2').getDisplayValue();
    var options = {};
}
function runSql(query, options) {
    try {
        var fullConnectionString = 'jdbc:' + DB_TYPE + '://' + HOST + ':' + PORT;
        var conn = Jdbc.getConnection(fullConnectionString, USERNAME, PASSWORD);
        Logger.log('conn :', conn);
        Logger.log('query :', query);
        var stmt = conn.createStatement();
        stmt.execute('USE ' + DATABASE);
        var start = new Date();
        var stmt = conn.createStatement();
        stmt.setMaxRows(MAXROWS);
        var rs = stmt.executeQuery(query);
    } catch (e) {
        console.log(e, e.lineNumber);
        Browser.msgBox(e);
        return false;
    }
}

Just wondering, if I could find a way of getting my IPv4 address online (since it would be DHCP I think, so changing periodically), could I use Host such as 101.146.72.23/localhost? (not my real IP)

[Edit] I tried adding my IPv4 address as above, and maybe that took me a step closer, as now I get a different error:

{ [Exception: Failed to establish a database connection. Check connection string, username and password.] name: 'Exception' } undefined

While reading up on Google: JDBC, it states "In order to create a database connection using the JDBC service you must allow-list certain IP ranges in your database settings to allow Apps Script to access it. These are the address ranges you'll need to allow-list."

Now searching on how to do this in Windows 10.

[Edit 2] Reporting progress for anyone else who needs to do this. Following @Julian Benavides suggestions, I found a link on how to Add Port Forwarding on Telstra Smart Modem . This post pointed out that my pc would need a Static IP address (on the local network), at How to Setup a Static IP Address on a Computer.

I think I have made a successful connection, although now I have to figure out why I have error:

Exception: Table 'investment.investment_type' doesn't exist

Getting closer, but no cigar yet.

[Edit 3 - Success!] I had the wrong table name, now works!

Upvotes: 0

Views: 3847

Answers (2)

maxhugen
maxhugen

Reputation: 1944

A brief summary of how the issue was resolved - more info in the updates in the original post.

  1. The code I used was closely derived from Ben Collins - How To Connect Google Sheets To A Database, Using Apps Script
  2. @Julian Benavides pointed out that I needed to add Port Forwarding, found how to do that at Setup a Port Forward on the Telstra Smart Modem Gen 2 Router
  3. Had to assign a Static IP on my local network for the machine running MySQL, from How to Setup a Static IP Address

Hope this may be useful to another novice like me. :)

Upvotes: 1

Julian Benavides
Julian Benavides

Reputation: 330

Did you configure a port forwarding or DMZ access ?, if you are at home configure and do access to router or contact with your ISP, or if you are in the company contact with the network administrator.

With this way your ip could be resolve in internet and be accesed by jdbc connection.

Upvotes: 1

Related Questions