Python Connect to Google Sheets Made Easy

on Activate, Data Integration, ETL, ETL Tutorials, Google sheets, Hevo Activate, Python, Spreadsheet Application • March 14th, 2022 • Write for Hevo

Python connect to Google Sheets Cover

When huge volumes of data are involved, manually uploading and modifying the data in Google Sheets can be time-consuming. It would be much easier if these sheets were connected to a script that could simply automate the tasks, consumes less time to complete, and are simple to understand. With the Python connect to Google Sheets, it becomes easier to integrate the data with libraries like NumPy or Pandas. Google also provides an API for executing most of the operations, which acts as a medium to connect Python script to Google Sheets.

In this blog, a step-by-step approach on how to connect Python with Google Sheets is laid out. But before getting started with Python connect to Google Sheets, let’s discuss the various aspects of Google Sheets.

Table of Contents

Prerequisites

Basic knowledge of Python libraries.

Technical Requirements for Python Connect to Google Sheets

Here’s what you would need to establish the Python connect to Google Sheets.

  • Python 2.6 or later.
  • The pip package. 
  • API (Application Programming Interface) enabled Google Cloud Platform project.
  • Authorization credentials. 
  • A Google account.

What is Google Sheets?

Python Connect to Google Sheets: Google Sheets
Image Source: www.stickpng.com

Google Sheets is a web-based Spreadsheet application provided by Google that allows users to create, manage, format, and collaborate with others. Being an online application, Google Sheets is compatible with Google Chrome, Microsoft Edge, Mozilla Firefox, Internet Explorer, and Apple Safari.

Key Features of Google Sheets

  • Seamless Collaboration: Google Sheets is a real-time collaboration tool for interactive Spreadsheet editing. Multiple users can open, edit, and share documents simultaneously and view character-by-character changes while other collaborators are making edits.
  • Supports various File Formats: File formats such as .xls, .xlt, .xlsx, .ods, .xlsm, .xltx, .tsv, .xltm, .csv, .tab, .txt can be viewed and converted according to the format of the Sheets.
  • Explore Feature: The Explore feature in Google Sheets allows users to ask questions like “How many units were sold on Black Friday?” and get the answer without the need for a formula. This feature allows users to automatically build Charts, visualize Data, and create Pivot Tables.

Getting Started with Google Sheets

  • Step 1: Type sheets.google.com in any web browser of your choice.
  • Step 2: Click the Blank Sheet or select any template from the template gallery.

Simplify Customer & Product Analytics using Hevo Activate

Hevo Activate helps you directly transfer data from Data Warehouses such as Snowflake, Amazon Redshift, etc., 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. 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 pre-processing data needs and allows you to focus on key business activities, to draw compelling insights into your product’s performance, customer journey, high-quality Leads, and customer retention through a personalized experience.

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.
  • 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.

Users can leverage Hevo Activate to perform the following operations:

  • Create User Segments: Creating user segments allows the Marketing and Sales teams to understand how resources should be utilized for different kinds of users. This allows teams to focus on channels that convert better and maximize their Return On Investment (ROI).
  • Build 360 View of Customers: This can be used to understand each customer better and plan strategies accordingly to ensure maximum revenue. This information can also be leveraged to help Support teams prioritize Enterprise customers. Businesses can seamlessly sync all customer data into their support software and respond quicker with a holistic customer background.
  • Sync Product Data into Sales CRM: Hevo Activate can be leveraged to get all product data in the CRM tool of choice, allowing businesses to track user activity easily. Users can be segmented based on their activity, and that information can be used to improve product adoption and prevent churns.
Sign up here for a 14-day free trial!

Understanding Use of Python in Google Sheets

Python is a programming language widely used to create Websites and Applications, automate Operations, and perform Data Analysis. It is an object-oriented, open-source, high-performance language that is simple to learn and debug. As it promotes readability for making it easy to understand, Python is a beginner-friendly programming language. Its Data Analysis libraries, such as Pandas and NumPy, are mostly used to manipulate and analyze data effectively. And hence, a Python connect to Google Sheets leverages NumPy or Pandas to integrate the data.

NumPy

Python Connect to Google Sheets: NumPy
Image Source: www.commons.wikimedia.org

Numerical Python is abbreviated as NumPy. The n-dimensional array in NumPy is the most powerful feature. Basic linear algebra functions, Fourier transformations, and advanced random number capabilities are also included in the NumPy package. 

  • Provides multidimensional arrays, functions, and operators, which helps in executing mathematical operations quickly.
  • Used in several advanced analysis tasks. To store and manipulate data, the OpenCV computer vision library uses NumPy arrays.

Pandas

Python Connect to Google Sheets: Pandas
Image Source: www.commons.wikimedia.org

Pandas library allows users to work with datasets. It also offers Data Analysis, Cleansing, Exploration, and Manipulation tools. It can import data from a variety of file types, including comma-separated values (CSV), JSON, SQL Database tables or queries, and Microsoft Excel.

  • DataFrame object with inbuilt indexing for data manipulation capabilities.
  • Slicing of big datasets based on labels, clever indexing, and subsetting.
  • Allows users to read and write data between in-memory data structures and several file formats with tools.

Getting Started with Python

Python can be installed on your system through various methods, 2 of which are mentioned below.

Anaconda, a Python environment, is used for this demonstration of Python connect to Google Sheets.

Getting Started with Anaconda

  • Step 1: Run the Anaconda Navigator.
  • Step 2: Click on Jupyter Lab. As soon as you execute Step 2, you will witness a new tab in your browser. 
  • Step 3: Click the ‘+‘ button in the upper right corner to create a new Python file. Keep the file open.

Getting Started with Google Cloud Console

In order to establish a Python connect to Google Sheets, first, we will need to obtain an authorization code/mail from the Google API Platform. For that, we need to follow the steps given below carefully.

How to Create a New Project?

  • Step 1: Open Google Cloud Console in your browser and log in.
  • Step 2: Click on the Menu (three horizontal lines, right corner) > IAM & Admin > Create a Project.
Python Connect to Google Sheets: Create a Project
Image Source: Self
  • Step 3: Enter the name for your project in the Project Name section.
Python Connect to Google Sheets: Project Details
Image Source: Self
  • Step 4: Click Browse in the Location field to get a list of possible sites for your project. After that, click Select.
  • Step 5: Select Create. Within a few minutes, the console will navigate to the Dashboard page, where you can start your project.

How to Enable an API?

The next step in accomplishing a Python connect to Google Sheets is enabling an API. Let’s hover to the Google Cloud Console.

  • Step 1: Click on Menu, at the top left > APIs & Services > Dashboard.
Python Connect to Google Sheets: API & Services
Image Source: Self
  • Step 2: Type the Google Drive API in the search field and press Enter.
  • Step 3: Click on Enable.
Python Connect to Google Sheets: Google API
Image Source: Self
  • Step 4: Now, for Google Sheets API, repeat steps 2 and 3.
Python Connect to Google Sheets: Sheets API
Image Source: Self
  • Step 5: After enabling both the APIs, click on the Bell icon at the top right of the screen and click on the project name.
Python Connect to Google Sheets: Create Project
Image Source: Self
  • Step 6: Now that you have enabled the required APIs in your project, it’s time to create credentials for the service account.

How to Create a Service Account?

After obtaining your choice of credentials, now it’s time to create a service account. 

  • Step 1: Click on Credentials > Manage service accounts > Create service account.
Python Connect to Google Sheets: Service Account
Image Source: Self
  • Step 2: The following screen will appear, fill in the necessary information and hit Done. You will receive an email on the screen; copy that email. Do not close the window.
Python Connect to Google Sheets: Account Details
Image Source: Self
  • Step 3: Paste the mail into the Google Sheets’ share button and click on Send.
Python Connect to Google Sheets: Share
Image Source: Self
  • Step 4: Go back to the email window from step 2. You will see the following image. Click on the three dots > Manage keys > Add Key > Create a new key.
  • Step 5: Click on JSON to create a private key. Hit CREATE. And then select OK.

You have now successfully connected your Google API account to the Google Sheets.

Python Connect to Google Sheets

Follow the below-mentioned steps to work around a Python connect to Google Sheets.

  • Step 1: In the Anaconda console, install the following libraries.
pip install gspread
pip install --upgrade google-api-python-client oauth2client
  • Step 2: Open a new Python file and import the following libraries into a Python file.
import gspread
import pandas as pd
from oauth2client.service_account import 
ServiceAccountCredentials
  • Step 3: Gathering the essentials.
# defining the scope of the application
scope_app =
['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive'] 

#credentials to the account
cred = ServiceAccountCredentials.from_json_keyfile_name('json_file_name.json',scope) 

# authorize the clientsheet 
client = gspread.authorize(cred)
  • Step 4: Create a sample Spreadsheet.
# get the sample of the Spreadsheet
sheet = client.open(‘addresses’)

# get the first sheet of the Spreadsheet
sheet_instance = sheet.get_worksheet(0)
  • Step 5: Display all the records.
# get all the records of the data
records = sheet_instance.all()

# view the data
records

Output:

[{'First Name' : John,
  'Last Name' : Doe,
  'Address' : 120 jefferson st.,
  'City' : Riverside,
  'State' : NJ
  'Code' : 8075}
 {'First Name' : Jack,
  'Last Name' : McGinnis,
  'Address' : 220 hobo Av.,
  'City' : Phila ,
  'State' : PA,
  'Code' : 9119
 }]
  • Step 6:  Convert Dictionary to the DataFrame.
 # convert the json to dataframe
records_df = pd.DataFrame.from_dict(records)

# view the top records
records_df.head()
Python Connect to Google Sheets: Output 1
Image Source: Self

Additional Operations

Here are a few additional operations that you can perform with a Python connect to Google Sheets.

  • Grouping columns.
# First names of the people with the city name.

first_names = df.groupby([‘First Name’])['City'].count().reset_index()
first_name
Python Connect to Google Sheets: Output 2
Image Source: Self
  • Displaying the first row.
first_row = df.iloc[1]
first_row

If you want to display any row, add the row number in the above syntax.

Python Connect to Google Sheets: Output 3
Image Source: Self

Conclusion

With the Python connect to Google Sheets, users can easily edit, organize, and analyze different types of data. It also allows multiple users to edit and format files in real-time, with a revision history that allows users to track changes to the Spreadsheet. In this blog, you learned how to establish the Python connect to Google Sheets easily and create a sample Google Spreadsheet with the help of Python.

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 helps you directly transfer data from a source of your choice such as Snowflake, Amazon Redshift, etc., and various other sources to Google Sheets or any SaaS application, CRMs, etc., in a fully automated and secure manner without having to write the code repeatedly. It will make your life easier and make data migration hassle-free.

Give Hevo Activate a try and sign up for a 14-day free trial today. Hevo offers plans & pricing for different use cases and business needs, check them out!

Share your experience of understanding the Python connect to Google Sheets in the comments section below.

Sync your data to Google Sheets Seamlessly