How to use gspread to get and update Google Sheets

Google Sheets is an everyday tool for data analysts, especially in companies that run on Google Workspace. I use Google Sheets for reports and dashboards myself, and the data in them is updated every day, automatically, with Python. Below are some of the things I have learned from using gspread to work with Google Sheets.

Preparation

To use gspread or Google Sheets API in general, you need to create a Service Account and get its JSON file. This makes authorization in Python much easier.

Once you have a Service Account, open the Google Sheets sharing settings and add the Service Account email with Editor access. The email is the client_email field in the JSON file you downloaded.

Make sure you have Python installed on your machine, then install gspread with the following command:

pip install gspreadCopy

Use gspread to get data from Google Sheets

Get all the data in the worksheet

If the worksheet holds a standard table (the first row is the header and the other rows are values), use get_all_records() to get the data from Google Sheets.

import pandas as pd
import gspread

client = gspread.service_account(filename='service_account.json')
sheet = client.open_by_key(key='YourSheetsKey')
worksheet = sheet.get_worksheet_by_id(id=0) # Replace 0 with your worksheet ID

data = worksheet.get_all_records()
df = pd.DataFrame(data)Copy

If the worksheet is not structured as a table, you can get all of its rows (a list of lists) with get_all_values(). However, you will need to parse and clean the data yourself.

data = worksheet.get_all_values()Copy

gspread also has functions to open a spreadsheet or worksheet by name. In my experience, though, using key and id is still the safest option because they never change, while names can be edited, and several sheets with the same name will cause errors.

Get data from a range in a worksheet

When using get_values() to get data from a range in a worksheet, you will get the values back as a list of lists.

data = worksheet.get_values(range_name='A1:B2')Copy

Get formulas instead of values

You may not know this, but besides values, gspread can also return the formulas in your cells. This comes in handy in special situations, such as updating every formula at once by replacing a string.

data = worksheet.get_all_records(value_render_option='FORMULA')Copy

You can use the value_render_option='FORMULA' parameter with the other read functions too.

Use gspread to update data into Google Sheets

With most gspread update functions, I add the parameter value_input_option='USER_ENTERED' because it makes the worksheet format the data correctly, just as if you had typed it in by hand.

Replace all old data

Below is the code I usually use to update data in Google Sheets with gspread. It does three things:

1. Connect to the worksheet.
2. Delete all rows from row 2 down.
3. Write all the data from the DataFrame to the worksheet, including the header.

import pandas as pd
import gspread

df = pd.DataFrame({'name': ['iPhone', 'iPad', 'MacBook'], 'profit': [7200, 8600, 9500]})

client = gspread.service_account(filename='service_account.json')
sheet = client.open_by_key(key='YourSheetsKey')

worksheet = sheet.get_worksheet_by_id(id=0) # Replace 0 with your worksheet ID

# Delete old data but keep the header
worksheet.delete_rows(start_index=2, end_index=worksheet.row_count) 

# Update new data
worksheet.update(values=[df.columns.tolist()] + df.values.tolist(), value_input_option='USER_ENTERED')Copy

You can also pass a range_name parameter to update() to choose which area to update.

worksheet.update(values=[['iPhone', 7200]], range_name='A2:B2', value_input_option='USER_ENTERED')Copy

Note: Since gspread v6, the first two arguments of update() have swapped to values first and range_name second (version 5.x was the other way around), and values must be a list of lists. That’s why I always pass arguments by name as above, so the code works on both v5 and v6 without worrying about the order. I have checked the code in this post against gspread 6.2.1 (10/2026).

Append data below the worksheet

This is what most people call “append”. When appending data, you don’t need the header from the DataFrame.

worksheet.append_rows(values=df.values.tolist(), value_input_option='USER_ENTERED')Copy

Update a specific cell

To update a single cell, you can use update_acell() or update_cell(). The only difference is how you specify the cell.

worksheet.update_acell(label='A1', value='Hello World!')

# The same as
worksheet.update_cell(row=1, col=1, value='Hello World!')Copy

Update multiple ranges at once

batch_update() is ideal when you need to update many cells or ranges at the same time, instead of calling an update function over and over.

worksheet.batch_update([
    {
        'range': 'A1',
        'values': [['Hello World!']]
    },
    {
        'range': 'B2:C3',
        'values': [['Name', 'Birthday'], ['Hieu Tran', 1996]]
    },
    {
        'range': 'D1:D3',
        'values': [['Guide'], ['From'], ['Lucid Gen']]
    }
])Copy

Advanced: To update many ranges at once in a fully automated way, you need to combine gspread with other libraries like pandas and openpyxl. Suppose you want to update only the cells that changed in a DataFrame. Here is the approach I use:

1. Create a row_number column from the DataFrame index.
2. Filter the rows that have changed.
3. Create a list of columns to update.
4. Use a for loop to build the batches automatically, using get_column_letter() and row_number to create each range.
5. Finally, use batch_update() to update all changed cells.

from openpyxl.utils import get_column_letter

# Assume df is data from worksheet
df['row_number'] = df.index + 2 # +2 for the header row and 0-based index

# Some values in df have changed. Filter the rows you want to write back
df_filter = df[df['status']=='changed'] # Replace with your condition

# Columns you want to write back to the worksheet
update_cols = ['profit', 'revenue']

batches = []
for col in update_cols:
    col_letter = get_column_letter(df_filter.columns.tolist().index(col) + 1) # +1 because Python indexes start at 0
    for row_number, value in zip(df_filter.row_number, df_filter[col]):
        batch = {'range': f'{col_letter}{row_number}', 'values': [[value]]}
        batches.append(batch)

worksheet.batch_update(data=batches, value_input_option='USER_ENTERED')Copy

Conclusion

This article covers the situations where data analysts most often use gspread with Google Sheets. The library has plenty of other functions that we rarely need. If you are curious, take a look at the official documentation. Leave a comment below if you have any questions!