Read and write google sheets from python

We all know the power of Google Sheets and its numerous utilities that helps the different types of businesses around the world. Many of the businesses like to perform their operations via Google Sheets. Moreover, they are not limited to business but to the developers too for collaborative documentation.

Though Google Sheets are a powerful collaborative way to perform data operations, most of the time there are so many manual tasks that may be tiresome.

In this blog, we are going to connect the Google Sheet with our Python program so that all the manual tasks can be automated. For example, we have to read a column of data and write its respective price from a CSV file which is in our local systems. There are so many possibilities once we connect these two powerful tools. So enough of the talking let's dive right into the procedures.

At first, we need to create a credential file to secure our connection with Google Cloud Platform which allows us to connect them.

Open this URL https://console.cloud.google.com/ to create a new project in the Google Cloud Platform console:

Dashboard

Now click on the My First Project drop-down on the top left corner. You will be shown the following pop up to create a new project:

Create a new project option

Click on the New Project Option:

New Project Naming Option

Now fill-up the form by adding your project name and if you have an organization listed you can add that also by clicking the browse option, otherwise leave it as it is. Then click on the create button after filling up all information.

This will take some time and you will get the notification about the creation of a new project as given below:

Dashboard for your brand new project

Now we have to enable Google Drive API and Google Sheets API. We can do that by searching on the top search bar with the same name:

Searching for Google Drive API

You will be redirected to the detailed page of the API searched as given below:

Google Drive API

Click on the Enable button. You will be redirected to the dashboard of the API you have enabled:

Google Drive API Dashboard

Now again repeat the same steps to enable Google Sheets API:

Searching for Google Sheets API

Enable the Google Sheets API:

Enabling the Google Sheets API

Now again you will be redirected to the dashboard page of Google Sheets API. Now we have to create a credentials file which we will be using later on for our python project.

Click on the Create Credentials button on the top-right corner of that page as shown below.

Create credentials on the top-right corner

Create Credential Page

Now in Select, an API choose Google Drive API from the drop-down:

Google Drive API Selection

Now choose Application Data and No option for the next question respectively as shown below and hit Next Button:

Now we will be redirected to the Service Account Details Page. Fill up the Service Account Name as per your desire and click on CREATE AND CONTINUE button:

Service Account Details Configuration

Now we have to give the created service account access. Give the Owner access from the dropdown as shown:

Giving owner access to the service account

Then click on continue:

Continue

Now skip the 3 numbered forms and click on DONE:

Completing the Service Account Form

Now you will be redirected to the credentials page where the recently created service account can be found:

Credentials List Page

Now on the last Details of Service Account, there will be a recently created service account email. Click on that:

Service Account Email

Then you will be redirected to the specific page for that service account:

Service Account Details Page

Now click on the KEYS tab on the top:

KEYS Page

Now click on the ADD KEY button on that page and Choose to Create New Key:

Create new Key

Choose the JSON Key Type from the Pop Up Prompt:

Choosing the Key Type

And click on the CREATE button.

The credentials file will be downloaded on your local system as a JSON file:

Private Key Saved in Local System

Now do not share this file with anyone. The name of the JSON file will be as per your service account details. Copy it in a secure folder and rename it as credentials.json as shown below:

Before Renaming

After renaming

Open up the credentials file and copy the client_email value from it.

Before diving into the python code, we have to create a spreadsheet to communicate.

Open https://docs.google.com/spreadsheets/u/0/ and create a new Spreadsheet:

New Spreadsheet

And click on the share button and paste the copied client_email from the credentials.json file before:

Share Button

Email Share Panel

Now Change the Sheet name from Sheet1 to Test:

Renamed sheet name

Add the following data in your spreadsheet or you can add your data as in the given format:

test.csv file

Spreadsheet with added data

Now that sheets are all set up it’s time to dive into our python code.

Open the folder on which credentials.json reside with Visual Studio Code:

VSCODE with credentials.json file

Now we have to create a virtual environment with python to download the dependency modules.

Create a requirements.txt in the same folder and paste the following into it:

requirements.txt

Requirement File

Now we have to create a virtual environment to install those dependency files.

Click on Terminal and Click on New Terminal and Enter the following command to create a new virtual environment

$ python3 -m venv pythontosheet

If you are having a problem creating the virtual environment go through these links

For Windows: https://docs.python.org/3/library/venv.html

For Ubuntu: https://linuxize.com/post/how-to-create-python-virtual-environments-on-ubuntu-18-04/

After creating the virtual environment you can see the file in your terminal by hitting the following command:

$ ls

Activate the created virtual environment by:

$ source pytosheet/bin/activate

Now finally install the requirements file with the following command:

$ pip3 install -r requirements.txt

If you have not installed Pip3 previously follow the guide below:

For Ubuntu: https://linuxize.com/post/how-to-install-pip-on-ubuntu-18.04/

For Windows: https://phoenixnap.com/kb/install-pip-windows

Now create a python script file with the name of pythontosheet.py and paste the following code in it:

pythontosheet.py file

Now go in the browser and copy the spreadsheet id from the URL as shown below:

Copying the spreadsheet ID

Just copy the text between the d/ and /edit and you are all set. After copying the ID, paste it in the code by replacing YOUR_SPREADSHEET_ID as shown below:

Pasting area for Spreadsheet ID

All set! It's time for some action.

Run the code and see for yourself!

$ python3 pythontosheet.py

Your output will be something like this:

— — Reading from Google Sheets — — — 
 — — — — — — — — — — — — — — — — — — 
 11 cells retrieved.
 — — — — — — — — — — — — — — — — — — 
[[‘id’, ‘name’], [‘1’, ‘Merola’], [‘2’, ‘Alonso’], [‘3’, ‘Alric’], [‘4’, ‘Claudie’], [‘5’, ‘Myrle’], [‘6’, ‘Jakob’], [‘7’, ‘Paula’], [‘8’, ‘Dierdre’], [‘9’, ‘Maritsa’], [‘10’, ‘Kenon’]] rows retrieved.

That’s it you have successfully read from the Google Sheet by using Python.

You can also write data into the Sheet. For that create a new tab in the Google Sheet and rename it as Write as shown below:

Creating a new tab on the Google Sheet

Now uncomment the last line of code and comment the second last on as given below:

#read_range()write_range()

Now again run the program with:

$ python3 pythontosheet.py

Your output will be something like this:

--- Reading from Google Sheets------
------------------------------------
        11 cells retrieved.
------------------------------------
[['id', 'name'], ['1', 'Merola'], ['2', 'Alonso'], ['3', 'Alric'], ['4', 'Claudie'], ['5', 'Myrle'], ['6', 'Jakob'], ['7', 'Paula'], ['8', 'Dierdre'], ['9', 'Maritsa'], ['10', 'Kenon']] rows retrieved.
--- Writing from Google Sheets------
------------------------------------
        22 cells updated.
------------------------------------

Finally, check out the Google Sheet in the newly created Write Tab:

Written Google Sheet from Python Program

That is it! Congrats you have successfully communicated between the Python and Google Sheets!

Thanks for reading!

More content at plainenglish.io

How do you read and write in Google Sheets using Python?

Can Python work with Google Sheets?

Can I Automate Google Sheets with Python?

How do you access Google Sheets from Python?