Row Zero is now in public beta

← Back

How To Connect Snowflake to Google Sheets

2023-11-02 // Nick End, Founder

Connecting Snowflake to Google Sheets can make it easy for business teams to pull data into a spreadsheet for analysis. The spreadsheet is often the prefered tool for building models and combining various data sets into one analysis. The following post covers the benefits and considerations when connecting Snowflake to Google Sheets and provides instructions for 3 different methods of connecting Google Sheets to Snowflake as well as instructions for connecting Snowflake to Row Zero, a blazingly fast spreadsheet designed for big data. Row Zero works just like Google Sheets but is easier to connect to Snowflake and better suited for processing big data sets from Snowflake. To get started now, watch the video below connect your Snowflake instance to Row Zero for free.

Contents

What is Snowflake?

Snowflake Computing is a cloud-based data warehousing platform that simplifies data storage and analysis. Unlike traditional data warehousing solutions, Snowflake operates on a cloud-native architecture, enabling organizations to seamlessly store, manage, and analyze vast amounts of data across multiple clouds without the constraints of hardware limitations. Snowflake utilizes a structured data storage model known as a "data lakehouse," which combines the benefits of data warehousing and data lakes. This architecture allows for separation of storage and compute, providing elasticity and scalability.

The benefits of connecting Snowflake to Google Sheets

  • Production Data - Connecting Google Sheets to Snowflake enables financial and accounting models to be built on product or operational data stored in Snowflake.
  • Live Models - Once Snowflake is connected to a spreadsheet, like google sheets, it is easy to re-run or refresh the snowflake query to pull the latest dataset into the spreadsheet without having to recreate another static analysis.
  • Dashboards - With a connected spreadsheet it is easy to turn a simple analysis into a dashboard. Simply refresh the queried data from Snowflake and all graphs, pivot tables, and spreadsheet models will update.

Considerations when connecting Snowflake to Google Sheets

  • Data Set Size - While Google sheets is a great tool it cannot support large data sets. Google sheets will often slow down or crash as data sets sizes reach tens of thousands or a hundred thousand, depending on the number of columns. For more information on the Google Sheets row limit, read our previous blog. It is common for data sets stored in Snowflake to be quite large. To import data from Snowflake to Google sheets, the Snowflake query will need to trim the data down to a size Google Sheets can manage or it will be better to use a more powerful spreadsheet, like Row Zero.
  • Cost - Some methods for connecting Snowflake to Google Sheets charge a monthly fee. Depending on the company's budget, paying for another service may or may not be acceptable
  • Complexity - Certain options for connecting Snowflake to Google Sheets are technically complicated and not easy to maintain. They may require writing code or regularly repeating a process every time the Snowflake data is updated.

Methods for connecting Snowflake to Google Sheets:

  1. Row Zero - A spreadsheet designed for big data with a free tier and built-in connection to Snowflake. Use it and share it just like Google Sheets but with larger data sets.
  2. CSV Upload - A simple, free process of exporting data from Snowflake and uploading it to Google Sheets
  3. App Script - A free, somewhat complicated way of building a Google Sheets add-on that pulls data from Snowflake into Google sheets.
  4. Coefficient - A Google Sheets add-on with an easy to use GUI that connects to Snowflake with a 5000 row limit on the free tier.
  5. Other Connectors - A number of other connector options with varying degrees of complexity and cost.

1. Row Zero

Row Zero is a blazingly fast spreadsheet designed for big data. Row Zero spreadsheets run in the cloud, making it easy to connect to data warehouses, like Snowflake, and import giant data sets. Once data is in Row Zero, users can filter/sort, pivot, graph, and use all the spreadsheet functions they are familiar with.

There are 3 steps to easily connect Snowflake to Row Zero and analyze data in the spreadsheet. Follow the instructions below to get started.

  1. Connect Row Zero to your Snowflake account
  2. Write a query to import data
  3. Begin analysis in Row Zero

1. Connect Row Zero to Snowflake

In Row Zero click on the 'data source' icon in the right-hand menu and then click the button for 'New data source.' New data source Add new connection

The connection requires 5 pieces of information defined below.

  • Name - This field can be anything that helps identify the data source being connected. (e.g. "Production Data" or "Product Metrics")
  • Description - (optional) Can be anything to help describe the data source
  • Account name - This is the snowflake account name. It can be found and copied from Snowflake as identified in the image below. When it is copied and pasted from Snowflake into Row Zero, it will paste with a decimal between the two alphanumeric identifiers. The decimal must be replaced by a hyphen ( - ). For example, "HW97234.DV88430" should be modified to "HW97234-DV88430" when pasted into Row Zero. See the image below. Snowflake account name
  • Username - The username used to log into your Snowflake account.
  • Password - The password used to log into your Snowflake account. Snowflake account information

Once all the information is entered, hit 'Test connection' to ensure the information is correct. If the test returns green, you will be asked to select the 'Warehouse' and 'Role' before you can '+ Add Source.' Snowflake add source

2. Write a query to import data

Now that Snowflake is connected to Row Zero, begin writing a query to pull data into the spreadsheet. First select the 'Database' and 'Schema' from the two drop downs. Write a query in the query editor. The easiest query to write is the 'select *' statement, which pulls in the entire table. Example:

select * from events

Row Zero is equipped to handle large data sets and will easily ingest the entirety of a database. Write more complicated queries to pull in various subsets of data stored in Snowflake. All results will be displayed in the 'connected data table' in the spreadsheet. Snowflake write query

3. Begin analysis in Row Zero

Once the data is loaded into the data table use all the features in Row Zero to analyze, filter/sort, pivot, and graph the data.

Snowflake query table gragh

2. CSV Upload

The CSV option is simple and free but is not an actual connection between Snowflake and Google Sheets and is a one-time data upload to Google Sheets that will need to be repeated every time the data in Snowflake changes. Follow the steps below for the CSV upload from Snowflake to Google Sheets.

  1. Log in to your Snowflake Account
  2. Create a new worksheet in Snowflake Create Snowflake Worksheet
  3. Write your query. 'Select * from tablename' is an easy way to pull in all data from a particular table. If SQL is a familiar language, write a more complex query to pull the data needed into Google Sheets.
  4. Choose the compute warehouse in the upper right.
  5. Click the blue run button in the upper right-hand corner. Execute Snowflake query
  6. Download results by clicking on the download button and selecting 'download as csv.' Snowflake results download
  7. Upload csv to Google Sheets. Use an existing account or create a new one. Go to the File>Import>Upload. Search for the csv file on your computer and upload it. If the file isn't too big, Google Sheets will open it in a matter of seconds and the analysis can begin. Upload Snowflake data
  8. Repeat the process if Snowflake data is updated.

3. App Script

Google Sheets provides an app development functionality within the product, called Apps Script. App Script allows Google Sheets users to build custom features within the product, like menus, sidebars, and other web based apps. App script also includes a native MYSQL protocol through a Java Database Connectivity (JDBC) service. This JDBC service enables connection to databases. The Apps Script + JDBC functionality can be used to connect a Snowflake database to Google Sheets. To connect Snowflake to Google Sheets using Apps Script, follow the instructions below.

  • Create a new App Script in your Google Sheet using the top menu and selecting Extensions > Apps Script Google Sheets Apps Script
  • When the app script window opens, it will look like the image below. Select and delete the sample 'function myfunction()' and begin using the code provided below. Make sure to replace the example values for your own values in the first code snippet below. blank app script window
  • Next, create a set of variables that identify the database address and login information to be used by the program. Replace the values in green with your own values.
//replace the variables in this block with real values as needed
var address = 'my.server.address:port'; // e.g. TF34701.us-west-2.snowflakecomputing.com:443 -> taken from Snowflake URL
var user = 'SQL_GATEWAY_USER'; // e.g. John
var userPwd = 'SQL_GATEWAY_PASSWORD'; // e.g. password12345
var db = 'CData Snowflake Sys'; // e.g. MyCompanyDB
 
var dbUrl = 'jdbc:mysql://' + address + '/' + db; //
  • Write a function that will appear in the Google Sheets menu bar and allow you to run in from the UI rather than code.
function onOpen() {
var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [
{name: 'Import Snowflake data to a sheet', functionName: 'connectToSnowflakeData'}
];
spreadsheet.addMenu('My Snowflake Data', menuItems);
}
  • Write a function that will identify the first empty row in a spreadsheet in which to start the import.
/*
* Finds the first empty row in a spreadsheet by scanning an array of columns
* @return The row number of the first empty row.
*/
function getFirstEmptyRowByColumnArray(spreadSheet, column) {
var column = spreadSheet.getRange(column + ":" + column);
var values = column.getValues(); // get all data in one call
var ct = 0;
while ( values[ct] && values[ct][0] != "" ) {
ct++;
}
return (ct+1);
}
  • Finally, write a function that will import and write Snowflake data to your Google Sheet.
/*
* Reads data in a specified Snowflake table and then imports it to the specified sheet in your workbook.
*    (If there is no speciiced Google Sheet, the function will create one.)
*/
function connectToSnowflakeData() {
var thisWorkbook = SpreadsheetApp.getActive();
 
//select a sheet and if it does not exist, create one.
var selectedSheet = Browser.inputBox('Which sheet would you like the data to post to?',Browser.Buttons.OK_CANCEL);
if (selectedSheet == 'cancel')
return;
 
if (thisWorkbook.getSheetByName(selectedSheet) == null)
thisWorkbook.insertSheet(selectedSheet);
var resultSheet = thisWorkbook.getSheetByName(selectedSheet);
var rowNum = 2;
 
//select a Snowflake 'table'
var table = Browser.inputBox('Which table would you like to pull data from?',Browser.Buttons.OK_CANCEL);
if (table == 'cancel')
return;
 
var conn = Jdbc.getConnection(dbUrl, user, userPwd);
 
//confirm that var table is a valid table/view
var dbMetaData = conn.getMetaData();
var tableSet = dbMetaData.getTables(null, null, table, null);
var validTable = false;
while (tableSet.next()) {
var tempTable = tableSet.getString(3);
if (table.toUpperCase() == tempTable.toUpperCase()){
table = tempTable;
validTable = true;
break;
}
}
tableSet.close();
if (!validTable) {
Browser.msgBox("Invalid table name: " + table, Browser.Buttons.OK);
return;
}
 
var stmt = conn.createStatement();
 
var results = stmt.executeQuery('SELECT * FROM ' + table);
var rsmd = results.getMetaData();
var numCols = rsmd.getColumnCount();
 
//if the sheet is empty, populate the first row with the headers
var firstEmptyRow = getFirstEmptyRowByColumnArray(resultSheet, "A");
if (firstEmptyRow == 1) {
//collect column names
var headers = new Array(new Array(numCols));
for (var col = 0; col < numCols; col++){
headers[0][col] = rsmd.getColumnName(col+1);
}
resultSheet.getRange(1, 1, headers.length, headers[0].length).setValues(headers);
} else {
rowNum = firstEmptyRow;
}
 
//write rows of Snowflake data to the sheet
var values = new Array(new Array(numCols));
while (results.next()) {
for (var col = 0; col < numCols; col++) {
values[0][col] = results.getString(col + 1);
}
resultSheet.getRange(rowNum, 1, 1, numCols).setValues(values);
rowNum++;
}
 
results.close();
stmt.close();
}

*The entire script can be found at the end of this blog post in the Appendix

  • Once all the code has been written or copied from this blog, save the project with the 'Save' icon on the top menu bar. Then run the program. You will be prompted to verify and give access to your Google Account. Save and Run Snowflake Import Extension
  • After running the program, you will see a 'Snowflake Data' menu option in your Google Sheet Snowflake Data Menu
  • When running the extension, you will first be asked which sheet should receive the imported data. Simply type the sheet name (e.g. 'Sheet9'). If no sheet with that name exists, a new sheet will be created. select sheet
  • A second question will ask which table should be imported. select table
  • After responding to the questions, the program will import your Snowflake data into the Google Sheet you selected. The script can be re-run every time new data needs to be imported from Postgres. Snowflake import complete

4. Coefficient

Coefficient is a Google Sheets add-on that makes it easy to connect the spreadsheet to a number of different applications. Coefficient offers a free plan that limits import to 5000 rows. The starter plan is $50/mo with the same 5000 row limit and the Pro plan is $99/user/mo and has no row limit on import. To use Coefficient to connect Snowflake to Google Sheets, follow the steps below.

  1. Install the Coefficient extension in Google Sheets. If starting from Coefficient's page, follow the instructions with the pop-up menu. If starting from your own Google Sheets workbook, use the top menu and click Extensions>Add-Ons>Get Add-ons. Search for 'Coefficient.' Select Coefficient from the search results and click 'Individual Install.' get add-ons
  2. Follow the steps to authenticate your Google account with Coefficent and give the add-on access.
  3. Launch the application by going to Extensions>Coefficient>Launch.
  4. Once launched, follow the steps to answer questions and select Snowflake as your import source
  5. Enter your database name, username, password, and an optional nickname. If your Snowflake database is behind a firewall or private network, you will need to whitelist the Coefficient IP addresses. coefficient UI
  6. Once connected, you can use the visual query editor or write your own sql query and click import.
  7. The data will load into a Google sheet. The data in Google Sheets can be automatically updated every time Snowflake data updates if you use a paid tier of Coefficient.
  8. Be cognisant of the query result size as Google sheets has a cell limit (rows x columns) of 10M cells. Google Sheets also has a file size limit of 100MB. There is a blog specifically focused on the Google Sheets row limit here. To work with large data sets in a spreadsheet, use Row Zero, a spreadsheet designed for big data.

5. Other Connectors

There are a few other connectors that can be used to connect Snowflake to Google Sheets. Some notable options are listed below but there are many options of varying costs and complexity. In this post we covered the most commonly used options.

  • AirOps - Requires use of their 3rd party UI to connect Snowflake and Google Sheets.
  • Polytomic - A fairly technical option for those familiar with SQL and/or Python
  • Hevo Data - Another application that requires use of their 3rd party portal to connect Snowflake and Google Sheets.

Conclusion

After reviewing the popular options for connecting Snowflake to Google Sheets, it is clear there are a number of different options for connecting. Some options, like CSV import, are simple and free. Others, like App Script, are more technically complex. Products like Coefficient make it easy but have row limits on data import and charge for auto-updating the connected data. Above all, the biggest challenge is that most data sets in Snowflake are rather large and Google Sheets struggles with large data sets. To get full utility out of connecting Snowflake to a spreadsheet, the best option is Row Zero, a blazingly fast spreadsheet designed for big data. Row Zero provides a simple UI to connect to Snowflake and can easily handle many millions of rows of data. Try Row Zero for free and connect Snowflake now.

Appendix

Entire App Script for connecing Snowflake to Google Sheets

//replace the variables in this block with real values as needed
var address = 'my.server.address:port'; // e.g. TF34701.us-west-2.snowflakecomputing.com:443 -> taken from Snowflake URL
var user = 'SQL_GATEWAY_USER'; // e.g. John
var userPwd = 'SQL_GATEWAY_PASSWORD'; // e.g. password12345
var db = 'CData Snowflake Sys'; // e.g. MyCompanyDB
 
var dbUrl = 'jdbc:mysql://' + address + '/' + db;

function onOpen() {
var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [
{name: 'Import Snowflake data to a sheet', functionName: 'connectToSnowflakeData'}
];
spreadsheet.addMenu('My Snowflake Data', menuItems);
}

/*
* Finds the first empty row in a spreadsheet by scanning an array of columns
* @return The row number of the first empty row.
*/
function getFirstEmptyRowByColumnArray(spreadSheet, column) {
var column = spreadSheet.getRange(column + ":" + column);
var values = column.getValues(); // get all data in one call
var ct = 0;
while ( values[ct] && values[ct][0] != "" ) {
ct++;
}
return (ct+1);
}

/*
* Reads data in a specified Snowflake table and then imports it to the specified sheet in your workbook.
*    (If there is no speciiced Google Sheet, the function will create one.)
*/
function connectToSnowflakeData() {
var thisWorkbook = SpreadsheetApp.getActive();
 
//select a sheet and if it does not exist, create one.
var selectedSheet = Browser.inputBox('Which sheet would you like the data to post to?',Browser.Buttons.OK_CANCEL);
if (selectedSheet == 'cancel')
return;
 
if (thisWorkbook.getSheetByName(selectedSheet) == null)
thisWorkbook.insertSheet(selectedSheet);
var resultSheet = thisWorkbook.getSheetByName(selectedSheet);
var rowNum = 2;
 
//select a Snowflake 'table'
var table = Browser.inputBox('Which table would you like to pull data from?',Browser.Buttons.OK_CANCEL);
if (table == 'cancel')
return;
 
var conn = Jdbc.getConnection(dbUrl, user, userPwd);
 
//confirm that var table is a valid table/view
var dbMetaData = conn.getMetaData();
var tableSet = dbMetaData.getTables(null, null, table, null);
var validTable = false;
while (tableSet.next()) {
var tempTable = tableSet.getString(3);
if (table.toUpperCase() == tempTable.toUpperCase()){
table = tempTable;
validTable = true;
break;
}
}
tableSet.close();
if (!validTable) {
Browser.msgBox("Invalid table name: " + table, Browser.Buttons.OK);
return;
}
 
var stmt = conn.createStatement();
 
var results = stmt.executeQuery('SELECT * FROM ' + table);
var rsmd = results.getMetaData();
var numCols = rsmd.getColumnCount();
 
//if the sheet is empty, populate the first row with the headers
var firstEmptyRow = getFirstEmptyRowByColumnArray(resultSheet, "A");
if (firstEmptyRow == 1) {
//collect column names
var headers = new Array(new Array(numCols));
for (var col = 0; col < numCols; col++){
headers[0][col] = rsmd.getColumnName(col+1);
}
resultSheet.getRange(1, 1, headers.length, headers[0].length).setValues(headers);
} else {
rowNum = firstEmptyRow;
}
 
//write rows of Snowflake data to the sheet
var values = new Array(new Array(numCols));
while (results.next()) {
for (var col = 0; col < numCols; col++) {
values[0][col] = results.getString(col + 1);
}
resultSheet.getRange(rowNum, 1, 1, numCols).setValues(values);
rowNum++;
}
 
results.close();
stmt.close();
}