By Prateek Jain —

How to interact (read / write) data from/to your Google Sheets using Python and Google Sheets API

Let’s jump right in!

How to interact (read / write) data from/to your Google Sheets using Python and Google Sheets API

Let’s jump right in!

Steps:
1. Enabling Google Sheets API
2. Creating Service / OAuth Credentials
3. Creating a function in Python to read / write data from / to Google Sheets.
4. Importing the function and using it

Enabling Google Sheets API

Pre-requisites: You should have a personal (yourname@gmail.com) or a company (yourname@yourcompany.com) Google Account

Steps:

  • Go to Google Console and login with your Google Account (if not already logged in)
  • On the header, create a new project: (Click on the dropdown that says “My First Project” in my case)
  • From the dialogue box that opens up, click on “New Project” and give your project a name of your choosing and under location you can either keep it as default (No Organization) or select your organization, if listed.
    Click on Create.
  • Google Cloud will automatically create a project for you and once it’s done, select the created project from the same dropdown in the header as done in Step 2.
  • After selecting your newly created project, your screen should look something like this:
  • In the search bar, search for “Google Sheets API” and click on the first result and click on “Enable” on the screen that follows.

Congratulations! You have successfully enabled the Google Sheets API for your account. Now we’ll set up our credentials so that our Python Script can authenticate and interact with the API.

Creating Service / OAuth Credentials

After the above steps, you should have landed on a page which looks like this:

From the side navigation bar, click on “Credentials” and click on “+ CREATE CREDENTIALS” from the sub-header and select “OAuth client ID”. (We will be setting our credentials up using OAuth because that is what I personally prefer due to an advantage it gives us over Service Account Credentials.)

From the screen that follows, click on “CONFIGURE CONSENT SCREEN”.

If you are setting this up from your company’s Google Account, then click on “Internal”, otherwise click on “External” on the “User Type” section of the OAuth Consent Screen and click on “CREATE”.

You will then be taken to a 4-Step process of configuring your consent screen.

  • On the “App Information” page, fill in your “App Name” (It can be a random name since you are not opening up this functionality to any third party user and is entirely internal to your use), your email ID under the “User support email” and the “Developer contact information” field (towards the end of the page) and click on “SAVE AND CONTINUE”. You may leave the remaining fields empty since they are optional.
  • On the “SCOPES” page, click on “ADD OR REMOVE SCOPES”. From the page that pops up, search for “Google Sheets API” as shown below and select all the scopes that show up and click on “UPDATE”.
  • After clicking on Update in the above screenshot, this page will automatically close and you will see your scopes added on the page. Scroll down to the bottom and click on “SAVE AND CONTINUE”.
  • Under Test Users add your email ID as the test user and directly press “SAVE AND CONTINUE”. (This page will not show up if you are on a company account and you chose User Type as Internal on the first page.)
  • Scroll down on the Summary page and click on “BACK TO DASHBOARD”

Click on “CREDENTIALS” page on the side navigation bar again and click on “+ CREATE CREDENTIALS” and choose “OAuth Client ID”. Choose the Application Type as “Desktop App” and give it a random name and click on create. You will now a screen similar to below screen:

Download the JSON on your computer, we will be using this later.

Congratulations! Now you have successfully setup credentials to authenticate with your Google Sheets API.

Creating a function in Python to read / write data from / to Google Sheets.

At this point, I am assuming you already have python installed in your system and you are ready to install and use new libraries.

Before we begin writing our script, we require some libraries for our script to utilise. Install the libraries using the following commands: (Open your terminal and run the following commands)# Will upgrade your pip to the latest versionpip install --upgrade pip# Installing gspread library and a third-party library to convert data from gspread to a dataframepip install gspread gspread-dataframe# Installing oauth2client for authenticationpip install oauth2client

Once all the above commands run successfully, you will have downloaded the required libraries in your system.

Let’s jump right in to create functions which will help us read/write data from/to our Google Sheet.

In the code above, “credentials” variables refer to the file path to your client secrets JSON which you downloaded in the previous step and “storage” variables refer to your storage.json file path. (Remember, storage.json should be created as an empty JSON file while running the code for the first time for the code to automatically generate access tokens and store in your storage.json file for the subsequent uses)

The “sheet_name” variable refers to the sheet in your workbook from / to where you want to read / write the data. For example, in your Google Sheet, you have a tab named “Sheet 1”, then “Sheet 1” shall be passed in your as your sheet_name argument.

The “dataframe” variable in the googleSheetWrite function takes in the dataframe which needs to be posted in the given sheet_name.

The “sheet_url” variable takes in the complete URL of the workbook where all this data needs to be written.

The “boolean” variable in the googleSheetWrite function takes in True or False values. If you pass True, then the function will remove all existing data from the sheet and paste your mentioned dataframe in the sheet else it will overwrite only the cells according to the shape of your dataframe.

The “row” and “col” variables take in integer values to mention the Row Number and Column Number from where the dataframe needs to start pasting. For example, if your row = 1 and col = 1, the the data will be pasted from A1, if row = 2 and col = 1, then the data will be pasted from A2, if row =1 and col = 2, then the data will be pasted from B1 and so on.

In the googleSheetRead function, “col” variable refers to an array of column numbers to be read as a dataframe from the given sheet_name and sheet_url.

For example, if you want to read data from columns A,B,C in the sheet,
col = [0,1,2], if you want to read only columns A and C, then,
col = [0,2].

Please note that the googleSheetRead function will also return the empty cells in the sheet as null values. You can drop the null rows using dropna() function of pandas.DataFrame.

Congratulations, now you can easily read / write data from any Google Sheet that is shared with an Editor Access with the Google Account that you generated the credentials from!