The best ways to connect Snowflake to Google Sheets

Introduction
Apps Script
Python
Using SeekWell to simplify querying
Snowflake External Function
Scheduling Tasks
SeekWell + Apps Script
Syncing your database with Sheets
Conclusion

Introduction

Google Sheets is often the go-to tool to quickly perform analysis, build charts, and manipulate data. But far too often, analysts will simply export or download the results of a query to copy data into Google Sheets. Getting data from your Snowflake database to Sheets doesn't have to be manual. Here are a few ways to send Snowflake data to Sheets automatically.

Apps Script

Sheets comes with built-in app development platform called "Apps Script" that can be used for things like custom menus, side bars, or even web apps. It also comes with a JDBC service that lets you connect to certain databases. While Apps Script doesn't currently support Snowflake, it does support Oracle databases, Mircrosoft SQL Server, and MySQL.

Python

Querying and sending data to Sheets is fairly painless with Python. The pandas library has built-in SQL support that make it easy to query data and save it as a pandas dataframe. Google also offers an API that makes sending a dataframe to Sheets straightforward.

To connect to your Snowflake database with Python, you need your JDBC driver connection string (a combination of username, password, and database) which will look something like:

jdbc:snowflake://<account_name>.snowflakecomputing.com/?<connection_params>

(These Snowflake docs can help you configure the driver.)

You likely don't want your database credentials in plain text so it's better to use an environment variable to retrieve the database URI or use key pair authentication and key rotation.

Here is some simple code that sets up the SQLAlchemy engine. You'll also need the pandas, SQLAlchemy, and googleapiclient Python packages for this code to work.

from sqlalchemy import create_engine
import pandas as pd
import os
SQLALCHEMY_DATABASE_URI = os.environ.get('SQLALCHEMY_DATABASE_URI')
engine = create_engine(SQLALCHEMY_DATABASE_URI)

Once you have your database engine, it's really easy to query and save it as a Pandas dataframe. You can do something like the code below:

sql = '''
SELECT * FROM public.users limit 5
'''
df = pd.read_sql(sql, engine)

Now it's time to send your dataframe to Google Sheets. One way to send it to Sheets is via the Google Analytics API. You'll have to create a service account, generate an API credentials JSON file, and save it to your computer.

(These steps are explained clearly in the Google Analytics documentation.)

Now you can gain access to Sheets with the following code, replacing with your own Google API credentials:

from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow

google_api_creds = 'Your_google_API_credentials.json'

SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
flow = InstalledAppFlow.from_client_secrets_file(
                google_api_creds, SCOPES)
creds = flow.run_local_server()
service = build('sheets', 'v4', credentials=creds)
sheet = service.spreadsheets()

A new window should open up which will ask you if you want to authorize access to your Google Account. Once you approve, you can get data from Sheets like below. You'll need the Spreadsheet ID (located in the URL of the Sheet), and name and range of the Sheet you want to access:

google_sheet_id = 'Your Google Sheet ID'
sheet_range = 'Your Sheet Range'


result = sheet.values().get(
    spreadsheetId=google_sheet_id,
    range=sheet_range,
    valueRenderOption='UNFORMATTED_VALUE'
).execute()

Then you can write your dataframe (df) to that Sheet by using the update() method as outlined below:

google_sheet_id = 'Your Google Sheet ID'
sheet_range = 'Your Sheet Range'

result = service.spreadsheets().values().update(
    spreadsheetId=google_sheet_id,
    valueInputOption='RAW',
    range=sheet_range,
    body=dict(
        majorDimension='ROWS',
        values=df.T.reset_index().T.values.tolist())
).execute()

And you've successfully sent your query result to the Sheet with Python!

Using SeekWell to simplify querying

SeekWell takes all the above Python code and turns it into an easy-to-use interface where you can write your query, select your destination Sheet, and set your data refresh schedule to daily, hourly, or even five minute intervals. You can run up to 500 queries a month on the free plan and schedule your queries to run automatically on the paid plans. It saves a ton of time and hassle for data folks. Try it here.

Snowflake External Function

If you don't want to connect to your Snowflake database through SeekWell or just prefer to stay in the Snowflake UI, you can use SeekWell's Snowflake external function. This allows you to send query results to Sheets (and elsewhere) right from Snowflake. To do so, just follow these steps in Snowflake.

Create integration

Create an integration with SeekWell's AWS API which allows you to create the function.

CREATE OR REPLACE api integration seekwell
  api_provider = aws_api_gateway
  api_aws_role_arn = 'arn:aws:iam::145125969827:role/snowflake-external-lambda-functions'
  enabled = true
  api_allowed_prefixes = ('https://yjq9k9mqh3.execute-api.us-east-1.amazonaws.com/snowflake-external-function-stage')
;

Verify integration

Verify the integration is successful by running this SQL statement.

DESCRIBE integration seekwell;

Create seekwell function

Create the seekwell function that will send your data to external apps (Sheets, Salesforce, Slack, etc.) from Snowflake. You will need to generate an API key in the SeekWell web app by hitting command / ctrl + k → "API" and insert it in the code below.

CREATE OR REPLACE external function public.seekwell(x object)
  returns variant
    headers = (
      'seekwell-api-key' = 'INSERT_YOUR_API_KEY'
  )
  api_integration = aws_lambda
  as 'https://yjq9k9mqh3.execute-api.us-east-1.amazonaws.com/snowflake-external-function-stage/seekwell'
;

Grant roles access to function

Finally you need to grant role access to the function so others can use it. You can do so with the following statement:

grant all privileges on function public.seekwell(object) to role analyst;

Now you can send Snowflake data to Sheets (and other apps) right from the Snowflake UI! Snowflake uses the concept of an "Object_Construct" which is essentially just a JSON object in Snowflake. The parameters you need to send data to Sheets are:

type - 'sheets'
ss_id - The spreadsheet ID (long unique ID in the Sheet URL)
sheet - The sheet / tab name within the spreadsheet (e.g. "Sheet1")
cell - The cell to start rows in (e.g. "A1")

With those parameters, you can send any query to Sheets. The example below is sending the result of the query "SELECT * FROM public.users limit 100" to a Sheet called "users".

with to_sheets as (
	SELECT
	u.*,
	OBJECT_CONSTRUCT(
	    'type', 'sheets',
	    'ss_id', 'INSERT_YOUR_SPREADSHEET',
	    'sheet', 'users',
	    'cell', 'a1',
	    'title', 'User Report'
	) as seekwell
	FROM public.users AS u
	LIMIT 100
)
select 
to_sheets.*,
public.seekwell(
  OBJECT_CONSTRUCT(to_sheets.*)
)::string as seekwell_result
from to_sheets as to_sheets

Scheduling Tasks

You can also schedule your data updates to run automatically using Snowflake's tasks so your data in Sheets is never stale. You can create a Task with your preferred schedule using an SQL statement like the following:

CREATE TASK mytask_hour
  WAREHOUSE = mywh
  SCHEDULE = 'USING CRON 0 9-17 * * SUN America/Los_Angeles'
  TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24'
AS
select 
to_sheets.*,
public.seekwell(
  OBJECT_CONSTRUCT(to_sheets.*)
)::string as seekwell_result
from (
	SELECT
	u.*,
	OBJECT_CONSTRUCT(
	    'type', 'sheets',
	    'ss_id', 'INSERT_YOUR_SPREADSHEET',
	    'sheet', 'users',
	    'cell', 'a1',
	    'title', 'User Report'
	) as seekwell
	FROM public.users AS u
	LIMIT 100
) as to_sheets

Runs every hour 9am - 5pm LA time on Sundays

The Snowflake external function gives users a lot of the functionality of SeekWell—like sending data to Sheets and other apps—without having to leave the Snowflake UI or make another database connection.

SeekWell + Apps Script

You can also use the SeekWell API in Apps Script to run queries straight from Sheets. As mentioned earlier, Apps Script doesn't support some databases, but SeekWell's API allows you to trigger a query to run programmatically using the database connection you already established in the SeekWell web app. SeekWell customers have used the API to trigger queries from Zapier, Airflow, and other applications.

(If you haven't connected to a database with SeekWell yet, you can do that here!)

The SeekWell API allows you to insert Parameters—such as dates and conditions to filter your query on—into a block when you trigger it. A Parameter can even be the query itself! To try this out, simply start a new block by clicking the green plus sign in the top right of the SeekWell web app, and type a parameter alias in double curly braces (e.g. {{myParam}}). Then set your destination to the Sheet you want to send the query result to. Finally, copy the block ID which can be found in the URL of your block (e.g. "app.seekwell.io/run/YOUR_BLOCK_ID").

While in the web app, you will also want to generate an API key, which you can do by hitting command/ctrl "k" → "API". Make sure to store your API key somewhere safe.

Now copy this script into the Sheets script editor (under "Tools" in the Sheets toolbar) with your API key and block ID:

function runApi(query) {
	var my_block_id =  'YOUR BLOCK ID';
	var my_api_key = 'YOUR API KEY';
	var data = {
      'blockId': my_block_id,
     'api_key': my_api_key,
     'parameters': [{'name': 'myParam', 'value':query}]
        }
	var options = {
	  'method' : 'post',
	  'contentType': 'application/json',
	  'payload' : JSON.stringify(data)
	};
var response = UrlFetchApp.fetch('https://api.seekwell.io/run_one', options);
}
function runSqlFromSheet() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var query = doc.getRange('query!A2').getDisplayValue();
    runApi(query);
}

function onOpen() {
    SpreadsheetApp.getUi()
        .createMenu('SeekWell')
        .addItem('Run SQL', 'runSqlFromSheet')
        .addToUi();
}

This code assumes you have a Sheet called "query" and will take the text from the A2 cell in that Sheet, run that query, and then send it back to whatever Sheet you entered in your block above.

Note: don't send the data to the same Sheet as your query as this will overwrite your query.

When you save and run this script you should see "SeekWell" in the toolbar with a dropdown button called "Run SQL". Click on it and your query will send data to the Sheet. Pretty easy, right!

If you already have a query, but want to be able to adjust it right from Sheets (for example, by changing the date interval of the query), you can add Parameters to your query in the SeekWell web app, and then send those from Sheets in a JSON payload to the API.

In this example, I have 2 parameters, toDate and fromDate, in my SeekWell block, and the code below takes the values from cells data!F2 and data!H2 and inserts them into my query when I post the API request.

  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var fromDate = doc.getRange('data!F2').getDisplayValue();	
	var toDate = doc.getRange('data!H2').getDisplayValue();

	var data = {
      'blockId': my_block_id,
     'api_key': my_api_key,
     'parameters': [{'name': 'toDate', 'value':toDate},
										{'name':'fromDate','value': fromDate}]
        }

⚠️

Remember to keep strict permissions in your Sheet and/or use a database user with restricted access (e.g. read only) to keep your database secure.

Now if you want to adjust a query, you don't have to leave Sheets and go through the process of re-sending the data. You can just tweak the query—or specific parameters of the query—and re-run the query right from within Sheets.

Syncing your database with Sheets

Connecting your database to Sheets doesn't have to be all one way! You can also sync Sheets back to your database in real-time using the SeekWell Sheets add-on. This is ideal if you have non-technical users who regularly update or add records to your database. It works with both updates and upserts (inserts + updates at the same time).

Follow these steps to get it set up and check out this video for a demo:

1. Create a new SQL block with Parameters. It's best to give the parameter the same name as the column.

update dummy.users 
set plan = '{{plan}}' 
where email = '{{email}}'

2. Add the form tag to your update block.

3. Launch the add-on.

4. Select the block created in step #1 (you should see "Form" to the right of it).

5. Click "Set up a sync" under the Run button.

6. Add the range that your data table is in, for example "Sheet1!A:G", and include the cell range using A1 notation. If the table in Sheets has different column names than your parameters, specify the names under each parameter name.

7. Click Save and you're done! You can test your sync by updating a value in the Sheet and confirming the write back to your database.

Conclusion

There are a lot of ways to send database data from Snowflake to Google Sheets, and the best one for you depends on your use case and preference.

In general, though, we've found customers who switch from do-it-yourself scripts to SaaS Apps (like SeekWell) spend a lot less time debugging, and a lot more time focusing on the data that matters.

Why not give SeekWell a go? You can start with our 14-day free trial.