There are several ways to connect Amazon Redshift to Google Sheets, depending on your goals. This post provides instructions for 3 ways of connecting Google Sheets to Redshift.
We also show how Row Zero is a better solution for connecting Amazon Redshift to a spreadsheet. Row Zero is an enterprise-grade spreadsheet that can handle much larger datasets and has a built-in Redshift connector that can connect in a few easy steps.
Table of Contents
- 3 Ways to Connect Amazon Redshift to Google Sheets
- Apps Script - A free, slightly complicated way of building a Google Sheets add-on that pulls data from Redshift into Google sheets.
- Download and Open a CSV -A simple, free process of exporting data from Redshift as a CSV and uploading it to Google Sheets.
- Coefficient - A Google Sheets add-on that connects your sheets to Redshift. It's easier, but expensive - $99/month for 5,000+ rows.
- Better Solution:
- Row Zero - A powerful spreadsheet with a built-in Redshift connector. Row Zero works like Google Sheets but can handle much larger datasets, much faster.
- Pros and Cons of Connecting Redshift to Google Sheets
3 Ways to Connect Redshift to Google Sheets
1. Apps Script
To connect Amazon Redshift to Google Sheets and import data directly into a spreadsheet, you can use Google Sheets Apps Script to create a custom function that connects to AWS Redshift via JDBC. Here's how you can achieve this:
1. Open Google Sheets:
- Open the Google Sheets workbook where you want to import your Redshift data.
2. Access Apps Script:
- Go to
Extensions>Apps Script.
3. Write Your Script:
- Copy and paste the following code into the Apps Script editor:
function connectToRedshift() {
// Replace with your Redshift details
var url = 'jdbc:redshift://<your-cluster-url>:<port>/<database>';
var user = '<your-username>';
var password = '<your-password>';
var query = 'SELECT * FROM <your-table> LIMIT 100'; // Customize the query
// Establish the connection
var conn = Jdbc.getConnection(url, user, password);
// Execute the query
var stmt = conn.createStatement();
var results = stmt.executeQuery(query);
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Get column metadata and add to the sheet
var numCols = results.getMetaData().getColumnCount();
var data = [];
// Add column headers
var headers = [];
for (var i = 1; i <= numCols; i++) {
headers.push(results.getMetaData().getColumnName(i));
}
data.push(headers);
// Fetch and add rows to the data array
while (results.next()) {
var row = [];
for (var i = 1; i <= numCols; i++) {
row.push(results.getString(i));
}
data.push(row);
}
// Write data to the sheet
sheet.getRange(1, 1, data.length, numCols).setValues(data);
// Close the connection
results.close();
stmt.close();
conn.close();
}
4. Replace Placeholders:
Update the following with your actual Redshift cluster details:
<your-cluster-url><port><database><your-username><your-password><your-table>
5. Authorize the Script:
- Save the script, run
connectToRedshift, and grant any necessary permissions when prompted.
Important Considerations:
- Security: Avoid hard-coding sensitive credentials. Use environment variables or a secure method to handle credentials if you need a production-grade solution.
- Redshift Access: Ensure that your Redshift instance is configured to accept connections from the necessary IPs, which may require modifying security groups or VPC settings.
- Data limits: Google Sheets has a data size limit of 10 million cells and an import limit of 100 MB. In practice, you may struggle to work with Redshift datasets above 100,000 rows. If you need to work with larger Redshift datasets, use Row Zero, a powerful Google Sheets alternative for enterprises and big data.
Note:
Google Apps Script includes Jdbc as a native service, so there is no need to enable it separately.
2. Download and Open a CSV
Opening a CSV extract from Redshift is the simplest way to get data from an Amazon Redshift database into Google Sheets. The downside is this method doesn't establish a live-connection and Google Sheets will not be able to open a very large CSV file.
- Run Query: In your Redshift database tool (e.g., SQL Workbench), run a query to select the data you want to export.
SELECT * FROM your_table;
- Export to CSV: After running the query, export the result set to a CSV file. You can typically do this from the query tool's interface. Ensure that the CSV file contains headers.
- Open Google Sheets
- In Google Sheets, go to File > Import > Upload and select your CSV file.
- The CSV will be loaded into a new or existing sheet.
Note, Google Sheets has a 100MB CSV import limit and may struggle to work with data above 100,000 rows. If you want to import a large CSV from Redshift, use Row Zero. Row Zero makes it easy to open and edit big CSV files. The free plan supports 5GB uploads and 10s of millions of rows and Enterprise plans can support billion row spreadsheets.
3. 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/month with the same 5000 row limit and the Pro plan is $99/user/month and has no import limit (but you will be limited by Google Sheets data limits). To use Coefficient to connect Redshift to Google Sheets, follow the steps below.
- 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.'
- Follow the steps to authenticate your Google account with Coefficient and give the add-on access.
- Launch the application by going to Extensions>Coefficient>Launch.
- Once launched, follow the steps to answer questions and select AWS Redshift as your import source
- Enter your database host, port, database name, username, password, and SSL.
- Once connected, you can use the visual query editor or write your own sql query and click import.
- The data will load into a Google sheet. The data in Google Sheets can be automatically updated every time Redshift data updates if you use a paid tier of Coefficient.
In addition to Coefficient, there are several additional Google Sheets add-ins that let you connect to Amazon Redshift. Go to Extensions > Add-Ons > Get Add-ons and search for 'Redshift' to compare options. Note that any add-in will still be limited by Google Sheets data size limits and may struggle above 100,000 rows.
If you need to work with big Redshift datasets or want a much less expensive solution, try Row Zero, which is designed for big data and has a built-in Redshift connector. The free plan lets you connect to Redshift and paid plans start at $10/month. Read more about using Row Zero as a Coefficient alternative.
Why Row Zero is a better solution for Redshift
Row Zero is an enterprise-grade spreadsheet designed for big data with a built-in Redshift connector. Row Zero works like Google Sheets but can handle significantly larger datasets and is much faster than Google Sheets with big data. Row Zero's native Redshift connection makes it easy to import Redshift data into a spreadsheet. To get started, watch the video below or follow the simple instructions to connect Redshift to Row Zero.
There are 3 steps to easily connect Redshift to Row Zero and analyze data in a spreadsheet. Follow the instructions below to get started.
- Connect Row Zero to your Redshift account: Click the 'Data' icon in the top right of your workbook, click 'Add connection', select Redshift, and enter your connection details.
- Write a query to import data: Click the '+' sign next to your Redshift connection to open a query editor. Write your query, click 'Run', and your Redshift data imports to a connected table.
- Build out your Redshift connected spreadsheet: Row Zero works like Google Sheets and Excel, just a lot more powerful. Everything you build on top of connected data stays in sync and dynamically updates, including pivot tables, charts, formula functions, etc. Here's more info on how to build connected spreadsheets.
Pros and cons of connecting Redshift to Google Sheets
Pros
Connecting Google Sheets to Amazon Redshift offers several advantages:
- Dynamic Redshift Dashboards and Analysis: With a live connection between Redshift and Google Sheets, you can build dynamic dashboards, charts, and models that update automatically as new data is added to Redshift.
- Redshift Data Visualization: Easily visualize Redshift data in Google Sheets with charts, pivot tables, etc.
- Familiar Spreadsheet Interface: Google Sheets provides a familiar spreadsheet interface for analyzing Redshift data. Connecting Google Sheets to Redshift allows business and operations teams to leverage governed, data warehouse data without the need for extensive training in complex BI tools.
By connecting Google Sheets to Redshift you can make big data more accessible across your organization and enable efficient, big data spreadsheet analysis.
Cons
While there are several advantages, there are also some drawbacks and limitations of connecting Redshift to Google Sheets including data and performance limitations, security risks, complexity, and costs.
Data and performance limits: Google Sheets isn't designed to work with big datasets so you will not be able to import large Redshift tables. Google Sheets has a 10 million cell limit and a file size limit of 100 MB. In practice, Google Sheets can slow down or crash above 100,000 rows, especially as you add formulas, formatting, pivot tables, etc.
Security risks: Connecting Google Sheets to Redshift production data, especially customer data, can pose a data security risk if access controls and restrictions are not put in place. If data security is a priority, consider using Row Zero to connect to Redshift. Row Zero is specifically designed for enterprise security and gives organizations advanced security controls and the option to restrict sharing, export, and copy/paste. Learn more about these enterprise security features here.
Complexity: Connecting Google Sheets to Redshift using Google Apps Script can be a challenge for non-technical folks. Some Google Sheets add-ins make the process easier, but also add another layer of cost and data governance considerations. If you need an easy way to connect Redshift to a secure spreadsheet, try Row Zero, which has a built-in Redshift connector and has the power to handle large Redshift datasets.
Cost: While Google Sheets add-ins offer an easier way to connect to Redshift data, they can be expensive. For example, Coefficient costs $99 per user per month if you want to import more than 5,000 rows.
Conclusion
There are multiple ways to connect Google Sheets to Redshift. For one-off tasks, CSV upload is easiest. For recurring reports and workflows, you'll want to set up a true Redshift connection using Apps Script or a Google Sheets add-in. If you need a more powerful and cost-effective solution, use Row Zero. Row Zero is an enterprise-grade spreadsheet that easily handles massive datasets well beyond Google Sheets limits. It's also significantly less expensive than Coefficient and other Redshift connector solutions.
If you use another cloud data warehouse or blob storage, Row Zero also connects to Snowflake, Databricks, Postgres, BigQuery, and S3.