This is a guest post from Mike Ritchie, co-founder of Seekwell.io, which adds SQL to the apps you need it in.

Google Sheets is great for quickly spinning up dashboards and analysis, but getting raw data into Sheets from databases can be tedious.

In this post we cover a few ways to get data from your SQL database into Google Sheets.

Google Sheets Database Connection With Apps Script

Sheets comes with a built-in 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.

Steps To Connect Google Sheets To A Database

1) Open the Script Editor in Sheets using "Tools" → "Script editor". Or just copy this Sheet here.

How To Connect Google Sheets To A Database
Access Apps Script under the menu Tools > Script editor

2) Replace "Code.gs" with the code here. (Skip this if you copied the Sheet above)

3) We included credentials for SeekWell's 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 to your active cell, so you'll need to add a sheet called "query" and add the query below in the cell query!A2 (skip if you copied the Sheet above).

SELECT *
FROM dummy.users
LIMIT 100

7) 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.gs file.

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.

And 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, Snowflake or RedShift and requires a long list of whitelisted IP's. It also doesn't support SSH.

Alternatives to Google Sheets database connections with App Script

Python

If you're comfortable with Python, you can put together a 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 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. ActionDesk did a good overview of the options out there.

This is a guest post written by Mike Ritchie. Mike is the co-founder of Seekwell and has over 15 years experience in analytics.

SeekWell features include:

  • Takes < 2 minutes to get your first schedule set up
  • A shared code repository with every query anyone on your team has ever written
  • Beautiful query editor with autocomplete and snippets
  • Ability to automate alerts via Slack and email
  • Support for MySQL, Postgres, Snowflake, Redshift, Salesforce, and SQL Server