Connect MySQL to Google Sheets: 2 ways to export your data

If you’ve ever tried to get MySQL data into Google Sheets, you’d probably agree that the process isn’t straightforward, especially if you have to deal with a large amount of data or combine data from sources other than MySQL.

That’s why in this article, we’ll show you two ways to export data from MySQL (or any other database, for that matter) to Google Sheets in a few clicks.

Fast forward:

• Google App Scripts, if you’re comfortable with scripting languages
• Supermetrics, if you’re looking for a code-free solution

Method 1: Connect MySQL to Google Sheets using Google App Scripts

First, if you’re comfortable with coding, you can use Google App Scripts to query data from your MySQL database.

Open a Google Sheet, navigate to ‘Extensions’, and select ‘App Scripts’.

Use this script:

function getDataFromDatabase() {
let conn = Jdbc.getConnection('jdbc:mysql://hostname:port/database', 'username', 'password');
let stmt = conn.createStatement();
let results = stmt.executeQuery('SELECT * FROM table');
let metaData = results.getMetaData();
let numCols = metaData.getColumnCount();
let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
for (let col = 0; col < numCols; col++) {
sheet.getRange(1, col + 1).setValue(metaData.getColumnName(col + 1));
}
let row = 2;
while (results.next()) {
for (let col = 0; col < numCols; col++) {
sheet.getRange(row, col + 1).setValue(results.getString(col + 1));
}
row++;
}
results.close();
stmt.close();
conn.close();
}

Replace the following values with your MySQL credentials:

• hostname:port >> your MySQL host name and port
• database >> your database’s name
• username >> your username
• password >> your password
• SELECT * FROM table >> the SQL query that retrieves the data you need

Once you’re happy with your script, run it.

It’s important to handle your credentials securely and avoid exposing sensitive data. If you’re not familiar with Google Apps Script, check out the Google Apps Script documentation.

The pros of using Google App Scripts to get MySQL data into Google Sheets are:

• You can edit the scripts according to your needs.
• It’s completely free.
• You can automate the entire process.

But, this method may not be for you if:

• You aren’t familiar with coding and scripting languages.
• You don’t want to spend too much time maintaining your scripts, especially when your data needs change and/or you want to get data from multiple sources.

After that, you’ll find Supermetrics under the ‘Add-ons’ menu.

Method 2: Automate data from MySQL or any database into Google Sheets with Supermetrics

To begin, you need to get the Supermetrics add-on for Google Sheets. You can do this in two ways:

1. Install the add-on directly from the Google Workspace Marketplace
2. Or, open a fresh Google Sheet, click ‘Add-ons’ → ‘Get add-ons,’ then search for Supermetrics

By installing the add-on, you’ll start your free 14-day Supermetrics trial.

After that, you’ll find Supermetrics under the ‘Add-ons’ menu.

Next, we’ll use the Rfam public database — a database of non-coding RNA families — as an example to show you how to get your data to Google Sheets.

Let’s launch the Supermetrics sidebar. Click ‘Extensions’ → ‘Supermetrics’ → ‘Launch sidebar.’

When the sidebar opens, you need to build a query.

• Data source: Database
• DB type: here, you’ll find a list of different databases, including

Step 2: Export MySQL data to Google Sheets

Next, we’ll use the Rfam public database — a database of non-coding RNA families — as an example to show you how to get your data to Google Sheets.

Let’s launch the Supermetrics sidebar. Click ‘Add-ons’ → ‘Supermetrics’ → ‘Launch sidebar.’

When the sidebar opens, you need to build a query.

• Data source: Database.
• DB type: here, you’ll find a list of different databases, including
  • MariaDB
  • MySQL
  • Oracle
  • PostgreSQL
  • SQL Server
  • Amazon RDS Aurora
  • Amazon RDS MariaDB
  • Amazon RDS MySQL
  • Amazon RDS Oracle
  • Amazon RDS PostgreSQL
  • Amazon RDS SQL Server
  • Amazon Redshift
  • Google Cloud SQL for MySQL
  • Microsoft Azure MySQL

In this case, we’ll select MySQL.

Next up, fill in the following information:

• DB address (or host)
• DB port
• DB name
• Username
• Password

Check the ‘SSL’ box if your database requires Secure Sockets Layer (SSL).

Remember my Rfam example? My query looks like this.

Once you’re done, click ‘Test connection’ to connect to your database.

• Database query
  • Query SQL: here, you need to write the SQL query you want to run. 

Back to my example. Let’s say I want to pull all snoRNA families found in mammals. I’ll use this query:

SELECT fr.rfam_acc, fr.rfamseq_acc, fr.seq_start, fr.seq_end, f.type
FROM full_region fr, rfamseq rf, taxonomy tx, family f
WHERE
rf.ncbi_id = tx.ncbi_id
AND f.rfam_acc = fr.rfam_acc
AND fr.rfamseq_acc = rf.rfamseq_acc
AND tx.tax_string LIKE '%Mammalia%'
AND f.type LIKE '%snoRNA%'
AND is_significant = 1 -- exclude low-scoring matches from the same clan

Then, click ‘Get data to table’ and wait for Supermetrics to load your data to Google Sheets.

Here’s what my spreadsheet looks like.

With Supermetrics, you can automatically pull data from multiple sources into a single spreadsheet. Once you’ve got your data to Google Sheets with Supermetrics, you don’t have to worry about updating your data manually ever again.

Click’ Schedule’ on your sidebar to set up an automatic data refresh.

Next, click ‘Add trigger.’

From here, you can choose how often you want to update your data and if you’d like to receive an email notification when that happens.

Supermetrics is a great option if you:

• Don’t have any coding skills
• Want to automate the process
• Want to get data from multiple sources other than MySQL

But, Supermetrics may not be the right option for you if:

• You prefer a free solution.
• You only want to do a one-off data transfer from your database to Google Sheets.

The final verdict: Which option is right for you?

The easiest way to decide which option is right for you is by looking at your own use case and skills.

If you’re familiar with coding and just want to get MySQL data to Google Sheets for an ad-hoc or one-off analysis, then you can use Google App Script. But if you also want to get data from multiple marketing sources into Google Sheets and build customized reports, then Supermetrics is the best way to go.

If you want to take Supermetrics for a test drive, start your 14-day free trial of Supermetrics for Google Sheets.