Introduction
Google Sheets is great for quickly spinning up dashboards and analysis, but getting raw data into Sheets can be tedious. In this post we cover a few ways to get data from your MySQL database into Google Sheets.
App Script
Sheets comes with built-in a app development platform called "Apps Script". Based on JavaScript, it covers a lot of the tasks you'd use VBA for in Excel. App Script comes with a JDBC Service that lets you connect to MySQL, Microsoft SQL Server, and Oracle databases.
1) Open the Script Editor in Sheets using "Tools" → "Script editor". Or just copy this Sheet here.
2) Replace "Code.gs" with the code here. (Skip this if you copied the Sheet above)
3) We included credentials for our demo MySQL database. To connect to your database, replace the six fields below. Note you'll need to whitelist Google's IP addresses.
var HOST = 'yourhostname'
var PORT = '3306 or your port'
var USERNAME = 'yourusername'
var PASSWORD = 'yourpassword'
var DATABASE = 'youdatabasename'
var DB_TYPE = 'mysql or your type'4) You might also want to change the MAXROWS, but you don't go too crazy, Sheets has a hard limit of 5 million cells and the query will take longer to run with more rows.
5) Save the file and refresh / refresh the Sheet. You'll see a new menu option of "SeekWell Lite" show up.
6) The script is set up to read the query from query!A2 and write the results your active cell, so you'll need to add a sheet called "query" and add the query below in cell query!A2 (skip if you copied the Sheet above).
SELECT *
FROM dummy.users
LIMIT 1007) Go back to Sheet1, click in cell C4 (or any other cell) and click "SeekWell Lite" → "Run SQL". In a few moments you'll see the data show up!
A few problems with this approach
- You need to store your password in plain text in the
Code.gsfile - Sharing the script with your team and adding the script to different Sheets is a bit of a pain. You can publish an addon, but that comes with some overhead.
- Scheduling / automating refreshes can be cumbersome when you need many different queries going to many different Sheets
- Google's JDBC service doesn't work for Postgres or Snowflake, RedShift and requires a long list of whitelisted IP's. It also doesn't support SSH.
Alternatives
Python
If you're comfortable with Python, you can put together program using Pandas and the Sheets API. Pandas has great SQL support built in.
SaaS Products
A lot of people hate paying for things they can do for free, but if you should always do some "napkin math" when making the "build vs. buy" decision. In the case of automating reports, the ROI can be pretty high, especially if you have several daily, hourly, or near real time dashboards you need to keep updated. I'm clearly biased towards SeekWell since we made it, so here's our pitch:
- Takes < 90 seconds to get your first schedule set up
- A shared code repository with every query anyone else on your team has ever written
- Beautiful query editor, powerful autocomplete and snippets
- You can query other Sheets, CSVs
- We also automate alerts via Slack and email
- Support for MySQL, Postgres, Snowflake, Redshift, and SQL Server