Redshift Google Sheets Integration: 2 Easy Methods

on Activate, Amazon Redshift, Data Warehouses, Google sheets, Hevo Activate, Python, Tutorials • March 29th, 2022 • Write for Hevo

Data is a goldmine today. But are you leveraging it sufficiently to get maximum value? Google sheets are popular today, and Amazon Redshift is a cloud-based, fully managed data warehousing solution. Have you thought about the redshift google sheets connection? 

Redshift is a data storage and analysis platform utilized by some of the world’s major corporations, including Ford Motor Company and Lyft. Flexibility, scalability, high performance, and effective data compression are all aspects of the data warehouse.

Google Sheets is convenient and makes it easy to run simple analyses. However, it does not allow for the execution of complicated calculations. It can be challenging to gain a single source of truth for all your data. You will prefer a Redshift Google Sheets integration that can give the best of both worlds. Read along to learn 2 easy methods to connect these tools!

Table of Contents

Introduction to Amazon Redshift

Redshift Google Sheets: Amazon Redshift Logo
Image Source

Amazon Redshift is a scalable and robust Cloud-based Data Warehouse that is designed to act as a solution to your Big Data storage troubles. This popular storage offers high processing power and security for your data and allows companies to manage petabytes of data within just a few clicks. Moreover, all conventional forms of cloud-native, containerized serverless web services-based applications and event-driven ones can easily access data using Amazon Redshift. The Amazon Redshift Data API makes data access, intake, and egress easier from AWS SDK-supported programming languages and platforms like Python, Go, Java, Node.js, PHP, Ruby, and C++. 

The Data API eliminates the requirement for driver configuration and database connections to be managed. Instead, you may use the Data API to access a secured API endpoint to perform SQL queries on an Amazon Redshift cluster.

Key Features of Amazon Redshift

Some of the key features of Redshift are as follows:

  • Column-oriented databases: Data can get organized either into rows or columns.
  • Massively parallel processing (MPP): We have a distributed design approach where several processors handle large data jobs with a divide and act approach.
  • End-to-end data encryption: Encryption options in Redshift are customizable and robust.
  • Network isolation: Administrators can isolate their network within Redshift
  • Fault tolerance: Redshift continues functioning in case of issues as Redshift can automatically re-replicate data and shift the data to healthier nodes.
  • Concurrency limits: Concurrency refers to the maximum number of nodes or clusters that a user can provision at any point in time. Redshift is flexible as it configures limits based on regions instead of applying a single limit to all users.

To learn more about Amazon Redshift, visit here.

Introduction to Google Sheets

Redshift Google Sheets: Google Sheets Logo
Image Source

Google Sheets is the most sophisticated data storage and organization tool ever created. It’s more than just a spreadsheet program; it can also be used as a database for all your company processes, allowing you to arrange data intelligently. Consider automating your day-to-day operations with Google Sheets if you want to automate your procedures and enhance your productivity. You can also combine it with the hundreds of tools you already use and enhance flexibility. 

Key Features of Google Sheets

Google Sheets offer the following key features:

  • Rich Visualizations: Google Sheets allows you to make charts, display data, construct pivot tables, and color-code your spreadsheet.
  • Easy Functionality: It allows users to open, modify, save, and export spreadsheets and document files. It accepts a variety of spreadsheet file formats and types. 
  • Offline Services: Offline editing is available in Google Sheets, and users may modify the spreadsheet on their computers or mobile devices. 
  • Communication: A sidebar chats feature in Google Sheets will enable colleagues to debate adjustments in real-time and give recommendations on specific changes. 
  • Revision history: The Revision History tool allows you to trace any changes made by your partners. An editor can go back over previous modifications and undo any changes they don’t like.

To learn more about Google Sheets, visit here.

Methods to Set Up the Redshift Google Sheets Integration

Method 1: Redshift Google Sheets Integration Using Custom Code

In your python script, you can use the gspread library, which is the python API for Google Sheets. You can read, write, and format spreadsheets with it. The script’s goal is to take data from Amazon Redshift, apply specific business rules, and then output it to a Google spreadsheet.

Method 2: Redshift Google Sheets Integration Using Hevo Activate

Hevo Activate provides a hassle-free solution and helps you directly Connect Redshift to Google Sheets without any intervention in an effortless manner. Hevo Activate is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Hevo’s pre-built integration with various data sources such as Amazon Redshift, Snowflake, Salesforce, HubSpot, etc., will take full charge of the data transfer process, allowing you to focus on key business activities. It helps transfer data from Redshift to a destination of your choice for free.

Methods to Set Up the Redshift Google Sheets Integration

You can set up the Redshift Google Sheets Integration and transfer your data between these tools using the following 2 methods:

Method 1: Redshift Google Sheets Integration Using Custom Code

In this section, you will learn about connecting Redshift to Google Spreadsheet using Python. 

The first step in interacting with Google API is to establish a project in Google Development Console and enable the APIs. Open the GCP Dashboard and click on Create project to get started.

Redshift Google Sheets: Graphical user interface
Image Source

Name the project

Redshift Google Sheets: GCP Project Name
Image Source

Next, select +ENABLE APIS AND SERVICES from the project dashboard.

Redshift Google Sheets: APIs & Services
Image Source

Look for Google Drive API, click on it, and turn it on. To activate the Google spreadsheet API, perform the following steps:

Redshift Google Sheets: Test Project
Image Source

Enable the Google Drive API and the Google Sheets API as shown in the below image.

Redshift Google Sheets: Google Sheets API
Image Source

Create credentials by clicking the following button:

Redshift Google Sheets: Creating Credentials
Image Source

Choose the parameters and then choose what credentials do I require.

Redshift Google Sheets:Selecting Credentials
Image Source

Select Role and Service Account and provide a Service Account Name.

Redshift Google Sheets:Service Account Detail
Image Source

Now go to KEYS and make a new JSON-formatted key.:

Redshift Google Sheets: Create Test Key
Image Source

There will be a downloaded JSON file. You will need this JSON file in your script. Rename the file client_secretkey.json.

Create a Google Sheet called redshift-to-Google-Sheet in your Google Drive now. Copy the client email value from the JSON file you downloaded earlier and send it to this client email in a Google Sheet with edit permission.

Now that you have configured everything, you can construct your Python script.

To use a Python script to communicate with a spreadsheet, you must first install the gspread Python package. Oauth2client is a Python package for gaining access to OAuth 2.0-protected sites. Open a terminal and execute the following commands.

pip install gspread
pip install oauth2client

Make a new Python file called redshift_to_spradsheet.py. and paste the code below into it.

import gspread
from oauth2client.service_account import ServiceAccountCredentialsscope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/spreadsheets",
         "https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/drive"]credentials = ServiceAccountCredentials.from_json_keyfile_name('client_secretkey.json', scope)
client = gspread.authorize(credentials)

Now add the code that will connect to the Redshift database. The sqlalchemy library makes it easier for Python programs to communicate with databases. To install sqlalchemy type the following:

pip install sqlalchemy

Now import sqlalchemy into your python script and use create the engine to create a database connection. After the connection string, write a select query (or any other DML) to get the data and put it in a Pandas DataFrame.

from sqlalchemy import  create_engine
import pandas as pd# Connecting to postgresql
engine = create_engine('postgresql://user:password@host:5439/database')
data_frame = pd.read_sql('SELECT * FROM article_sales;', engine)

Installing the df2gspread python library is required to write this data frame to a Google spreadsheet. Type the following to install df2gspread:

pip install df2gspread

Import df2gspread into your Python script and construct a write google sheet method as follows:

from df2gspread import df2gspread as d2gdef write_googlesheet(df, spreadsheet_key, sheet_title, starting_cell, overwrite):
    d2g.upload(df, spreadsheet_key, sheet_title, credentials=credentials, col_names=overwrite, row_names=True, start_cell = starting_cell, clean=overwrite)

This d2g.upload method produces a gspread Worksheet object after uploading a Pandas DataFrame to Google Drive. In this case, supply you are supplying the spreadsheet key from the spreadsheet’s URL and the sheet title as the spreadsheet’s name (i.e., Sheet1, Sheet2).

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd
from sqlalchemy import create_engine
from df2gspread import df2gspread as d2g
scope = ["https://spreadsheets.google.com/feeds", 'https://www.googleapis.com/auth/spreadsheets',
"https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/drive"]
def write_googlesheet(df, spreadsheet_key, sheet_title, starting_cell, overwrite):
d2g.upload(df, spreadsheet_key, sheet_title, credentials=credentials, col_names=overwrite, row_names=True, start_cell = starting_cell, clean=overwrite)
if _name__ == "__main__":
engine = create_engine('postgresql://user:password@host:5439/database')
data_frame = pd.read_sql('SELECT * FROM article_sales;', engine)
print(data_frame)
credentials = ServiceAccountCredentials.from_json_keyfile_name('client_secretkey.json', scope)
client = gspread.authorize(credentials)
write_googlesheet(data_frame, '1MogvCK7mTPYv2Mmeapyq_D3hnOvlSynfUyADEBpL3F8', 'Sheet1', 'A1', True)

Now open the redshift-to-Google-Sheet Google Sheet in your browser after running the Python script. You’ll see the updated Google Sheet with the new information.

That’s it! The custom code is complete and Redshift Google Sheets integration is in place.

Method 2: Redshift Google Sheets Integration Using Hevo Activate

Hevo Activate helps you directly transfer data from Amazon Redshift, and various other sources to Google Sheets, CRMs, various SaaS applications, and a lot more, in a completely hassle-free & automated manner. Hevo Activate is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. It helps transfer data from Redshift to a destination of your choice for free. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Get Started with Hevo for Free

Hevo Activate takes care of all your data preprocessing needs and lets you focus on key business activities and draw more powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. Hevo Activate will efficiently automate the process of setting up your Redshift Google Sheets integration.

You can establish a Redshift Google Sheets connection using Hevo Activate with the following steps:

Step 1: Configure Amazon Redshift as your Data Source

Connect your Amazon Redshift account as source to Hevo Activate as shown in the below image.

Redshift Google Sheets: Configure Redshift
Image Source

Step 2: Configure Google Sheets as your Target Destination

To set Google Sheets as your Target destination, you can follow the simple steps given below:

  • Step 1: Click on the Activate in the Asset Palette.
  • Step 2: Do one of the following:
Redshift Google Sheets - Create a new Target
Image Source
Redshift Google Sheets - Create an Activation
Image Source
  1. In the Select Warehouse page, select your Activate Warehouse or click + ADD WAREHOUSE to add a new warehouse. Read Activate Warehouses to configure the selected Warehouse type.
  2. In the Select a Target page, click + ADD TARGET.
Redsift Google Sheets - Add New Target
Image Source
  • Step 3: In the Select a Target Type page, click on Google Sheets.
Redshift Google Sheets - Select a Target Type
Image Source
  • Step 4: In the Configure your Google Sheets account page, select the authentication method for connecting to Google Sheets.
Redshift Google Sheets - Select Authentication Method
Image Source
  • Step 5: Do one of the following:
    • Connect using a User Account:
      1. Click + ADD GOOGLE SHEETS ACCOUNT.
      2. Select the Google account associated with your Google Sheets data and click ALLOW to authorize Hevo to access your data.
    • Connect using a Service Account:
      1. Attach the Service Account Key. Read Downloading the key file for steps to create a new key. Note: Hevo supports only JSON format for the key file.
      2. Click CONFIGURE GOOGLE SHEETS ACCOUNT.
  • Step 6: In the Configure your Google Sheets Target page, specify details such as a unique Target Name, your Google Drive folder(optional), and your Google Spreadsheet.
Redshift Google Sheets - Configure Google Sheets Target
Image Source
  • Step 7: Click TEST & CONTINUE. You can view the new Target in the Targets List View. If you are creating an Activation, you return to the Select Data to Synchronize page.
Redshift Google Sheets - Targets List View
Image Source

Check out what makes Hevo Activate amazing:

  • Real-time Data Transfer: Hevo Activate, with its strong Integration with various sources, allows you to transfer data quickly & efficiently. This ensures efficient utilization of bandwidth on both ends.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer.
  • Secure: Hevo Activate has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Tremendous Connector Availability: Hevo Activate houses a large variety of connectors and lets you bring in data from numerous Data Warehouses and load it into Marketing & SaaS applications, such as Salesforce, HubSpot, Zendesk, Intercom, etc. in an integrated and analysis-ready form.
  • Simplicity: Using Hevo Activate is easy and intuitive, ensuring that your data is exported in just a few clicks.
  • Completely Managed Platform: Hevo Activate is fully managed. You need not invest time and effort to maintain or monitor the infrastructure involved in executing codes.
  • Live Support: The Hevo Activate team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
Sign up here for a 14-Day Free Trial!

Conclusion 

This article introduced you to Amazon Redshift and Google Sheets along with their key features. It also provided 2 methods to set up the Redhsift Google Sheets integration. step-by-step approach to Sync Excel to Google Sheets. The first method uses Python to establish a Redshift Google Sheets connection to transfer data. This way can link Amazon RedShift to Google Sheets and gain flexibility with your data. However, this method requires you to have a programming background as you will have to write the complex code manually. The second method provides you with a simpler alternative in Hevo Activate!

Visit our Website to Explore Hevo

Hevo Activate will automate your data transfer process, hence allowing you to focus on other aspects of your business like analytics, customer management, etc.  This platform allows you to transfer data from Cloud-based Data Warehouses like Amazon Redshift, Snowflake, Google BigQuery, etc to various CRMs and SaaS applications. It will provide you a hassle-free experience and make your work life much easier. It helps transfer data from Redshift to a destination of your choice for free.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. 

Try to set up Redshift Google Sheets integration yourself and share your experience in the comments section!

Enhance Customer Analytics Using Hevo Activate