Read and update Google spreadsheets in Python

Overview

• Learn more about setting up a Google service account.
• Read and write data to Google spreadsheets with Python

Introduction

Automating work has been one of the fastest ways to achieve functional efficiency. What's more, in today's age where success depends on speed, automating countless repetitive tasks plays a key role in any industry and at the most basic level of functionality. But many of us don't understand how to automate some tasks and end up in the cycle of doing the same things manually again..

For instance, we often spend hours a day extracting data and then copying and pasting into spreadsheets and creating reports that lead to excessive time consumption. Consequently, it would be great if we just run a script, and the data is loaded into the spreadsheet and the report is prepared with just one click. There are multiple benefits of report automation, as it could save time on data collection and eliminate typos, and the focus would be more on the analysis part.

In this article, We will see a step by step process to set up a Google service account. We will use Google APIs to read data from Google spreadsheets using Python and we will also update data in spreadsheet using Python. We are going to read the cricket comment data from the spreadsheet and find out the number of runs scored by each batsman and then load the results into a separate spreadsheet.

In case you are not familiar with Python, take a look at our free course Introduction to Python

Table of Contents

1. Create Google service account
2. Read data from Google Sheets
3. Update data in Google Sheets

Create Google service account

To read and update data from Google Sheets in Python, we will have to create a Service account. It is a special type of account that is used to make authorized API calls to Google Cloud Services. First, make sure you have a google account. If you have a Google account, You can follow these steps to create a Google service account.

1. Go to the developer console. Now you will see something like this. Click the Create Project button.
2. Then provide the project name and organization name, what is optional. Then click the create button.
3. Now that our project is created, we need to enable the APIs that we require in this project. Click the Enable APIs and Services button to find the APIs provided by Google. Consequently, we will add two APIs for our project.
   • Google Sheets API
   • API de Google Drive
4. Later, in the search bar, find these APIs and click the enable button.
5. The Google Sheets API will look like this. It will allow you to access Google spreadsheets. You will be able to read and modify the content present in the spreadsheets.
   The Google Drive API will look like this. Allow you to access Google Drive resources.
6. Once you have enabled the required APIs in your project, it's time to create credentials for the service account. Click the Create credentials button to continue.
   

7. Now, select Google Drive API in type of API required question. We will call the API from a non-UI based platform, so select Others that are not UI (for instance, cron job, Devil). Select the Application data in the next question, since we don't need any user data to run our application. And we are not using any cloud-based computing engine for our application either.. Finally, click on the What credentials do i need? button.
8. Later, share google spreadsheets with other people and provide permissions like edit or view only. In the same way, we will provide access to our service account. We will give you full access so that we can read and write the spreadsheets and download the JSON file of the credentials.

Now, a JSON file will be downloaded containing the keys to access the API. Our Google service account is ready to use. In the next section, we will read and modify the data in the spreadsheet.

Read data from Google Sheets

We will read the commentary data from the cricket match India Bangladesh. You can access the data here.

We have ball-by-ball data for the entire match in the spreadsheet. Now, we will do a very basic task and calculate how many runs each of the batsmen scores. We can do this using a simple groupby in pandas. And finally, we will upload the results on a separate sheet.

Provide access to the Google sheet

Now, we need to provide access to the google sheet so that the API can access it. Open the JSON file that we downloaded from the developer console. Look for the email_client in the JSON file and copy it.

Then click the Share button on the spreadsheet and provide access to this customer email.

Now, we are ready to code and access the sheet using python. Following are the steps:

1. Importing the libraries

We will use the gspread Y oauth2client service to authorize and make API calls to Google Cloud Services.

You can install the libraries using the following commands.

!pip3 install gspread
!pip3 install --upgrade google-api-python-client oauth2client 

2. Define the scope of the application.

Later, we will define the scope of the application and add the JSON file that has the credentials to access the API.

3. Create the sheet instance

Use the client object and open the sheet. You just need to pass the title of the sheet as an argument. What's more, you can pass the sheet url if you want.

Access particular sheet: We have multiple sheets in a single spreadsheet. You can access certain Google spreadsheets with Python by providing the index for that sheet in the get_worksheet function. For the first sheet, pass the index 0 and so on.

Basic functionalities

The API provides some basic functionality, as the number of columns, by using col_count and getting the value in a particular cell. Below are some examples of the same.

4. Get all the logs

Later, we will obtain all the data present in the sheet using the get_all_records function. It will return a JSON string containing the data.

5. Convert the dictionary to the data frame

In data science, pandas is one of the preferred libraries to perform data manipulation tasks. Then, we will first convert the JSON string to pandas data frame.

In case you're not comfortable with pandas, I recommend that you enroll in this free course: Pandas for data analysis in Python

6. Batter's grouping

Later, we will create a group by the number of runs scored by a batter and load that data frame on the separate sheet.

Now, we will add this data frame to google sheets.

Update data in Google Sheets

The following are steps to update the data in Google sheets.

1. Create a separate sheet

   First, we will create a separate sheet to store the results. For that, use el add_worksheet function and pass the number of rows and columns required and the title of the sheet. Thereafter, get the instance of the second sheet by providing the index which is 1.

   Once you run this command, you will see a separate sheet is created.

   

2. Update values ​​to sheet

   Later, convert runs data frame to 2-D list and use function to add values ​​on sheet. With this single line of code, can update the sheet. Later, you will get a message of the number of rows and columns updated with some more details.

   

   

Final notes

To sum up, in this article, we dive into understanding the various steps involved in the process of creating a service account. And how to read writing in Google spreadsheets directly from your Python console. We download the data from the spreadsheet and convert it to the pandas data frame, we create a groupby table and load it back into the spreadsheet. This API can be very useful in automating reports.

In case you want to brush up on your spreadsheet concepts, I recommend the following article and course:

Hope this helps you automate the scripts and save a lot of your precious time. Communicate in the comments section in case of doubts. I will be happy to help.

Related