LAKSHAY ARORA — July 30, 2020
Beginner Excel Programming Python

Overview

  • Learn how to setup a Google service account
  • Read and Write data in Google Spreadsheets using Python

 

Introduction

Automation of work has been one of the quickest ways to reach functional efficiency. Moreover, in today’s era where success is dependent on speed, automation of myriad repetitive tasks play a key role in any industry and at the most basic level of functionality. But many of us fail to understand how to automate some tasks and end in the loop of manually doing the same things again.

google spreadsheets with python

For instance, we often spend hours daily extracting data and then copy-pasting to spreadsheets and creating reports leading to excessive time consumption. Consequently, it would be great if we just run a script, and data is uploaded in the spreadsheet and the report is prepared with just a click. There are multiple advantages of report automation like you would be able to save time on data collection and removing typos and 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 make use of the Google APIs to read google spreadsheets data using python and we will also update the data in the spreadsheet using python. We are going to read the cricket commentary data from the spreadsheet and find out the number of runs scored by each batsman and then upload the results into a separate spreadsheet.

In case you are unfamiliar with Python, do have 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

In order to read and update the data from google spreadsheets 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 of all, make sure that 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’s console.  Now, you will see something like this. Click on the Create Project button.google spreadsheets with python : Create project
  2. Then provide the project name and the organization name which is optional. Then click on the create button.google spreadsheets with python : new project name
  3. Now, that our project is created we need to enable the APIs that we require in this project. Click on the Enable APIs and Services button to search for the APIs that Google provides.google spreadsheets with python: APIs and services Consequently, we will add two APIs for our project.
    • Google Sheets API
    • Google Drive API

     

  4. Then, in the search bar, search for these APIs and click on the enable button.google spreadsheets with python: API search bar
  5.  Google Sheets API will look something like this. It will allow you to access Google Spreadsheets. You would be able to read and modify the content present in the Spreadsheets.google spreadsheets with python: enable
    Google Drive API will look something like this. It will allow you to access the resources from Google Drive.google spreadsheets with python
  6. Once you have enabled the required APIs in your project then it’s time to create credentials for the service account. Click on the Create Credentials button to continue.google spreadsheets with python:overview

     

  7. Now, select Google Drive API in the type of API required question. We will be calling the API from a non UI based platform so select Other non-UI (e.g. cron job, daemon). Select the Application Data in the next question as we do not require any user data to run our application. And also we are not using any cloud-based compute engine for our application. Finally, click on the What credentials do I need? button.
  8. Then, share the google spreadsheets with other people and provide permission like edit or view only. Similarly, we will provide access to our service account. We will give it the complete access so that we will be able to read as well as write the spreadsheets and download the JSON file of the credentials.

 

Now, a JSON file will be downloaded which contains 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 of the India Bangladesh cricket match. You can access the data here.

google spreadsheets with python : data

We have a ball by ball data of the complete match in the spreadsheet. Now, we will do a very basic task and calculate how many runs are scored by each of the batsmen. We can do this by using a simple groupby in pandas. And finally, we will upload the results in 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’s console. Look for the client_email in the JSON file and copy it.

google spreadsheets with python: client email

Then click on the Share button on the Spreadsheet and provide access to this client email.

google spreadsheets with python : Provide access to google sheet

Now, we are ready to code and access the sheet using python. The following are the steps-

1. Importing the Libraries

We will use the gspread and 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

Then, 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 the argument. Also, you can pass the URL of the sheet if you want to do so.

Access Particular Sheet: We have multiple sheets in a single spreadsheet. You can access particular google spreadsheets with python by providing the index of 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 functionalities such as the number of columns by using col_count and get the value in a particular cell. Here are some examples of the same.

 

4. Get all records   

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

 

google spreadsheets with python: all records

5. Convert the Dictionary to the Dataframe

In data science, pandas is one of the most preferred libraries to do data manipulation tasks. So we will first convert the JSON string to the pandas dataframe.

In case you are not comfortable with the pandas, I would highly recommend you to enroll in this free course: Pandas for Data Analysis in Python

google spreadsheets with python: dataframe

6. Grouping Batsman

Then, we will create a groupby of the number of runs scored by a batsman and upload that dataframe in the separate sheet.

google spreadsheets with python grouping batsman

Now, we will add this dataframe into the google sheets.

 

Update Data in Google Sheets

The following are steps to update data in google sheets.

  1. Create a Separate Sheet

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

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

    google_spreadsheets_with_python - create a spresdsheet

  2. Update values to the sheet

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

    google_spreadsheet_with_python : Update values to the sheet

    google_spreadsheets_with_python : update values to the sheet

     

 

End Notes

To summarize, in this article, we dived into understanding various steps involved in the process of creating a service account. And how to read the write in the google spreadsheets right from your python console. We downloaded the spreadsheet data and converted it into the pandas dataframe and created a groupby table and uploaded that on the spreadsheet again. This API can be very helpful in the automation of reports.

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

I hope this helps you in automating scripts and saving loads of your valuable time. Reach out in the comment section in case of any doubts. I will be happy to help.

 

 

About the Author

LAKSHAY ARORA

Ideas have always excited me. The fact that we could dream of something and bring it to reality fascinates me. Computer Science provides me a window to do exactly that. I love programming and use it to solve problems and a beginner in the field of Data Science.

Our Top Authors

  • Analytics Vidhya
  • Guest Blog
  • Tavish Srivastava
  • Aishwarya Singh
  • Aniruddha Bhandari
  • Abhishek Sharma
  • Aarshay Jain

Download Analytics Vidhya App for the Latest blog/Article

12 thoughts on "Read and Update Google Spreadsheets with Python!"

puneet wadhwa
puneet wadhwa says: August 01, 2020 at 5:46 pm
Hi, unable to open commentary data from spyder, console throws SpreadsheetNotFound error. while sharing the spreadsheet on email in the json file the mail bounced back. Reply
LAKSHAY ARORA
LAKSHAY ARORA says: August 24, 2020 at 11:52 am
Hi Puneet, Please make sure that you have a copy of the spreadsheet on your google drive and the spreadsheet is shared with your Google service account. Reply
Stalin R
Stalin R says: October 26, 2020 at 9:40 pm
Bro thankyou very much. It worked... Reply
EMINA TUZOVIC
EMINA TUZOVIC says: October 29, 2020 at 9:34 pm
Hi, unable to open commentary data from spyder, console throws SpreadsheetNotFound error. while sharing the spreadsheet on email in the json file the mail bounced back. I made sure that I have a copy of the spreadsheet on my google drive and the spreadsheet is shared with my Google service account. but it still won't work. Reply
JJ
JJ says: November 19, 2020 at 10:56 am
Hi, Thank you for the guide, When I tried the last step: sheet_runs.insert_rows(runs.values.tolist()) I received 400 errors: gspread.exceptions.APIError: {'code': 400, 'message': 'Invalid value at \'data.values[0]\' (type.googleapis.com/google.protobuf.ListValue), etc.. etc.. Can you help? Thank you Reply
Muskan
Muskan says: November 19, 2020 at 10:20 pm
How do i view the pandas data like shown in the images? I am using vs code. Thanks, Reply
William
William says: December 02, 2020 at 10:00 pm
this was awesome to read Reply
Deva
Deva says: December 04, 2020 at 4:14 pm
how to export data from external excel files to google spreadsheet using python Reply
Deva
Deva says: December 04, 2020 at 4:32 pm
-------------------------------------------------------------------------- AttributeError Traceback (most recent call last) in () ----> 1 sheet_runs.insert_rows(records_df.values.tolist()) AttributeError: 'Worksheet' object has no attribute 'insert_rows' i got this error when i use this code Reply
David S
David S says: January 07, 2021 at 1:07 pm
Awesome really easy to follow and worked thanks! Reply
Marta
Marta says: March 09, 2021 at 8:00 pm
Very helpful article! Thanks for that :) Reply
QRstuv.info — My Visitor’s Kiosk Hydra – MARTINsquared
QRstuv.info — My Visitor’s Kiosk Hydra – MARTINsquared says: March 15, 2021 at 10:32 am
[…] I followed this tutorial for connecting the 4.2″ WaveShare e-paper screen to Google Sheets! This tutorial helped me get up with the API needed for the project. Thanks, […] Reply

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