Select Page
Automation Testing

gspread Python Tutorial for Google Sheets Automation

Learn the full setup process & find out how to open a spreadsheet, read & insert data into it and much more from our gspread Python Tutorial

Gspread Python Tutorial For Google Sheets Automation Codoid Blog
Listen to this blog

On average, more than 2 Billion people use Google Sheets on a monthly basis. It is a powerful tool for managing and analyzing data. It is a popular choice among individuals as it is very user-friendly in nature. Additionally, it is also widely used by professionals and businesses. With continuous & extensive usage, few tasks could end up becoming repetitive and time-consuming. But with the help of automation, you will be able to overcome this major issue and streamline your workflow and perform tasks more efficiently. Being an automation testing company, we have written this gspread Python tutorial to help you automate google sheets using Python. So let’s get started.

There are actually several libraries such as pygsheets, gdata, and gspread that one can use to automate Google Sheets using Python. And we have picked gspread for this particular tutorial. We’ll explore the basic setup & configuration required to get started, explore some common use cases, and then dive into how we can automate those use cases in Google Sheets using Python.

Google Sheet Automation Set up

Before we explore how to automate Google Sheets using Python in our gspread Python Tutorial, we’ll find out how to set up the prerequisites.

Install a Python library

We have chosen gspread for this tutorial as it is one of the most popular and widely used libraries. So the first step in our gspread Python Tutorial is to install the gspread library that is needed to achieve Google Sheets automation using Python.

You can install gspread using the below command,

Command:

pip install gspread

Output:

Output Of Installing A Python Library

Once you have installed the library, you’ll need to set up authentication. And to do that, you’ll have to create a “service account” in Google API Console and grant access to the Google Sheet you want to automate. Let’s see what steps have to be followed to do it.

Create a New Project

Navigate to Google Developers Console and click on ‘Create Project’.

Creating A Project In Gspread Python Tutorial

For explanation purposes, we have created a project called ‘Gsheet Reader’ for our gspread Python Tutorial. You can also do it by filling in the required fields as shown in the below image and clicking the ‘Create’ button.

Creating A New Project

Enable API & Services

As the project has been created, you will see a new option to ‘Enable APIs and Services’ as shown below. You’ll have to click on it to add the Google Sheet API.

Enabling Api And Services

There will be a list of APIs and you can enter an appropriate keyword such as ‘sheet’ in the search bar and select the ‘Google Sheets API’.

List Of Api In Enabling Api And Services

You can then click the ‘Enable’ button from the Product Details page.

Google Sheets Api In Gspread Python Tutorial

Create Credentials:

Google Sheets API will now appear in your Enabled APIs & Services tab. You will see 3 sections namely Metrics, Quotas, and Credentials under it. So the next step in our gspread Python Tutorial would be to see how to create the required credentials to access the Google Sheet API.

So click the ‘Create Credentials’ button and follow the below steps.

Creating Credentials

Credential Type
  • Select ‘Google Sheets API’ in the ‘Select an API’ drop-down
  • Select the ‘Application Data’ radio button, and
  • Choose the ‘No, I’m not using them’ option in the last question.
  • Click ‘NEXT’.

Credential Type In Google Sheet Automation

Service Account Details

After which, you’ll have to enter the Service account name & Service account ID in the respective fields and click the ‘CREATE AND CONTINUE’ button.

Service Account Details

Grant Access

Now, you’ll have to specify the level of access you are going to provide for the service account.

Click on ‘Select a Role’ and select ‘Editor’ under the ‘Basic’ section. Based on your needs, you can choose whichever role will be the aptest.

Press ‘CONTINUE’ and leave the other optional fields and click DONE.

Granting Access In Gspread Python Tutorial

You will now be able to see the newly created Service account under the Credentials section.

New Credentials In Gspread Python Tutorial

Under the Keys Section, you can click on ‘ADD KEY’ and choose ‘Create new key’ to create a credential secret key.

Creating A New Key In Gspread Python Tutorial

There are two key types, and as suggested by Google, it is better to choose the JSON key type. After making the selection, click the ‘CREATE’ button.

Types Of Keys

Once you click the button, the key will be downloaded to your computer. Please make sure to not share this file with anyone else and keep it safe.

Saving The Key To The Computer

Tip: It will be helpful if you rename the file to credentials.json as it will be easy for you to remember the file name.

Create a Google Sheet

So the last step in our setup process is to create a Google Sheet that we can use to store our data. Once we are clear with that, we can go ahead and see the different Python code commands you can use to perform Google Sheet Automation.

If you already have a Google sheet, you can just copy the sheet url or sheet id as shown in the image as this copied string will be needed to access Google Sheets.

Creating A New Google Sheet In Gspread Python Tutorial

gspread Python Tutorial

Everything you’ll need to achieve Google Sheet Automation is now ready. So you can start automating the listed actions by typing the Python code commands we have mentioned in a code editor.

Opening a Spreadsheet

First up in our gspread Python Tutorial, we’ll be seeing how to open a spreadsheet using Python code commands. Remember the URL we had copied after creating a new Google Sheet? We will now use it to open that spreadsheet. You can also use your Sheet’s key to open the spreadsheet.

We will also have to use the JSON key we have created.

Code:

import gspread

Sheet_credential = gspread.service_account("credential.json")

# Open Spreadsheet by URL
# spreadsheet = Sheet_credential.open_by_url('paste your sheet url')

# Open Spreadsheet by key
spreadsheet = Sheet_credential.open_by_key('paste your sheet key')
print(spreadsheet.title)

Output:

Output Of Spreadsheet In Gspread Python Tutorial

You can see that the name of the Google Sheet we created (Gsheet Automation) has been fetched here.

Get a Worksheet’s Name

A Spreadsheet can have more than one worksheet and if you wish to print the name and ID of a worksheet, you can use the below Python code commands.

Code:

# to print worksheet name using sheet id
worksheet = spreadsheet.get_worksheet(0)

# to print worksheet name using sheet name
worksheet = spreadsheet.worksheet('Sample Sheet')
print(worksheet)

Output:

Output Of Worksheet Name

Read Data from a Sheet

Next up in our gspread Python tutorial, we’re going to see the command you’ll have to use to read data from a Google Sheet. In this example, we will be reading all the values present in a specific worksheet and printing it.

Code:

# To read all values from the sheet
all_values = worksheet.get_all_records()
for value in all_values:
    print(value)

Output:

Output Of Read Data From The Sheet

Insert Data into the Sheet

Reading the data alone isn’t enough. You’ll have to insert data into a sheet as well. So, let’s look at the Python Code command you’ll have to use to achieve that. In our example, we will be updating multiple values after a defined row and read & print the updated values.

Code:

# Update multiple values after A6 row
worksheet.update('A7', [["106", "Robert","J","Pass" ], ["107", "Robert","G","Fail"]])

# read data after update
update_data = worksheet.get_all_values()
for valu in update_data:
    print(valu)

Output:

Output Of Insert Data Into The Sheet

Update Multiple Ranges

Finally in our gspread Python tutorial, we are going to see the commands you can use to update multiple ranges of data at the same time.

Code:

# Update multiple ranges at once
worksheet.batch_update([{
    'range': 'E1:F2',
    'values': [['Name', 'Age'], ['Mohammed', '24']],
}, {
    'range': 'G1:H2',
    'values': [['A', 'AB'], ['C', 'CD']],
}])
# To Read data after the update
update_data = worksheet.get_all_values()
for valu in update_data:
    print(valu)

Output:

Output Of Multiple Range In Gspread Python Tutorial

CONCLUSION

So in our gspread Python tutorial, we have laid the foundations you’ll have to know to achieve Google Sheets automation using Python. By following these simple steps, you can easily automate tasks such as data entry, data analysis, and report generation. We hope this will help you significantly improve your workflow and save you time. Being a leading big data analytics testing company, we have used Python libraries and found gspread to be the best of the lot. Hope you find it useful too.

Comments(1)

  • 26 days ago

    Every time I visit your site, I leave feeling more knowledgeable.

Submit a Comment

Your email address will not be published. Required fields are marked *

Talk to our Experts

Amazing clients who
trust us


poloatto
ABB
polaris
ooredo
stryker
mobility