How to Link Your SQL Queries to Excel or Google Sheets


How to Link Your SQL Queries to Excel or Google Sheets

Have you ever come up with such an idea?

Why would you do that?

The possibilities of spreadsheets are huge. There are plenty of ways to use them. And definitely using spreadsheets you can process and visualize data.

Spreadsheets serve as one of the main tools for the majority of companies in the world. 

Spreadsheets have the best user interface ever created. People are just getting it kind of naturally. 

It is easy to learn, easy to use, easy to implement, and easy to integrate. 

It is proved, in particular,  by Excel, one of the most successful software in history. According to Microsoft, Excel is used by every fifth adult person on earth.

Interestingly, there are currently many startups that consider spreadsheets to be their main competitor.

Let’s start

We can do it using zettaflow.tech.

First, we have to connect to the database. For our example, we may use the Airline On-Time Performance Database

Then we need to create a new SQL query (let’s name it the Chicago flights):

Zettaflow New SQL Query
Zettaflow SQL Statement

After running the query we are getting the following result:

Zettaflow Data Flow

Next, we need to create the Data URL (we are using the same name – the Chicago flights). Here we have links ready for sharing:

Zettaflow Links

We use the CSV URL

https://zettaflow.tech/r/nC1jR26cd642aHCGmyFVbY7Pp8mbRj1nIYmCSEQWPwB7Dhku.csv

to load the data into Excel, 

and the Google Sheets formula

=IMPORTDATA(“https://zettaflow.tech/r/nC1jR26cd642aHCGmyFVbY7Pp8mbRj1nIYmCSEQWPwB7Dhku.csv”)

to load the data into the Google Sheets.

See how it works: 

This is our CSV: 

Zettaflow CSV

We may open a worksheet in Excel and from the Data tab select Get External Data, and click on From Web. Further, we will need to paste the link, adjust the separator if required, and import the data:  

Zettaflow to Excel
Zettaflow to Excel 02
Zettaflow to Excel 03

Or we may want to open Google Sheets and simply paste the Google Sheets formula in the cell that we wish to:

Zettaflow to Google Sheets

Once done we may want to build say a bar chart based on the data:

Zettaflow Bar Chart

 

Sure we may use CSV URL for other tools, such as Jupyter:

Zettaflow to Jupyter

Why not share the result with other stakeholders using the basic features of Google Sheets or Excel?

Check this link: 

https://docs.google.com/spreadsheets/d/1VBEt1ANOBSWCmP_2-rq8Gyw9ZMbvZVhP5lvlw_6S7bs

We can click the menu button in Google Sheets in the upper right corner of the chart and publish our chart to the web in a graphical or interactive format:

Embedded Google Sheets Bar Chart

Embedded Google Sheets Bar Chart

Interactive Embedded Google Sheets Bar Chart

We may share our visualization via the link:

https://docs.google.com/spreadsheets/u/2/d/e/2PACX-1vQEuTOIL0mUmIXYI1fRJujkM5TbQ3-jaDQL8vG-GLEbbYT7s52VpCRLNqfRCTpgkTVLqFXoT2qmYJEw/pubchart?oid=140860641&format=interactive

Yourself as well as your business users may work with this data in the spreadsheets, or you may stick with SQL because amazingly both Excel and Google Sheets even in their free version support the Refresh function. 

Having all these why not try building a kind of real-time dashboard in the spreadsheets?! 

However, this is the subject for another post.