Read the following blog for a comprehensive guide on how to export MySQL data into Google Sheets.
However, many business users prefer to leverage MySQL data in spreadsheets, particularly Google Sheets. But the process for exporting data from MySQL into Sheet isn’t always straightforward. Read on to learn how to export MySQL data into Google Sheets quickly and easily.
Why Export MySQL Data into Google Sheets?
MySQL databases often contain data that is valuable to business users. But since business users do not have SQL programming skills, they cannot easily extract the data they need from MySQL.
That’s why business users typically rely on data engineers to extract MySQL data. And once they have the MySQL data they need, business users often pull the data into the analysis tool they prefer: spreadsheets.
Exporting data from MySQL and importing it into Google Sheets offers several benefits:
- Powerful data analysis. Google Sheets provides powerful data analysis tools, including a wide-variety of pre-built functions, that allows you to easily manipulate, analyze, and report on your MySQL data.
- Easy data visualization. With Google Sheets’ chart and graph options, you can create visual representations of your MySQL data in a few clicks.
- Centralized data storage. Importing your MySQL data into Google Sheets with your other business data lets you centralize your data storage and access it from one place. For instance, you can export data from Salesforce, Amazon Redshift, and MySQL, and analyze it all in a single Google spreadsheet.
- Seamless data sharing. You can collaborate in Google Sheets with team members, clients, and stakeholders. Google Sheets lets you set permissions to control who can view or edit your data.
- Enhanced data accuracy. Importing data directly from MySQL into Google Sheets helps reduce errors and ensures that your data is accurate and reliable.
These are just some of the benefits of exporting MySQL data into Google Sheets — they will depend on your use case.
Export MySQL Data into Google Sheets: Top 3 Methods
1. Coefficient
Coefficient enables you to import your business data from many platforms, databases, and data warehouses into Google Sheets in just a few clicks. The Coefficient Google Sheets add-on is available for free in Google Workspace Marketplace.
These steps will show you how to connect MySQL database to Google Sheets using Coefficient for seamless data exporting, importing, and syncing.
First, open a new Google spreadsheet and navigate to Extensions on the top menu, then Add-ons and Get add-ons.
Enter “Coefficient” in the search box on the Google Workspace Marketplace popup window.
Click on Coefficient in the search results. Go through the prompts and follow the steps to complete the installation process.
Once installed, return to the Google Sheets Extensions tab and launch Coefficient. It will run as a side panel on the right hand side of your screen.
Select the Import from… option in the Coefficient side panel.
Scroll down and select MySQL as your data source. Click Connect.
Enter the required information in the designated fields.
Now choose a data extraction method: Import from tables or Custom SQL Query. For this example, let’s import from a MySQL table.
You should see the Data Preview window with all your MySQL tables. Click on the table you want to import.
To include only the data you want, you can check or uncheck the corresponding boxes of each field.
You can choose to add filters, sorting options, and limits, or group your data in a cloud pivot table.
Then click Import. Your data will auto-populate in your Google spreadsheet instantly.
Now set up an auto-refresh schedule to automatically update your import data whenever your source data changes.
This way, you won’t need to update your import dataset manually, ensuring you always work with the latest and most accurate data.
Coefficient provides a hassle-free way to integrate Google Sheets and MySQL, allowing you to import data quickly and effortlessly. Now you can eliminate tons of manual work by streamlining and automating your data importing.
2. Google Apps Script
You can also use Google Sheets’ built-in app development platform, Google Apps Script, to export data from your MySQL database and import it into Sheets.
Google Apps Script has a Java Database Connectivity (JDBC) service that connects to databases such as MySQL, Oracle, and Microsoft SQL Server.
To set up your MySQL database to Google Sheets connection, start by opening the Script Editor from Google Sheets.
Go to Extensions > Apps Script.
Copy and paste the code below into the Google Apps Script interface:
/**
* @OnlyCurrentDoc
*/
var MAXROWS = 1000
var (Replace this) = { day: "yyyy-MM-dd", month: "yyyy-MM", year: "yyyy", dayNum: "dd", monthNum: "MM", yearNum: "yyyy", week: "W" }
var (Your Time zone) = "UTC"
var HOST = '35.196.130.133'
var PORT = '3306'
var USERNAME = 'apps_script_demo'
var PASSWORD = 'jkSqXKKa2Em7UBCh'
var DATABASE = 'dummy'
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.getActiveSheet();
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!a2').getDisplayValue();
var options = {}
Logger.log('sql;', sql)
runSql(sql, options)
}
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Replace this')
.addItem('Run SQL', 'runSqlFromSheet')
.addToUi();
}
function launch() {
var html = HtmlService.createHtmlOutputFromFile('sidebar')
.setTitle('Replace this');
SpreadsheetApp.getUi()
.showSidebar(html);
}Whitelist Google’s IP addresses and remember to replace these fields:
You may also consider changing the MAXROWS accordingly. Try not to go overboard since Google Sheets has a limit of ten million cells, and your query can run slower if you have more rows.
Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule.
Save the file and refresh your sheet. You should now see a new menu option with your function’s name.
On your sheet, click on an empty cell, select the new menu option, then Run SQL.
Voila! Your data should appear in your Google spreadsheet after a slight delay.
While this approach gets your MySQL data into Google Sheets, it has several limitations.
You’ll need to store your password (in plain text) in your Code.gs file, which can be a pain to share with your team.
Scheduling and automating data refreshes can also be tedious since you’ll need to write SQL queries in separate sheets.
Plus, not all your team members may have the technical knowledge and coding skills to create, run, and maintain your scripts.
3. Comma Separated Values (CSV File)
MySQL lets you export a query’s result into a CSV file within the database server. The prerequisites for exporting data include the following:
- Ensure that the target CSV file must not currently exist
- The MySQL server’s process must have write access to the target folder containing the target CSV file
The sample query below selects canceled orders from an Orders table in MySQL:
SELECT
orderNumber, status, orderDate, requiredDate, comments
FROM
orders
WHERE
status = 'Cancelled';Add clauses to the query above to export the result into a CSV file format like this:
SELECT
orderNumber, status, orderDate, requiredDate, comments
FROM
orders
WHERE
status = 'Cancelled'
INTO OUTFILE 'C:/tmp/cancelled_orders.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY 'rn';The statement should create a CSV file named cancelled_orders.csv within the C:tmp folder containing the result set.
The file has lines of rows in the result set. A carriage return terminates every line, including a line feed character specified by the LINES TERMINATED BY ‘rn’ clause sequence.
Download the CSV file to your local drive, upload it to Google Sheets, and you should be good to go.
While exporting MySQL data to a CSV file is pretty straightforward, there’s no guarantee that your data will stay intact.
For instance, formatting and data compatibility issues could distort or change the original dataset after uploading your exported CSV file to Google Sheets.
Resources you will also love
- How to Export Data from Tableau
- How to Export Data from PostgreSQL
- How to Export Data from Looker
- How to Export Data from Zendesk
Export MySQL Data from Google Sheets Like a Pro
Connecting Google Sheets and exporting MySQL data is easy with the right tools. Follow the steps outlined in this guide to easily export your MySQL data to Google Sheets so you can analyze and visualize it in a more accessible format.
The quickest and simplest way to get your MySQL data into Google Sheets is through Coefficient.
Coefficient removes the complexities of importing, exporting, and syncing live MySQL data with Google Sheets.
Try Coefficient for free to pull your MySQL database into Google Sheets with a few clicks.
