Generally, Google Sheets is used to analyze, chart swiftly, and manipulate data. But far too often, analysts just export or download query results to Google Sheets. Meanwhile, it’s possible to automate the process of sending data from Snowflake to Google Sheets. Continue reading to learn how to move data from Snowflake to Google Sheets using Python and App script.
Table of Contents
- What is Google Sheets?
- What is Snowflake?
- What is Apps Script?
- What is Python?
- Method 1: Moving Data from Snowflake to Google Sheets
- Method 2: Moving Data from Snowflake to Google Sheets using Hevo Activate
- Google sheets account
- Snowflake account
- Google Analytics API
- Google analytics profile
- JDBC driver connection string
- Python packages (pandas, googleapiclient, and SQLAlchemy)
What is Google Sheets?
Google Sheets are computerized spreadsheets with rows and columns that allow users to input, maintain, and perform mathematical operations on their data. It provides users with the one-of-a-kind capacity to produce tables, charts, and graphs for analysis.
With Google Sheets, multiple users can open, edit, and share documents at the same time, and they can see character-by-character changes as others work on them. Google Sheets also allows you to select from a wide range of pre-made schedules, budgets, and other spreadsheets that are intended to improve your work and make your life easier.
Methods to Move Data from Snowflake to Google Sheets
You can use python script and googleapiclient, SQLAlchemy, and JDBC drivers to connect snowflake to Google Sheets. Snowflake will be connected to python using the JDBC drivers and then import the data in form of data frames in Python. Then this data is moved to Google Sheets using google analytics API. As seen this is a very cumbersome task and this process can be automated using the second Method.
Hevo Activate provides a hassle-free solution and helps you directly Connect Snowflake 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 Snowflake to a destination of your choice for free.Get started with hevo for free
What is Snowflake?
Snowflake is a fully managed service that offers customers near-infinite scalability of concurrent workloads, allowing them to integrate quickly, load, analyze, and securely share their data.
Snowflake is a one-of-a-kind architecture that blends computation and storage natively. This design allows your users and data workloads to virtually access a single copy of your data without affecting performance. Snowflake enables you to execute your data solution across many locations and clouds to provide a consistent experience.
What is Apps Script?
Google Sheets includes an “Apps Script” app development platform that can be used to create sidebars, custom menus, and web apps. It also has a JDBC service, which allows you to connect to specific databases. Though Apps Script does not support Snowflake currently, it does support Oracle databases, MySQL, and Microsoft SQL Server.
What is Python?
Python makes querying and moving data to Sheets easy. For this, Python has a data analysis library called Panda. You can work with datasets using the Pandas library. It also offers Cleansing, Data Analysis, Exploration, and Manipulation tools.
It can import data from various file types, including CSV, JSON, SQL, and Microsoft Excel. Python Panda has built-in SQL support that makes it easy to query data and save it as a Pandas data frame. Google also provides an API that makes it simple to transfer a data frame to Google sheets.
Method 1: Moving Data from Snowflake to Google Sheets using Python
Before you get started with Snowflake to Google Sheets, you need to connect your snowflake database with Python. Use the connection string for the JDBC driver. This string is made up of username, password, and database information. It will look something like this
Understanding the connection parameters
- <account_identifier> This parameter specifies the account identifier for your Snowflake account.
- <connection_params> This parameter defines a sequence of one or more parameters in the format <param>=<value>, with each parameter separated by the (&) and no spaces in the connection string.
Let’s get started with the Snowflake to Google Sheets connection now that you’ve connected your snowflake database.
- Snowflake to Google Sheets Step 1
- Snowflake to Google Sheets Step 2
- Snowflake to Google Sheets Step 3
- Snowflake to Google Sheets Step 4
- Snowflake to Google Sheets Step 5
- Snowflake to Google Sheets Step 6
Snowflake to Google Sheets Step 1
To set up the SQLAlchemy engine, run this code. You’ll need the Python packages (pandas, googleapiclient, and SQLAlchemy).
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)
Snowflake to Google Sheets Step 2
It’s time to get your database engine up and running. With this, it will be easy to query and save as a Pandas data frame. Run the following code:
sql = ''' SELECT * FROM public.users limit 5 ''' df = pd.read_sql(sql, engine)
Snowflake to Google Sheets Step 3
Import your Pandas data frame into Google Sheets. The Google Analytics API is one way to move it to Sheets. You must first create a service account, then generate and save an API credentials JSON file to your PC.
To get started using Google Analytics API:
Create a Service Account
- Go to the Service accounts page. Select a project if prompted. Create a project if you don’t have one.
- Click “Create Service Account” and give the account a name and description. You can use the default service account ID or create a new, unique one. When you’re done, click Create.
- Click Done on the Grant users’ access to this service account.
- Your service accounts will be displayed here. You have to create a key. Left-click the three dots beneath the actions, then select the Manage keys.
- Select Create a new key from the Add key menu. Choose your key’s format: JSON is recommended.
- Your new public/private key pair is created and downloaded to your computer; it is the sole copy of the key.
- Click Done to return to your service accounts.
Connect the Google Analytics Account to the Service Account
Using the email address, i.e., your service account email, add a user to the Google Analytics account you want to access via the API.
Install the Google Client Library for Python
You can install the Python Google client library using any of the options below.
- Pip: For installing Python packages, this is the recommended tool to use. To install the client library, run the code below.
- Setup Tools: In the setuptools package, use the easy_install tool.
Manual Installation: Download the most recent Python client library, unpack the code, and run:
Note: Each option contains links to the python download directory.
Setup the Library
It’s time to set up your Google client library for Python. You’ll have to create a single file called TestAnalytics.py. This file will contain all the code you’ll use. (You have the option to rename the file.)
- Once you’ve created the file, paste the source code below into TestAnalytics.py.
"""This is an example of how to access the Google Analytics API.""" from apiclient.discovery import build from oauth2client.service_account import ServiceAccountCredentials def get_service(api_name, api_version, scopes, key_file_location): """ get_service: Get a service that communicates to a Google API. Args: api_name: The name of the api to connect to. api_version: The api version to connect to. scopes: A list auth scopes to authorize for the application. key_file_location: The path to a valid service account JSON key file. Returns: A service that is connected to the specified API. """ credentials = ServiceAccountCredentials.from_json_keyfile_name( key_file_location, scopes=scopes) # Build the service object. service = build(api_name, api_version, credentials=credentials) return service def get_first_profile_id(service): # Use the Analytics service object to get the first profile id. # Get a list of all Google Analytics accounts for this user accounts = service.management().accounts().list().execute() if accounts.get('items'): # Get the first Google Analytics account. account = accounts.get('items').get('id') # Get a list of all the properties for the first account. properties = service.management().webproperties().list( accountId=account).execute() if properties.get('items'): # Get the first property id. property = properties.get('items').get('id') # Get a list of all views (profiles) for the first property. profiles = service.management().profiles().list( accountId=account, webPropertyId=property).execute() if profiles.get('items'): # return the first view (profile) id. return profiles.get('items').get('id') return None def get_results(service, profile_id): # Use the Analytics Service Object to query the Core Reporting API # for the number of sessions within the past seven days. return service.data().ga().get( ids='ga:' + profile_id, start_date='7daysAgo', end_date='today', metrics='ga:sessions').execute() def print_results(results): # Print data nicely for the user. if results: print 'View (Profile):', results.get('profileInfo').get('profileName') print 'Total Sessions:', results.get('rows') else: print 'No results found' def main(): # Define the auth scopes to request. scope = 'https://www.googleapis.com/auth/analytics.readonly' key_file_location = '<REPLACE_WITH_JSON_FILE>' # Authenticate and construct service. service = get_service( api_name='analytics', api_version='v3', scopes=[scope], key_file_location=key_file_location) profile_id = get_first_profile_id(service) print_results(get_results(service, profile_id)) if __name__ == '__main__': main()
Note: A comment is any statement in a line of code that begins with (“””). It explains the purpose of a section or line of code.
- When you’re done with the code, copy the client secrets.json file into the same directory as the code.
- Replace the key_file_location values with the appropriate values.
Run the Program
The program is ready to run once you’ve enabled the Google Analytics API, installed the Python Google APIs client library, and set up the source code.
Use the following commands to run the program:
When you’ve completed these steps, the application will display the name of the authorized user’s first Google Analytics view (profile) as well as the number of sessions for the previous seven days. (The number of days can be changed in the code.)
Note: You must have at least one Google Analytics property and view to run this test successfully
Snowflake to Google Sheets Step 4
Now you can gain access to Sheets for Snowflake to Google Sheets connection. Run the following code, replacing the Google API credentials with your own:
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()
Snowflake to Google Sheets Step 5
Next, you will have to authorize access to your Google Account. After you’ve given your approval, you’ll be able to receive data from Google Sheets. The Spreadsheet ID (found in the Sheet’s URL), as well as the name and range of the Sheet you want to view, are required:
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()
Snowflake to Google Sheets Step 6
As shown below, you can use the update() method to publish your data frame (df) to that Sheet:
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()
Method 2: Moving Data from Snowflake to Google Sheets using Hevo Activate
Hevo Activate helps you directly transfer data from Snowflake, 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 Snowflake 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.
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 Snowflake Google Sheets integration.
You can establish a Redshift Google Sheets connection using Hevo Activate with the following steps:
- Step 1: Configure Snowflake as your Data Source
- Step 2: Configure Google Sheets as your Target Destination
Step 1: Configure Snowflake as your Data Source
- Click ACTIVATE in the Asset Palette, and:
- In the ACTIVATE TAB, click on CREATE ACTIVATION button.
- A Select Warehouse page will open, click ADD WAREHOUSE button
- On the Select Warehouse Type page, select Snowflake.
Or you can use this method to setup Snowflake as a Source:
- Click DESTINATIONS in the Asset Palette, and:
- Click on the CREATE button in the Destinations List View.
- On Add Destination page select Snowflake as the Destination type.
- A Configure your Snowflake Warehouse page will open, specify the parameters required.
- Click the Test Connection button to test connectivity with the Snowflake warehouse.
- Once the test is successful, click SAVE WAREHOUSE. now your warehouse is saved as a 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:
- Click on the Activate in the Asset Palette.
- Do one of the following:
- Select the TARGETS button in the Targets List View, and click CREATE TARGET button.
- Select the ACTIVATIONS in the Activations List View, and click CREATE ACTIVATION button.
- 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.
- On the Select a Target page, click + ADD TARGET.
- Step 3: On the Select a Target Type page, click on Google Sheets.
- Step 4: In the Configure your Google Sheets account page, select the authentication method for connecting to Google Sheets.
- Step 5: Do one of the following:
- Connect using a User Account:
- Click + ADD GOOGLE SHEETS ACCOUNT.
- 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:
- 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.
- Click CONFIGURE GOOGLE SHEETS ACCOUNT.
- Connect using a User 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.
- 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.
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.
Congratulations! You’ve successfully moved your data from Snowflake to Google Sheets using Python! Note that there are numerous ways to move data from Snowflake to Google Sheets, but the method you will use depends on your reasons and preferences.
However, dealing with huge volumes of data in Google Sheets can be a daunting and time-consuming task. Using Python scripts will need a lot of information and technical capabilities. For a more effortless and efficient approach, you can go for the second method and use a No-Code Automated solution like 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 with a hassle-free experience and make your work life much easier. It helps transfer data from Snowflake to a destination of your choice for free.
Share your experience of learning about the Snowflake to Google Sheets connection in the comments section below.