Python Google Sheets – An easy way to use Google API

Google Sheets API is a robust RESTful Google Sheets API that allows us to interact with Google Sheets easily. It allows you to work with spreadsheets in various ways, including creating or modifying sheets, retrieving data from sheets, and much more without worrying about data size.

This can save you time if you regularly deal with vast data sets, such as importing data into spreadsheets or analyzing data models on Google Sheets. Additionally, you can automate various low-value tasks. This article describes how to use the Python Google Sheets API library “gspread” to start working with one of the most widely used services on the Google Cloud Platform – Google Sheets.

Advantages of Using Python with Google Sheets

Python is a powerful programming language widely used in many industries today. For beginners, Python is easy to learn and has many advanced features for experienced programmers. Python is an interpreted, object-oriented, high-level programming language with dynamic semantics. Python’s simple syntax, dynamic typing, and interpreted nature make it an ideal language for scripting and rapid application development in many areas on most platforms. Python’s ecosystem consists of many data analysis, visualization, and Machine Learning libraries.

One of the advantages of using Python with the sheets API is that it is easy to integrate your data with NumPy or Pandas for data analysis or Matplotlib, Bokeh, and Plotly for data visualization.

Here’s an additional list of useful features:

1. Create, view, and edit: Google Sheets enables users to create, view, and edit spreadsheets, allowing for efficient collaboration and data management.
2. Autosave: Google Sheets automatically saves your work as you edit, ensuring you never lose your progress.
3. Real-time: Google Sheets supports real-time collaboration, allowing multiple users to work on a spreadsheet simultaneously, making sharing and updating information easier.
4. Fast and responsive UI: Google Sheets is fast and responsive, thanks to Google’s powerful infrastructure, providing a seamless user experience.
5. Multiple sheets support: You can easily create additional sheets within a Google Sheets document to better organize your data and make it easier to find information.
6. Seamless Excel integration: Google Sheets allows users to work seamlessly across Sheets and Excel, making collaborating and managing data easier across different platforms.
7. Part of Google Workspace: Google Sheets is part of the Google Workspace suite of productivity tools, which includes other applications such as Google Docs, Google Slides, and Google Forms.
8. Maintain control with enterprise-grade security: Google Sheets offers enterprise-grade security features, ensuring your data remains secure and protected while collaborating with others.
9. Powered by Google AI: Google Sheets leverages AI-powered features to provide smart suggestions, data analysis, and other advanced capabilities to enhance your spreadsheet experience.
10. Build custom solutions: With the Google Sheets API, you can create custom solutions tailored to your specific needs, such as automating data entry, generating reports, and integrating with other applications.
11. Google APIs Explorer: The Google APIs Explorer allows you to try Google API methods without writing code, making it easier to test and understand the capabilities of the Google Sheets API.

Demo Google Sheet Document

First, we need to prepare a Google Sheet with some sample data. The Google Sheet that I will be using for this article contains the following datasets:

Google Cloud Platform – Project Setup

To interact with the Google Sheet API, you must use an existing project or create a new one on the Google Cloud Console(GCP). In case you don’t have your own Google account (Google Cloud Platform account), you can sign up for it now using our fantastic offer (Receive $350 in free credits and free usage of 20+ products on Google Cloud):

Sign up today to receive $350 in free credits and free usage of 20+ products on Google Cloud!

Create a new project

Once you have an active GCP account, you can create a new project.

On the main screen of the Google Cloud Platform Console, find “Select a project” by going to the projects menu at the top of the page. Expand the list of all active projects and the option to create a “New Project.”

Once the “New project” create window opens, type the meaningful in the Project name field. We’ll use “PythonSheetsAPIAtHandsOnCloud” as the project name, something you can easily associate with this how-to article.

Click on the Create button. Make sure that the newly created project is selected as your current project.

Enable access to Google Sheets and Google Drive API

Now we have to enable the Google Sheets API for our project because this is the core API with the necessary functionalities to manipulate Google Spreadsheets.

At the top of the screen, use the search engine to find “Google Sheets API”:

On this screen, we click on the Enable button.

Additionally, we have to enable the Google Drive API as well. We can follow the same steps to search and enable the Google Drive API. This is needed because our Google Sheets are stored inside Google Drive folders.

Note: Google provides access to most APIs free of charge in trial mode. However, it has a daily usage limit, as described on the official API limits page. If this free quota is not enough for you, you may need to upgrade your account to a paid one.

Creating Google Service Account

Once both APIs are enabled, we need to create a Service Account in the Google Cloud Platform to allow Google Cloud Python libraries to interact with Google Cloud APIs on behalf of our account user. The Service Account will allow us to connect Python to Google Sheets API.

There’re two steps involved in the process:

• Create a Service account (create credentials)
• Create a key for the Service account

To achieve this goal, use the left-side navigation pane and select APIs and Services -> Credentials menu.

Create credentials for the Service account. A Service account is a special account intended to represent a user who needs access to certain resources in your Google Cloud Platform account but is not necessarily a person. Service accounts are commonly used for application-level authentication from your scripts or backend applications.

Fill in Service account details:

• Service account name – the name of the Service account (login), for example, GCPSheetsServiceAccount.
• Service account ID – Google will generate it based on your input in the previous field. Usually, there’s no need to change anything here.
• Service account description – Type something meaningful not to forget the purpose of this Service account.

Next, we need to grant Service account access to our project. Select Editor. This role will provide the required permission to interact with the account resources.

We click on Continue.

The last step is optional, so we click the Done button.

Create API keys for Google Cloud Client Python library

At this step, we need to create a Python Google Sheets API key for the Service Account, which we’ll be using in our scripts to interact with Google Cloud APIs on behalf of our service account.

Click on the Service account to configure it.

The service account has an email we will use later, so copy it somewhere.

Now we go to the Keys tab and then select “Add key -> Create new key“.

Choose the JSON key type, and then click on the CREATE button to save the file with the key.

The downloaded key file needs to be moved to the same folder where the example Python script will be created.

The content of the JSON file looks something like this.

{
  "type": "service_account",
  "project_id": "pythonsheetsapiathandsoncloud",
  "private_key_id": "78e0d3d4289f419fa344aa4565ac2ccab2e31d7d",
  "private_key": "-----BEGIN PRIVATE KEY-----\YOUR PRIVATE KEY HERE+\n-----END PRIVATE KEY-----\n",
  "client_email": "gcpsheetsserviceac@pythonsheetsapiathandsoncloud.iam.gserviceaccount.com",
  "client_id": "109540787094312810519",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/gcpsheetsserviceaccount%40pythonsheetsapiathandsoncloud.iam.gserviceaccount.com"
}

Share Spreadsheet to Service Account

The last thing we need to do before we jump to the Python script is to share the Spreadsheet document with the Service account’s email address.

How to use Google Sheets with Python?

Once we’re configuring the GCP project, let’s write some code. Open up your favorite code editor and create a new project folder. I’ll be using VS Code.

There are many Python libraries that you can use to interact with Google Sheets, for example:

• Google API Python Client – this library is provided by Google, allowing you to interact with the Google API from your Python code.

• gspread – this library allows you to interact with the Google Spreadsheets API from your Python code using more high-level code. It can be used to read and write Spreadsheet data and query and update Spreadsheet values.

• GSheets – another high-level Google Spreadsheets API Python module that is similar to the “gspread”

The tutorial will use the gspread library for Python to Google Sheets interaction. The gspread library provides a great way to work with Google Sheets within Python with the minimum code to be written. It provides easy-to-use methods that make it easy to read and write data from sheets. Also, it supports various other operations, such as sorting and filtering data.

Requirements

First, ensure you have installed a recent version of Python, such as Python 3 or the latest. I’m using Windows and PowerShell with the official Terminal app. You can repeat the same steps on your Mac or Linux.

Let’s install the necessary dependencies to connect our Python application to the Google Cloud Platform. The most common way of installing these dependencies is to use the pip command as shown below:

pip install oauth2client --user
pip install gspread --user

The oauth2client library is used by the Python GCP client for authorization and signing API calls to Google Cloud Services. The oauth2client provides an easy way to interact with OAuth2-protected resources such as Google APIs. So, we will be using it together with the gspread library to access Google Sheets.

Application Setup

Now, finally comes the good part!
we are ready to use the gspread Python library to access and manipulate the data in Google Sheets.

How to read a Google Sheet in Python?

First, you must import the required libraries at the top of your script, as shown below:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

Next, you need to add scopes for the Google Drive API and Google Sheet API. This is a mandatory requirement for interacting with required APIs. Also, specify the path to the credentials JSON file that we saved earlier.

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']

# Reading Credentails from ServiceAccount Keys file
credentials = ServiceAccountCredentials.from_json_keyfile_name('pythonsheetsapiathandsoncloud-78e0d3d4289f.json'
            , scope)

# intitialize the authorization object            
gc = gspread.authorize(credentials)

Now you’re ready to access the content of the Spreadsheet document. You need to know the name of the Spreadsheet document that you want to access. In our example, the Google Sheet file name is “handsoncloudDemoSheet”. So we will open it as shown below:

# Open Google Sheets file
sheet = gc.open('handsoncloudDemoSheet')

Now, here’s a complete source code of the script:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

def main():
    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']
    # Reading Credentails from ServiceAccount Keys file
    credentials = ServiceAccountCredentials.from_json_keyfile_name('pythonsheetsapiathandsoncloud-78e0d3d4289f.json'
                , scope)
    # intitialize authorization object            
    gc = gspread.authorize(credentials)
    # Open Google Sheets file
    sheet = gc.open('handsoncloudDemoSheet')
    try:
        sheet_info = sheet.get_worksheet(0)
        #Get and print all records
        print(sheet_info.get_all_records())
    except:
        print('Error Occurred')
    return

# Function Call
if __name__ == '__main__':
    main()

We read all the records from the first Google Sheet (the get_worksheet() method) using the get_all_records() method.

Note: read more about Exception handling in Python.

Execute the Python script using the following command:

python read.py

After the execution, you should get the following output:

[{'ID': 1, 'Subject': 'History', 'Grade': 'A'}, {'ID': 2, 'Subject': 'Science', 'Grade': 'B'}, {'ID': 3, 'Subject': 'Maths', 'Grade': 'A'}]

How to input data to Google Sheets using Python?

Similarly, for writing the content to the Google Spreadsheet, you can use the append_row() method :

sheet.append_row([23, "Geography", "A+"])

Now, here’s the complete source code of the script:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

def main():
    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']
    # Reading Credentails from ServiceAccount Keys file
    credentials = ServiceAccountCredentials.from_json_keyfile_name('pythonsheetsapiathandsoncloud-78e0d3d4289f.json'
                , scope)
    # intitialize authorization object
    gc = gspread.authorize(credentials)
    # Requesting to Open Google Sheets file
    sheet = gc.open('handsoncloudDemoSheet').sheet1
    try:
        #Insert row to google sheets 
        sheet.append_row([23, "Geography", "A+"])
    except:
        print('Error Occurred')
    return
# Function Call
if __name__ == '__main__':
    main()
 

Can you edit Google Sheets with Python?

Yes, you can edit Google Spreadsheet and change a specific cell using the update() method of the Sheet object representing a Worksheet:

sheet.update('B2', 'Science')
sheet.update_cell(1, 2, 'Biology')

Now, here’s a complete source code of the script:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

def main():
    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']
    # Reading Credentails from ServiceAccount Keys file
    credentials = ServiceAccountCredentials.from_json_keyfile_name('pythonsheetsapiathandsoncloud-78e0d3d4289f.json'
                , scope)
    # intitialize authorization object
    gc = gspread.authorize(credentials)
    # Requesting to Open Google Sheets file
    sheet = gc.open('handsoncloudDemoSheet').sheet1
    try:
        #Update Cell using absolute address 
        sheet.update('B2', 'BioChemistry')
        #Update Cell using cordinates numbers
        sheet.update_cell(2 ,1, 9022)
    except:
        print('Error Occurred')
    return
# Function Call
if __name__ == '__main__':
    main()

Apart from this, you can access data from a single row, column, or cell by using the row_values(), col_values(), and cell() methods.

How to store Pandas DataFrame to Google Sheets?

Pandas is a Python library used for data manipulation tasks in data science. You can use Pandas DataFrames to read data from Sheets and perform various operations on them. This can be very helpful in getting your data ready for insights.

In this example, we will fetch the USA population data in JSON format and save it to Google Sheets.

Let’s read this JSON data into the DataFrame and push it to Google Sheets:

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd
import requests 
import traceback
def main():
    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']
    # Reading Credentails from ServiceAccount Keys file
    credentials = ServiceAccountCredentials.from_json_keyfile_name('pythonsheetsapiathandsoncloud-78e0d3d4289f.json'
                , scope)
    # intitialize authorization object
    gc = gspread.authorize(credentials)
    # Requesting to Open Google Sheets file
    sheet = gc.open('handsoncloudDemoSheet').sheet1
    # Fetch data from API 
    r = requests.get('https://datausa.io/api/data?drilldowns=Nation&measures=Population')
    x = r.json()
    df = pd.DataFrame(x['data']) 
    try:
        #Insert dataframe to google sheets 
        sheet.update([df.columns.values.tolist()] + df.values.tolist())
    except Exception:
        print(traceback.format_exc())
    return
# Function Call
if __name__ == '__main__':
    main()

Here’s the code execution output on our blank spreadsheet:

How to load Google Sheets data to Pandas Dataframe?

You can load Google Spreadsheet data into a Pandas DataFrame for data manipulation or visualization.

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd
import requests 
import traceback
def main():
    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']
    # Reading Credentails from ServiceAccount Keys file
    credentials = ServiceAccountCredentials.from_json_keyfile_name('pythonsheetsapiathandsoncloud-78e0d3d4289f.json'
                , scope)
    # intitialize authorization object
    gc = gspread.authorize(credentials)
    # Requesting to Open Google Sheets file
    sheet = gc.open('handsoncloudDemoSheet').sheet1
    try:
        #Insert dataframe to google sheets 
        dataframe = pd.DataFrame(sheet.get_all_records())
        print(dataframe)
    except Exception:
        print(traceback.format_exc())
    return
# Function Call
if __name__ == '__main__':
    main()

Here’s an execution output:

  ID Nation         Nation  ID Year  Year  Population    Slug Nation
0   01000US  United States     2020  2020   326569308  united-states
1   01000US  United States     2019  2019   324697795  united-states
2   01000US  United States     2018  2018   322903030  united-states
3   01000US  United States     2017  2017   321004407  united-states
4   01000US  United States     2016  2016   318558162  united-states
5   01000US  United States     2015  2015   316515021  united-states
6   01000US  United States     2014  2014   314107084  united-states
7   01000US  United States     2013  2013   311536594  united-states

If you want to use Pandas more complexly, you can use extension libraries like gspread-pandas and gspread-dataframe. These libraries provide more functionality and allow you to do more with Pandas.

FAQ

How to run Python script in Google Sheets?

You must use the Google Apps script to run the Python script from Google Sheets. Please remember that the Python script has to be available as a web service or through an API. Here are some code examples.

Conclusion

As you can see, the gspreads module offers a wide range of functionality, which makes it an excellent choice for those looking for a reliable and feature-rich solution to automate Google Sheets using Python and Google Cloud Platform APIs. All the functionalities are well documented in their official documentation here.

This article described how users could edit, organize and analyze Google Sheets data using Python. Additionally, we covered some of the key features of the gspread library.

If you want to learn more about the Google Sheets API, here are some resources to help you get started:

• The Google API documentation page provides all the information you need to start using the API. This includes tutorials, code samples, and a reference guide.
• The Google Sheets Developer Forum is a great place to ask questions and get help from other developers.