Connecting your Google Sheet to an external MySQL database

Sat 23 2019

Connecting your Google Sheet to an external MySQL database

by bernt & torsten

I have been working on a Data Studio project that uses various data sources such as Google Sheet, BigQuery and an external MySQL database. During the project, I came across a few issues with data missing or a date being 0000-00-00 00:00:00 in the dataset, it was Data Studio that flagged a problem and gave an explanation to what was the cause of a data issue did not give the details of the issue.

While working with BigQuery or Google Sheet, I could easily query or filter data to detect the abnormality in the dataset, with the external MySQL database, it was not that easy. In data studio, I used a MySQL connector, and I created a custom query as I was not interested in the database tables as they reference each other, I just made a custom query with joins that gave me the data that I needed and could report on.

Data Studio

Making this connection with the custom query, I got all the fields I needed to build the the report in Data Studio.

As to my experience with Data Studio, there is no option, in which you could view the data in your field, in the report that you build you could use a table chart, and drop the fields into. It will show you the data, it is not that easy to navigate to track down potential abnormalities in your dataset, and under some conditions, an abnormality will no render the table chart. For instance, if you have a date field that is 0000-00-00 00:00:00

If you want to also test different custom queries, it’s not that easy from Data Studio, I should also mention that I did not have access to the external server that MySQL is running on or have access to any MySQL client tools like MySQLWorkbench as I was doing this work of my Chromebook.

I had to find another way to do this, I decide to use Apps Script in Google Sheet to connect to the external MySQL database, and pull the data into the Google Sheet. If you not familiar with Apps Script in Google Sheet, it’s a very powerful tool, if you are familiar with Excel, Apps Script is the Google Sheet option to Visual Basic in Excel.

Google Sheet

The first step was to create a Google Sheet and open the script editor.

The script editor looks like this when open

The Apps Script

To make the connection to the external MySQL and pull data using a SQL query, you can use this code.

// MySQL to Google Spreadsheet By Torbjorn Zetterlund
// Support and contact at torbjornzetterlund.com

function myMySQLFetchData() { 

    //Using a Google Cloud SQL instance  
    //var serverSslCertificate = PropertiesService.getScriptProperties().getProperty("serverSslCertificate");
    
    var serverSslCertificate = '-----BEGIN CERTIFICATE-----\n' +
    '-----END CERTIFICATE-----';
    
    var clientSslCertificate = '-----BEGIN CERTIFICATE-----\n' +
    '-----END CERTIFICATE-----';
    
    var clientSslKey = '-----BEGIN RSA PRIVATE KEY-----\n' +
    '-----END RSA PRIVATE KEY-----';
          
      var conn = Jdbc.getConnection('jdbc:mysql://xxxx.xxxx.xxxx.xxxxx:3306/<database name>?useSSL=true', { 
        user: '<MySQL Database Username>', 
        password: 'MySQL Database Password',
        _serverSslCertificate: serverSslCertificate,
        _clientSslCertificate: clientSslCertificate,
        _clientSslKey: clientSslKey 
      });
        
      var stmt = conn.createStatement();
      var start = new Date(); // Get script starting time
      
      var rs = stmt.executeQuery('SELECT t.id,\n' +
        't.Queue,\n' +
        't.Type,\n' +
        't.IssueStatement,\n' +
        't.Resolution,\n' +
        't.Owner,\n' +
        't.Subject,\n' +
        't.Status,\n' +
        't.Started,\n' +
        't.Resolved,\n' +
        't.Creator,\n' +
        't.Created,\n' +
        't.Disabled,\n' +
        't.IsMerged,\n' +
        'u.name as Username,\n' +
        'u.RealName,\n' +
        'u.Organization,\n' +
        'q.name as Queuename,\n' +
         'q.description,\n' +
        'ocfv.Content,\n' +
        'ocfv.ContentType\n' +
       'FROM Tickets t\n' +
            'INNER JOIN Users u\n' +
                'ON u.id = t.Owner\n' +
            'INNER JOIN Queues q\n' +
                'ON q.id = t.Queue\n' +
            'INNER JOIN ObjectCustomFieldValues ocfv\n' +
                'ON ocfv.ObjectId = t.id\n' +
             'WHERE t.Created >= "2018-01-01"');
      
// Google Sheets Details       
      var doc = SpreadsheetApp.getActiveSpreadsheet(); // Returns the currently active spreadsheet
      var cell = doc.getRange('a1');
      var row = 0;
      var getCount = rs.getMetaData().getColumnCount(); // Mysql table column name count.
      
      for (var i = 0; i < getCount; i++){  
         cell.offset(row, i).setValue(rs.getMetaData().getColumnName(i+1)); // 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)); // Mysql table column data will be fetch and added in spreadsheet.
        }
        row++;
      }
      
      rs.close();
      stmt.close();
      conn.close();
      var end = new Date(); // Get script ending time
      Logger.log('Time elapsed: ' + (end.getTime() - start.getTime())); // To generate script log. To view log click on View -> Logs.
    }

JDBC Connection Details

A few things that you have do in the code example, before it will work for you, the most important part is the use of JDBC to connect to a MySQL server. Apps Script takes full advantage of the JDBC to connect to external databases, it’s a wrapper around the standard Java Database Connectivity technology. In the code, the below snippet shows the connection parameters using JDBC.

      var conn = Jdbc.getConnection('jdbc:mysql://xxxx.xxxx.xxxx.xxxxx:3306/<database name>?useSSL=true', { 
        user: '<MySQL Database Username>', 
        password: 'MySQL Database Password',
        _serverSslCertificate: serverSslCertificate,
        _clientSslCertificate: clientSslCertificate,
        _clientSslKey: clientSslKey 
      });

You will need to change the xxxx.xxxx.xxxx.xxxx to the ip address or hostname of your external MySQL server. You will have to add you database name and the username and password for the external MySQL database.

If you are not using SSL – you can remove from the connection parameters ?useSSL=true and certificate details. It would look like this.

      var conn = Jdbc.getConnection('jdbc:mysql://xxxx.xxxx.xxxx.xxxxx:3306/<database name>', { 
        user: '<MySQL Database Username>', 
        password: 'MySQL Database Password'
      });

SSL Details

if you are using SSL you will need to add your external MySQL server certificate credentials and client certificate details, in these fields below.

    var serverSslCertificate = '-----BEGIN CERTIFICATE-----\n' +
    '-----END CERTIFICATE-----';
    
    var clientSslCertificate = '-----BEGIN CERTIFICATE-----\n' +
    '-----END CERTIFICATE-----';
    
    var clientSslKey = '-----BEGIN RSA PRIVATE KEY-----\n' +
    '-----END RSA PRIVATE KEY-----';

If your external MySQL server is hosted on the GCP, and are using SSL you should uncomment this line at the beginning.

var serverSslCertificate = PropertiesService.getScriptProperties().getProperty("serverSslCertificate");

and comment
//   var serverSslCertificate = '-----BEGIN CERTIFICATE-----\n' +
//    '-----END CERTIFICATE-----';

Query

Before you can run, you would need to change this code to match your table names and table structure.

 var rs = stmt.executeQuery('SELECT t.id,\n' +
        't.Queue,\n' +
        't.Type,\n' +
        't.IssueStatement,\n' +
        't.Resolution,\n' +
        't.Owner,\n' +
        't.Subject,\n' +
        't.Status,\n' +
        't.Started,\n' +
        't.Resolved,\n' +
        't.Creator,\n' +
        't.Created,\n' +
        't.Disabled,\n' +
        't.IsMerged,\n' +
        'u.name as Username,\n' +
        'u.RealName,\n' +
        'u.Organization,\n' +
        'q.name as Queuename,\n' +
         'q.description,\n' +
        'ocfv.Content,\n' +
        'ocfv.ContentType\n' +
       'FROM Tickets t\n' +
            'INNER JOIN Users u\n' +
                'ON u.id = t.Owner\n' +
            'INNER JOIN Queues q\n' +
                'ON q.id = t.Queue\n' +
            'INNER JOIN ObjectCustomFieldValues ocfv\n' +
                'ON ocfv.ObjectId = t.id\n' +
             'WHERE t.Created >= "2018-01-01"');

Running Script

Network connection, the complexity of your query and the size of your external database are some of the factors on how long it will take to execute your apps script. To run your script you can click either select the Run script from the menu bar

or click the arrow on the menu bar.

Trouble shooting

You may run into a connection error, if that happens you need to white-list the Google Apps Script IP addresses on your MyQL server. You can find the details of the range of IP addresses on Google Website at this location – https://developers.google.com/apps-script/guides/jdbc

Conclusion

I hope you enjoyed this instruction and that you got to connect to your external MySQL database, and was able to make queries that are stored in Google Sheet. For me, this was a great approach to getting data into a viewable sheet, in which I could identify any data abnormality.

As you can connect your Google Sheet with Data Studio, you could use this method of building your report, there are limitations with using Google Sheets if you using it for Data Studio reporting in which Google Sheet pulls data from an external MySQL server. https://gsuitetips.com/tips/sheets/google-spreadsheet-limitations/