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.
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 10 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 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.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
Exception: Connection URL uses an unsupported JDBC protocol.
getting above error
me too, did you find a solution already?
Can you share your JDBC string? Make sure you replace the actual password, username, etc. with PASSWORD
Hi Mike,
I am receiving the same error as well : Exception: Connection URL uses an unsupported JDBC protocol.
Below is my connection string:
var MAXROWS = 1000
var SEEKWELL_J_SHORT_DATES = { day: “yyyy-MM-dd”, month: “yyyy-MM”, year: “yyyy”, dayNum: “dd”, monthNum: “MM”, yearNum: “yyyy”, week: “W” }
var SEEKWELL_J_TIMEZONE = “UTC”
var HOST = ‘MyHost’
var PORT = ‘1433’
var USERNAME = ‘some user name’
var PASSWORD = ‘my_password’
var DATABASE = ‘MyDatabase’
var DB_TYPE = ‘Microsoft SQL Server’
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
}
It looks like your DB_TYPE should be “sqlserver”
Hi,
Fristly, thank you for this post!
Can you share you blog for web aaps with google sheet connector.
Correct!
Use the below link. it working for me.
https://expertofphp.blogspot.com/2020/10/mysql-data-get-and-show-in-google-sheet.html
Can I use instead host ip with localhost?
I don’t think you would be able to use localhost, because the google script runs server-side and so it doesn’t see your local machine.
Error Range is not defined
Hello,
I am using localhost but I am getting “Exception: Invalid argument: url” error.
Below is the connection string:
var MAXROWS = 1000
var SEEKWELL_J_SHORT_DATES = { day: “yyyy-MM-dd”, month: “yyyy-MM”, year: “yyyy”, dayNum: “dd”, monthNum: “MM”, yearNum: “yyyy”, week: “W” }
var SEEKWELL_J_TIMEZONE = “UTC”
var HOST = ‘localhost’
var PORT = ‘3306’
var USERNAME = ‘root’
var PASSWORD = ‘ ‘
var DATABASE = ‘test’
var DB_TYPE = ‘mysql’
Is this possible with a Netezza IBM server?
“SeekWell Lite” menu does not appear.
It works with sql server?
¿Funciona con un ibm db2? Quiero conectar una hoja de google sheets con una base de datos db2 de ibm.
Hi Ben,
The script run properly.
But I have a problem with the currency.
My values are imported in duration format and so not as currency.
I am located in Italy.
Are there any Google-based options for a database (Data Studio, Google Cloud, or BigQuery) that might serve as a repository for data? I have a client who is setting up cost information for about 40 suppliers, each supplier has their own Google Sheet on which they report cost changes. The 40 sheets contain information for over 6,000 products. The sheets are protected so the supplier cannot change to add new products on the cost change tab. Once a change is submitted, the client sets a new retail price. Once the retail is set, the new cost must be updated in the “database” so that the spreadsheet is updated for the next month’s changes. I am currently running a script to update each line in the product cost tab, but it is quite slow as it loops through each record. I would much rather push the changes to a database and allow the spreadsheet to be updated when refreshed. Any thoughts would be greatly appreciated.