Google Sheets MySQL Dashboard

UPDATE: This is one of my old posts imported to Medium — There may have been breaking changes since I wrote this.

If you have ever had the need to create some kind of reporting dashboard to display figures, traffic data or financials then the following solution may be of interest to you. The following will show you how to create a dynamic spreadsheet on google drive which is connected to your servers MySQL database although you could use in conjunction with a Microsoft SQL database also as we use the java based JDBC connector.

So once you get a connection established you can then pretty much execute any SQL query you would like to and have it populate the spreadsheet cells with the values — I have previously built quite an extensive one which would execute every night via a cron job and populate with the current days takings then collate the sum at the end of the week and also do a comparative each day for the takings on that same day the year prior as well as take note of all staff clock in times and also send this in an email at 3am to all management. So if you let your imagination run wild you can do some pretty cool things with it. I will say that it is best not to do it on your production server as you will need to have your database open to external connections.

You will need:

Linux Server of some sort (Can also be done of windows but Im not covering that),
MySQL Server installed,
Gmail Account/ Familiarity with Google Drive,
MySQL world test database imported (Download it here)

Prep the server

Your going to need to open MySQL up to external connections by editing the MySQL configuration file which on my Ubuntu or Debian is located here:

sudo nano /etc/mysql/my.cnf

Now look for a line like the following as comment it out by preceding it with #

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address = 127.0.0.1

Then restart the MySQL server

sudo service mysql restart

Now Import the world test database into your server by your preferred means.

Once thats done log into mysql then create a new SQL user and grant them full privileges on the world database, be sure to have them assigned to use any IP by using the ‘%’ wildcard rather than just ‘localhost’:

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE USER 'GDriveUser'@'%' IDENTIFIED BY 'reallyshitpassword';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL ON world.* TO 'GDriveUser'@'%' IDENTIFIED BY 'reallyshitpassword';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Set up the Google Drive Dashboard

This is pretty simple just log into your Gmail, then navigate to GoogleDrive and create a spreadsheet:

When the sheet opens up navigate to tools and then to script editor:

Then select Spreadsheet from the list:

When the script editor loads select all the code that’s already there and delete it, We wont be needing it for this. The following snipped I have prepared is using the JDBC driver to connect to our remote database — All you need to do is insert the connection parameters that pertain to your server:

function DriveDashboard() {
// Replace the variables in this block with real values.
var ServerIP = '168.23.52.81';
var SQL_Port = '3306';
var SQL_Usr = 'GDriveUser';
var SQL_Pwd = 'reallyshitpassword';
var SQL_DB = 'world';
var connectorInstance = 'jdbc:mysql://' + ServerIP+':'+SQL_Port;
var ConnectString = connectorInstance+'/'+SQL_DB;
var conn = Jdbc.getConnection(ConnectString, SQL_Usr, SQL_Pwd);
}

Press save and give your script a name when prompted then you will see what looks like a play button click it or else click “Run” in the toolbar and it will start to execute the connection script where you will be asked to grant various permissions to Google Drive before execution, this is noramal so if you are happy to grant the permissions do so and see if your script connects successfully — If id doesnt you will see a red bar with the error notification, if it connects you wont see anything.

Next up we need to set these initiation variables to be used throughout the script. The first one is used throughout when executing queries and the other 2 are used to get the active spreadsheet and then set it.

//Executes the queries
var execStmt = conn.createStatement();
//Gets and Sets the Current Spreadsheet as Active
var doc = SpreadsheetApp.getActiveSpreadsheet();
doc.setActiveSheet(doc.getSheetByName('Sheet1'));

Next up we can start requesting any data that we want, the first type of query is useful when you expect only one cell to be filled — I use this when I am getting a total for finances etc, basically it works great when you use a COUNT(*) query.

/* Count all records in the City Table */
var mysqlQuery = execStmt.executeQuery("select COUNT(*) From `City`");
var docRange = doc.getRange('A2'); //assign the value to cell A1
while(mysqlQuery.next()) {
docRange.setValue(mysqlQuery.getString(1));
}

Next up this query is the standard when you expect to get lists of results from the database. check out the line I have commented where you specify 2 fields for this record — Its important to set this in correlation to what your query will output:

// if you expect to ouput more than one record use this method
var mysqlQuery = execStmt.executeQuery("SELECT Name, Language FROM CountryLanguage, Country WHERE countrycode=code AND language = 'Swedish'");
var cell = doc.getRange('D2');
// loop through result object, setting cell values to database data
var row = 0;
while(mysqlQuery.next()) {
  for(var i=0; i<2; i++) { // 2 fields per record
cell.offset(row, i).setValue(mysqlQuery.getString(i+1));
}
row++;
}

The following is the same structure as the previous query but with 3 fields expected:

// if you expect to output more than one record use this method
var mysqlQuery = execStmt.executeQuery("SELECT Name, Language, Percentage FROM CountryLanguage, Country WHERE countrycode=code ORDER BY Name, Percentage");
var cell = doc.getRange('G2');
// loop through result object, setting cell values to database data
var row = 0;
while(mysqlQuery.next()) {
for(var i=0; i<3; i++) { // 3 fields per record
cell.offset(row, i).setValue(mysqlQuery.getString(i+1));
}
row++;
}

So this should give you all you need to start building up your dashboard — at a later date I may add another tutorial about how to display data from a stored procedure if its requested, for the entire script click here and don’t forget to close your function with the following:

mysqlQuery.close();
execStmt.close();
conn.close();

The Dashboard Script should give this output: