Read and Write Data in Google Sheets using Python and the Google Sheets API

Welcome!

In this blog, I will be showing you how to read and write data in Google Sheets using Python and the Google Sheets API.

If you prefer to watch a video instead of reading, I’ve got you covered:

Welcome to Chartmat! Are you tired of struggling to make sense of your data in Google Sheets? Do you want to turn your sheets into powerful apps and dashboards that can help you make better decisions and drive your business forward? Look no further! With Chartmat, you can easily transform your Google Sheets into beautiful and interactive dashboards and apps. Whether you’re a small business owner, a marketer, or a data analyst, Chartmat has something for everyone. With our easy-to-use platform and extensive range of features, you’ll be able to turn your data into actionable insights in no time. So why wait? Sign up for Chartmat today and start unlocking the full potential of your data! Check out the link given below to know more about charmat!

Chartmat | Build powerful apps on top of Google Sheets. Start for free today.

Sample Google Sheet

The Google sheet that I will be using for this blog contains the following details:

1. Name of the Student
2. Roll number of the student
3. Score in math
4. Score in Spanish

We will interact with this data in the Google Sheet using python and the Google Sheets API. If you want to work with the Google Sheet, click here.

Step 1: Create a new project on the Google Cloud Console

To interact with the Google Sheet using python and the Google Sheet API, we have to create a new project on the Google Cloud Console. We have to invoke the Google Sheets API and use the credentials received to interact with the Google Sheet using python to create the project.

To go to the Google Cloud Console, all you have to do is open a new tab in your browser and type in Google Cloud Console or you can click here.

On reaching the Google Cloud Console, you will see a screen shown below.

If you have already created projects on the Google Cloud console, you will see something like this. To create a new project, you have to click on the name of the current project you are in at the top.

You will see a list of projects you have created and the new project button on clicking the button.

On clicking the new project button, you will see the following.

Here at the top, you can see the number of projects left in your quote. On creating a new project, you have to name your project and then select an organization (option). After adding the required project details, go ahead and click on CREATE.

You will be taken to the project dashboard to create the project, which will look like this.

Step2: Add the API's to your project

Now that we have successfully created the project, we can add the API. We will be adding the Google Drive API and the Google Sheets API for this blog.

To add APIs to your project, click on Go to API's overview. On clicking Go to APS's overview, you will be taken to the API's dashboard. To add APIs to the project, you must click on the library button to show all available APIs.

This is the API library to search and add API to your project. The first API we will add to this project is the Google Drive API. After searching for the API, go ahead and enable the API.

Step 3: Create a Service Account

Now that we have enabled the API, it's time to get some credentials that we will be using to interact with the Google Sheet. To get the credentials, follow these steps:

1. Go to the credentials section by clicking on Credentials.

2. At the top, click on Create Credentials.

3. On clicking Create Credentials, you will be provided with various options credentials, but we will use a service account to interact with the Google Sheet for this blog.

So go ahead and click on Service account.

4. On creating a service account, you will have to fill in some details for the service account.

Here I have added the service account's name. After entering all the details, go ahead and click CREATE AND CONTINUE.

5. Now that the account is created, it's time to assign a role to the account.

Since our primary goal is to use this account to interact with the Google Sheet, we will give it an Editor role. The editor role allows you to read and write data to the sheet, whereas the viewer role will only allow you to view the data in the Google Sheet.

After assigning a role to the service account, click on continue.

After putting in all the details, go ahead and click on Done.

Step4: Get the Credentials

Now that we have successfully set up the service account, it's time to get the credentials. To do that, follow these steps:

1. Click on the Service account you just created.

2. At the top, click on KEYS.

3. Click on ADD KEY and click on Create a new key.

4. On creating the key, you will be provided with options for the key type. Just go ahead and click on JSON.

After clicking JSON, go ahead and create the key.

On clicking CREATE, a JSON file will get downloaded to your computer. Be careful with the JSON file, as it contains some important details that we will need to interact with the sheet. We will be using that JSON files credentials to work with the Google Sheet.

Step5: Add the Google Sheets API

This is the last step in the setup process. We have to follow the steps we did previously to add the API.

1. Go to the API and overview.
2. Go to Enable API and services.
3. Please search for the Google Sheets API and enable it.

Step6: Install the Gspread Library

Now that we have everything ready, it's time to install the library that we will use. For this blog, we are going to be using the gspread library. To do that, follow these steps:

1. Go to your Command Prompt or Terminal.
2. Type in the following command.

pip install gspread oauth2client

3. The library will be installed and ready for use in a few seconds.

Step7: Add the service account email to your Google Sheet

Before writing our Python script to read and write data in the Google Sheet, we will first add the service account to the Google Sheet. To do that, follow these steps:

1. Go to the JSON file you created and get the service account email address.
2. After getting the email address, go to your Google Sheet and click on the SHARE button.

3. Here, you can paste in the email address you just copied.

Step8: Write the Python script

Now that we have everything ready, go ahead and create a new folder and store the JSON file. Also, please create a new Python file in which we will be writing the scripts to read and write data from the Google Sheet.

After creating your folder, open it in your editor. I use Visual Studio code and recommend you use the same. If you want to download Visual Studio Code, click on this link.

Here I have both my files stored in one folder.

Let's get coding.

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from pprint import pprint as pp

Here I have started importing the gspread library using the import command followed by the library name (gspread). Next, I am importing the service account credentials from the service account. Next, I have imported the pprint library. This library is also a pretty print used to customize your output.

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

Here we have added some scope that we require to work with the Google Sheet, copy them into your python file.

creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json",scope)client = gspread.authorize(creds)

Now we will be getting the credentials from the JSON file using the from_json_keyfile_name function, in which we are going to be passing the name of the JSON file followed by the scopes we just declared. After getting the creds, we will authorize them using the gspread.authorize function.

sheet = client.open("Students Marks ").sheet1   
data = sheet.get_all_records() 
pp(data)

Here we have opened the sheet using the client.open function and passed the name of the sheet as the argument, and specified the sheet we want to open
(.sheet1).

Next, to get all data from the Google Sheet, we use the sheet.get_all_records function and store in a variable. On getting all the data from the Google Sheet, we print it.

Here you can see that all the data from the Google Sheet has been successfully printed.

row = sheet.row_values(3)
pp(row)
col = sheet.col_values(3)
pp(col)

Now that we learned how to get all the data from the sheet and print it out let us get a specific row of the Google Sheet.

To do that, we are going to be using the sheet.row_values function that takes the row number as an argument. Here I have passed the three as a parameter(which means I want to print the 3rd row) and assigned it to a variable. If I go ahead and print it out, you can see that the third row has been printed.

Here, the third row from the Google Sheet has successfully been printed. To print a specific column from the sheet, we follow the same steps but pass the column number in the col_values function.

On successful execution, you can see that the third column of the Google Sheet has successfully been printed.

cell = sheet.cell(1,2).value 
pp(cell)

Next, we will print the value of a particular cell in the Google Sheet. To do that, we will be using the .cell function in which we pass the row and the column number of the cell you want to print. Here I have passed 1,2 in the .cell function followed by .value function that gets the cell's value.I have printed them out on getting the values using pp(cell).

On successful execution, the cell value has been printed.

We have learned how to get data from the Google Sheet. Now we are going to set values in the Google Sheet. We will add a row to the Google Sheet and delete a row in the Google Sheet.

insertRow = ["Zayn","Malik",12,20]
sheet.insert_row(insertRow,6)
print("The row has been added")

Firstly, I have declared the row I want to add to a Google Sheet using a list. In this list, I have stored the first and last name of the student, followed by the marks scored in maths and Spanish.

Now that we have the row that we want to insert ready, we will be using the .insert_row() function to insert the row into the Google Sheet. Inside this function, we will be passing the data for the row (insert_Row list), followed by the row number where you want to add the list. In the end, I have printed a confirmatory message that tells us that row has been added to the Google Sheet.

This is the sheet before we run the insert_row function.

Here you can see that the message has been printed on successful execution of the script.

If we go back to the sheet, we can see that the row has been added to the Google Sheet.

Now that we have learned how to add rows to the Google Sheet, we will delete rows from the Google Sheet.

sheet.delete_row(2)
pp("The row has been deleted")

To delete a row from the sheet, we will be using the .delete_row() function in which we will be passing the row number you want to delete. So here I have passed two, which means using the delete_row function, I want to delete the second row of the Google Sheet. After declaring the delete_row function, I am printing a confirmatory message that tells us that the row has been deleted from the Google Sheet.

This is the Google Sheet before we run the delete_row function.

Here you can see the message has been printed on successful execution of the script.

On successful execution, the row has been deleted from the Google Sheet.

Summary

We saw how you could read and write data in Google Sheets using python and the Google Sheets API. I hope you have understood how to read and write data in Google Sheets.

Feel free to reach out if you have any issues/feedback at aryanirani123@gmail.com.