Back to Blog

The Best Way to Connect MySQL to Google Sheets Explained

Do you store your valuable information in MySQL and need to share a chunk of it with stakeholders? Or maybe, you’d like to create a custom self-updating analytical dashboard in Google Sheets based on your data. There could be many other reasons to connect MySQL to Google Sheets. So, in this article, we collected and described the most actionable ways you can do this. 

How you can import MySQL to Google Sheets

Еhere are two major options to get data from MySQL to a Google spreadsheet:

  1. You can export data as a CSV file using an SQL administrator tool like DBeaver or phpMyAdmin. Then you can upload it to Google Sheets.
  2. You can synchronize MySQL and Google Sheets so that the data will go directly from your database to your spreadsheet. The connection can be set up either with coding, say via AppScript, or with a third-party MySQL integration.

We claim that the second option is best since it reduces much of the manual routine. You can connect MySQL to Google Sheets and get your data either instantly or on a custom schedule. 

However, this does not mean that the manual exporting option is useless. We’ll cover all of these starting with the best one, of course, so that you can make the right choice for your needs. 

Connect MySQL to Google Sheets without coding to export data on a schedule

Let’s start with the no-code MySQL integration solution provided by Coupler.io. 

Coupler.io is a data integration solution to automate exports from multiple sources to Google Sheets, Microsoft Excel, or Google BigQuery.

Sign up to Coupler.io (you can do this with your Google account), click Add importer, then select MySQL as a source app and Google Sheets as a destination app.

1 mysql to google sheets

Then proceed to the connection of these apps.

Source

  • Click Connect to create a connection between your MySQL account and Coupler.io. For this, you’ll need to specify the following parameters:
    • Host
    • Port
    • User
    • Password
2 connect mysql

Note: Ask your MySQL database administrator for the connection parameters if you don’t know them.

  • Once your connection is set up, specify the name of the MySQL database and the table you want to load data from.
3 mysql database table to google sheets

Destination 

  • Connect your Google account. Then select a spreadsheet and a sheet to load your MySQL data.
4 mysql destination google sheets

Optionally, you can change the cell address or range for your exported data, or even the import mode. Read more about these options in the Coupler.io documentation.

Schedule

The last step is to schedule your exports from MySQL to Google Sheets. For example, you can have your data refreshed in the spreadsheet every hour or even more frequently. For this, toggle on the Automatic data refresh and specify the desired schedule.

12. Coupler.io schedule

Eventually, click Save and Run to launch your integration and export MySQL to Google Sheets. All the data from the specified MySQL table will be loaded into your spreadsheet.

5 mysql data in google sheets

It took us less than 2 minutes to connect MySQL to Google Sheets and another 2 minutes to load data… there were more than 2,000 rows in our database table. The spreadsheet will refresh automatically, meaning it will always have up-to-date information that we can share with stakeholders.

The no-code option provided by Coupler.io is awesome, but if you want to have some fun, you can import MySQL to Google Sheets using coding. For instance, let’s use Apps Script to do this.

Sync MySQL to Google Sheets with Apps Script

We assume that you know what Google Apps Script is and how it works. If you don’t, feel free to check out our Google Apps Script Tutorial and get back here to learn how to connect MySQL to Google Sheets using it.

Open the Apps Script – you’ll find it in the Extensions menu of your spreadsheet. 

6 apps script

Then you’ll need to write a script that will connect Google Sheets to your MySQL database and load data from it. Therefore, the essential part of this script should contain the following lines:

var HOST = '[your-host-name]'
var PORT = '[your-port]'
var USERNAME = '[your-username]'
var PASSWORD = '[your-password]'
var DATABASE = '[your-MySQL-database-name]'
var DB_TYPE = 'mysql'

Then you’ll need to create a function or functions that will extract a query from MySQL and load the results to the chosen or active sheet. In our example, the function runsqlfromsheet() will take the query from a separate sheet, query!A1.

function runsqlfromsheet() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var sql = doc.getRange('query!a1').getDisplayValue();
    var options = {}
    Logger.log('sql;', sql)
    runSql(sql, options)
}

Don’t forget to specify your query as we did:

7.0 apps script mysql query

The runsql function will load the data to the active cell of the sheet named mysql

function runsql(query, options) {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = doc.getSheetByName('mysql');
    var sheetName = sheet.getName();
    var cell = doc.getActiveSheet().getActiveCell();
    var activeCellRow = cell.getRow();
    var activeCellCol = cell.getColumn();
    try {
        var fullConnectionString = 'jdbc:' + DB_TYPE + '://' + HOST + ':' + PORT
        var conn = Jdbc.getConnection(fullConnectionString, USERNAME, PASSWORD);
        console.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
    }
    var results = [];
    cols = rs.getMetaData();
    console.log("cols", cols)
    var colNames = [];
    var colTypes = {};
    for (i = 1; i <= cols.getColumnCount(); i++) {
        var colName = cols.getColumnLabel(i)
        colTypes[colName] = { type: cols.getColumnTypeName(i), loc: i }
        colNames.push(colName);
    }
    var rowCount = 1;
    results.push(colNames);
    while (rs.next()) {
        curRow = rs.getMetaData();
        rowData = [];
        for (i = 1; i <= curRow.getColumnCount(); i++) {
            rowData.push(rs.getString(i));
        }
        results.push(rowData);
        rowCount++;
    }
    rs.close();
    stmt.close();
    conn.close();
    console.log('results', results)
    var colCount = results[0].length
    var rowCount = results.length
    var comment = "Updated on: " + (new Date()) + "\n" + "Query:\n" + query
    if (options.omitColumnNames) {
        results = results.slice(1)
        rowCount -= 1
    }
    if (options.clearColumns && sheet.getLastRow() > 0) {
        var startCellRange = sheet.getRange(startCell)
        sheet.getRange(startCellRange.getRow(), startCellRange.getColumn(), sheet.getLastRow(), colCount).clearContent();
    }
    if (options.clearSheet) {
        var startCellRange = sheet.getRange(startCell)
        sheet.clear({ contentsOnly: true });
    }
    sheet.getRange(activeCellRow, activeCellCol, rowCount, colCount).clearContent();
    sheet.getRange(activeCellRow, activeCellCol, rowCount, colCount).setValues(results);
    var cell = sheet.getRange(activeCellRow, activeCellCol)
    cell.clearNote()
    cell.setNote(comment);
    sheet.setActiveRange(sheet.getRange(activeCellRow + rowCount + 1, activeCellCol))
    console.log('query success!, rows = ', rowCount - 1)
}

Optionally, we want to add a separate menu with a button to run this script to import MySQL to Google Sheets. The following function will do this:

function launch() {
    var html = HtmlService.createHtmlOutputFromFile('sidebar')
        .setTitle('MySQLToGSheets');
    SpreadsheetApp.getUi()
        .showSidebar(html);
}

Having all these functions and parameters together, we have the following script that you need to insert into the Apps Script file:

var MAXROWS = 100 //we limited the number of rows for this example import
var HOST = '[your-host-name]'
var PORT = '[your-port]'
var USERNAME = '[your-username]'
var PASSWORD = '[your-password]'
var DATABASE = '[your-MySQL-database-name]'
var DB_TYPE = 'mysql'

function goToSheet(sheetName) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    ss.setActiveSheet(ss.getSheetByName(sheetName));
};

function runSql(query, options) {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = doc.getSheetByName('mysql');
    var sheetName = sheet.getName();
    var cell = doc.getActiveSheet().getActiveCell();
    var activeCellRow = cell.getRow();
    var activeCellCol = cell.getColumn();
    try {
        var fullConnectionString = 'jdbc:' + DB_TYPE + '://' + HOST + ':' + PORT
        var conn = Jdbc.getConnection(fullConnectionString, USERNAME, PASSWORD);
        console.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
    }
    var results = [];
    cols = rs.getMetaData();
    console.log("cols", cols)
    var colNames = [];
    var colTypes = {};
    for (i = 1; i <= cols.getColumnCount(); i++) {
        var colName = cols.getColumnLabel(i)
        colTypes[colName] = { type: cols.getColumnTypeName(i), loc: i }
        colNames.push(colName);
    }
    var rowCount = 1;
    results.push(colNames);
    while (rs.next()) {
        curRow = rs.getMetaData();
        rowData = [];
        for (i = 1; i <= curRow.getColumnCount(); i++) {
            rowData.push(rs.getString(i));
        }
        results.push(rowData);
        rowCount++;
    }
    rs.close();
    stmt.close();
    conn.close();
    console.log('results', results)
    var colCount = results[0].length
    var rowCount = results.length
    var comment = "Updated on: " + (new Date()) + "\n" + "Query:\n" + query
    if (options.omitColumnNames) {
        results = results.slice(1)
        rowCount -= 1
    }
    if (options.clearColumns && sheet.getLastRow() > 0) {
        var startCellRange = sheet.getRange(startCell)
        sheet.getRange(startCellRange.getRow(), startCellRange.getColumn(), sheet.getLastRow(), colCount).clearContent();
    }
    if (options.clearSheet) {
        var startCellRange = sheet.getRange(startCell)
        sheet.clear({ contentsOnly: true });
    }
    sheet.getRange(activeCellRow, activeCellCol, rowCount, colCount).clearContent();
    sheet.getRange(activeCellRow, activeCellCol, rowCount, colCount).setValues(results);
    var cell = sheet.getRange(activeCellRow, activeCellCol)
    cell.clearNote()
    cell.setNote(comment);
    sheet.setActiveRange(sheet.getRange(activeCellRow + rowCount + 1, activeCellCol))
    console.log('query success!, rows = ', rowCount - 1)
}

function runSqlFromSheet() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var sql = doc.getRange('query!a1').getDisplayValue();
    var options = {}
    Logger.log('sql;', sql)
    runSql(sql, options)
}

function onOpen() {
    SpreadsheetApp.getUi()
        .createMenu('MySQL to Google Sheets')
        .addItem('Import data', 'runSqlFromSheet')
        .addToUi();
}

function launch() {
    var html = HtmlService.createHtmlOutputFromFile('sidebar')
        .setTitle('MySQLToGSheets');
    SpreadsheetApp.getUi()
        .showSidebar(html);
}

Click Save project and get back to your spreadsheet. 

7.1 apps script mysql save project

You will see a new menu with a button to launch your MySQL to Google Sheets integration.

7.2 apps script mysql

Click on it, and in a few moments (or minutes if you’re loading thousands of rows), you’ll see the data imported to the active cell.

7.3 mysql google sheets results

You can also automate exports of data from MySQL to Google Sheets using Apps Script triggers. We’ve blogged about this in a separate section of our tutorial, so check it out.

What are the drawbacks of connecting MySQL to Google Sheets in Apps Script? 

Our script contains more than 100 lines and this is long enough. Of course, you can simplify some functions and make the script shorter, but, anyway, you will need Apps Script knowledge to write it.

Another drawback is that not all data types exported from MySQL are recognized properly in Google Sheets. Here is the comparison of the data exported with the MySQL integration by Coupler.io and the Apps Script.

8 mysql google sheets apps script vs coupler

So, you see how it goes. Now, let’s check out another option to get your data from MySQL to Google Sheets. This time without coding or automation.

Export MySQL to Google Sheets with an SQL client software

This method to export MySQL to Google Sheets involves an SQL client software application like Beekeper, DBeaver, phpMyAdmin, etc. The idea is quite simple:

  1. You export a query in CSV format.
  2. You upload the exported file to Google Sheets.

In most cases, you can create a query or select the necessary data range in your MySQL database table and export it using your SQL administrator tool. For example, this is what it looks like in DBeaver.

9 export mysql from sql client

Then, you’ll need to upload the export file to Google Sheets:

  • Open a Google Sheets file, then go to File => Import.
  • Click Upload and select your CSV file with MySQL data. 
  • Complete the next steps to get your MySQL data to Google Sheets. That’s it.

Which method you should choose to connect MySQL to Google Sheets

The connection between MySQL and Google Sheets means that the data will frequently move from your database to your spreadsheet. In this case, the MySQL connection by Coupler.io seems to be the most efficient method. It requires no coding skills and you can connect MySQL to Google Sheets in less than 2 minutes.

The code-based method using Apps Script or another programming language is trickier since you’ll need to actually code the integration between MySQL and Google Sheets from scratch. This will take more than 2 minutes and dozens of code lines. 

The third option only allows you to export MySQL to Google Sheets rather than connect them. You will need to manually export data from your database as CSV and upload it to Google Sheets every now and then. 

So, make a wise choice to meet your goals. Good luck with your data!

Back to Blog

Comments are closed.

Focus on your business
goals while we take care of your data!

Try Coupler.io