Forem: Nishant Patil

Integrate Google Sheets with Python: A Step-by-Step Guide

Nishant Patil — Thu, 03 Apr 2025 10:25:29 +0000

Want to learn how to connect your Google Sheets with Python to fetch and manipulate data? By using the gspread library alongside pandas, you can easily automate tasks, analyze data, and even save it in formats like JSON. Let’s walk through the process step-by-step—perfect for beginners and experienced users alike!

Step 1: Enable Google Sheets API & Get Credentials

To integrate Google Sheets with Python, you first need to set up access via the Google Cloud Console:

Visit the Google Cloud Console.
Create a new project (e.g., "Python-Google-Sheets").
Go to "APIs & Services" → "Library".
Search for Google Sheets API and click "Enable".
Search for Google Drive API and enable it too (required for file access).
Navigate to "APIs & Services" → "Credentials".
Click "Create Credentials" and choose "Service Account".
After creating the service account:
- Go to the "Keys" tab.
- Click "Add Key" → "JSON".
- A JSON key file (e.g., your-credentials.json) will download to your computer. Note: This JSON file contains your service account credentials. Keep it secure and don’t share it publicly!

Step 2: Install Required Libraries

You’ll need two Python libraries: gspreadfor interacting with Google Sheets and pandasfor handling data. Install them using pip:

pip install gspread pandas

Make sure you run this command in your terminal or command prompt.

Step 3: Load Google Sheets Data in Python

Now, let’s write some Python code to fetch data from your Google Sheet:

import gspread
import pandas as pd

*# Load credentials from the JSON file*
gc = gspread.service_account(filename="your-credentials.json")  *# Replace with your JSON file path# Open the Google Sheet by its name*
sh = gc.open("Your Google Sheet Name")  *# Replace with your sheet’s exact name# Select a worksheet (e.g., "Sheet1")*
worksheet = sh.worksheet("Sheet1")  *# Replace with your worksheet name# Fetch all data from the worksheet*
data = worksheet.get_all_records()

*# Convert the data into a pandas DataFrame*
df = pd.DataFrame(data)

*# Display the DataFrame*
print(df)

What’s Happening Here?

gspread.service_account() authenticates using your JSON credentials.
gc.open() accesses your Google Sheet by its title (found in the browser tab).
worksheet.get_all_records() retrieves all rows as a list of dictionaries, assuming the first row is headers.
pd.DataFrame() turns the data into a table-like structure for easy manipulation.

Step 4: Share Google Sheet with Service Account

Your Python script won’t work yet—your service account needs permission to access the sheet:

Open your Google Sheet in a browser.
Click the "Share" button (top-right corner).
Find the service account email in your JSON file (it looks like your-service-account@project-id.iam.gserviceaccount.com).
Add this email to the share list and grant Editor access.

Why? The service account acts like a user and needs explicit permission to read or edit the sheet.

Other Useful Methods

Once connected, gspreadoffers handy methods to interact with your sheet. Here are a few examples:

Get Data from a Specific Cell

value = worksheet.acell("B2").value  *# Fetch the value in cell B2*
print(value)

Get Data from a Specific Row or Column

row_values = worksheet.row_values(2)  *# Get all values in row 2*
col_values = worksheet.col_values(1)  *# Get all values in column 1*
print(row_values, col_values)

Append a New Row

worksheet.append_row(["John Doe", 25, "Engineer"])  *# Add a new row to the sheet*

These methods are great for tasks like updating records, extracting specific data, or automating reports.

Save Data to a JSON File

Want to save your Google Sheets data as a JSON file? Add this code after fetching the data:

import json
import os

*# Define the file path*
file_name = "./data.json"
dir_name = os.path.dirname(file_name)

*# Create the directory if it doesn’t exist*
if not os.path.exists(dir_name):
    print(f"\nDirectory {dir_name} does not exist.")
    print(f"\nCreating the directory: {dir_name}")
    os.makedirs(dir_name)
else:
    print(f"\nDirectory {dir_name} already exists.")

*# Check if the file exists and create or modify it*
if not os.path.exists(file_name):
    print(f"\nFile {file_name} does not exist.")
    print(f"\nCreating a new file: {file_name}")
else:
    print(f"\nFile already exists, modifying the file.")

*# Save the data to JSON*
with open(file_name, "w") as json_file:
    json.dump(data, json_file, indent=4)

What’s Happening?

The script checks if the directory and file exist, creating them if needed.
json.dump() writes the data to data.json with nice formatting (indent=4).
The result is a JSON file you can use elsewhere, like in a web app or for backups.

Conclusion

You’ve now learned how to integrate Google Sheets with Python! From setting up the API and fetching data to saving it as JSON, this setup opens up endless possibilities for automation and analysis. Try it out with your own Google Sheet—what will you build with this power?

Feel free to experiment with the code and share your ideas!

A Beginner's Guide to Searching for Elements in Arrays with JavaScript

Nishant Patil — Fri, 14 Apr 2023 19:03:07 +0000

Lets talk about searching an element in an array. There are various algorithm used for this purpose, but the one which I am going to discuss is Linear Search. Linear Search is simple and easy to understand. There is also a default javascript method for for linear search, will will talk about it later.

Linear Search is simple it uses a for loop, which loops through the given array and returns its index. If the index is a value then we know that the value exist , if its undefined then the value is absent.

  let data =[20,40,60,5,10,70,80,99];
        let item=70;
        let index=undefined;

        for(i=0;i<=data.length-1;i++){
             if(data[i]===item)
             {
                index=i;
                 break;
             }
         }
         console.log(index);

         // default method to search an element in array
         console.warn(data.indexOf(item))

You can copy paste the above code and test it.
The defualt funciton used of searching an element in array is indexOf() method. By passing the element that you have in array inside indexOf() method it will return its index.

Delete an element in array

Nishant Patil — Thu, 13 Apr 2023 17:44:20 +0000

Theory :
Consider a Array declared as
let arr=[1,2,3,4,5]

Suppose we want to delete the element at index 2, that means we have to replace the element at index 3 to position at index 2 and same for all other elements .

Suppose we want to delete the element at index n. We will do this by replacing the element at index n+1 to n, replacing the element at index n+2 to n+1, similarly for all other elements. And once the element is deleted and all other elements are at new place, then we will decrease the length the of array by 1

let data  =[30,20,45,76,20,80];
let position =3;
for(let i=position; i<data.length-1;i++){
    data[i]=data[i+1];
}
data.length= data.length-1;

"How Does JavaScript's splice Function Really Work?"

Nishant Patil — Thu, 13 Apr 2023 06:28:54 +0000

JavaScript splice method is used to add a new element in an array without deleting any element in it. Lets learn how splice method works behind the scene.

Theory :
Consider a Array declared as
let arr=[1,2,3,4,5]

Suppose we want to add new element at index 2, that means we have change the position of element at index 2 by replacing it somewhere else and add a new value there.

We will do so by replacing the position of last element to it's n+1 position (if there are n elements consider) which means we will replace the number 5 to a new position at index 5 (remember indexing starts from 0). Similarly we will shift the second last element to the position of last element and this process will continue until the element at position that we have to place the new number is replaced. This will create a empty space at index 2 where we will will insert our element.

let data =[60,30,10,67,40];
let newEl= 70;
let position=2;
for(let i=data.length-1;i>=0;i--){
      if(i>=position){
          data[i+1]=data[i];
          if(i==position){
              data[i]=newEl;
          }
      }
}

Practical explanation :
As seen from the code we have declared some variables, array, new element and position where we want to enter new element.

Next a for loop is created which will starts from the last element.
First the loop will changed the position of element as discussed above.
If the the position where we want to insert element will reach, the loop does adds a new element to that position.

Thats how the splice() method in JavaScript works behind the scenes. The code syntax may be different but logical and approach is same.