Append rows to Google Sheets from Python without creating duplicates (gspread)

You wrote a script that appends rows to a Google Sheet. It worked on Monday. On Tuesday the job failed halfway, you re-ran it, and now Monday's rows are in the sheet twice. Or you pressed "Run" twice while testing. Or a scheduled job started late and overlapped with the previous one.

append_row() does exactly what the name says: it appends. It has no idea whether the row already exists, so running the script twice gives you every row twice. If the sheet feeds a chart or a report, the numbers are now wrong and nobody notices.

The fix is to make the script idempotent: running it once or five times leaves the sheet in the same state. This post builds a small helper that does that and is safe to retry. It uses gspread 6.x.

Why duplicates happen

Four causes cover almost every case:

1. Re-runs. You fix a bug and run the whole job again. Rows from the first (partly successful) run are already there.
2. Retries. A request times out. The data may or may not have arrived. If your retry blindly calls append_rows() again, you can get duplicates. Appends are not idempotent.
3. Overlapping runs. A manual run and a scheduled run, or a slow run and the next scheduled one, execute at the same time.
4. Sloppy comparison. You do check for the row, but "42" (text) doesn't equal 42 (number), or the cell has a trailing space, so the check says "not found" and you append anyway.

All four have the same cure: before you write, look at what is already in the sheet, and decide per row whether to append, update or skip.

Step 1: Pick a key

A key is the value (or combination of values) that identifies a row. Good keys: an order ID, a date for a daily report, (date, store) for one row per store per day.

If your data has no natural key, create one. A hash of the fields that never change works well:

import hashlib

def row_id(*parts) -> str:
    raw = "|".join(str(p).strip().casefold() for p in parts)
    return hashlib.sha1(raw.encode()).hexdigest()[:12]

row_id("2026-10-31", "Ljubljana", "Acme d.o.o.")   # same input, same ID, every run

Put the key in the first column (or any column, and tell the helper which). You can hide that column in the sheet.

Step 2: The helper

Here is the whole thing. It reads the sheet once, compares by key, updates the rows that changed in a single request and appends the new rows in a single request.

"""Append new rows, update changed ones, never create duplicates."""
import random
import time

import gspread
import requests

RETRYABLE = {429, 500, 502, 503, 504}


def norm(value) -> str:
    """Make '42', 42, ' 42 ' and 'Acme ' compare equal."""
    return str(value).strip().casefold()


def key_of(row, key_cols) -> tuple:
    return tuple(norm(row[i]) if i < len(row) else "" for i in key_cols)


def sync_rows(ws, rows, key_cols=(0,), header_rows=1):
    """Upsert `rows` (a list of lists) into `ws`. Two requests at most."""
    existing = ws.get_all_values()  # request 1: one read

    index = {}  # key -> (sheet row number, current values)
    for n, row in enumerate(existing[header_rows:], start=header_rows + 1):
        if any(row):  # skip blank rows
            index.setdefault(key_of(row, key_cols), (n, row))

    incoming = {}  # de-duplicate the batch itself; the last row per key wins
    for row in rows:
        row = [c.strip() if isinstance(c, str) else c for c in row]
        incoming[key_of(row, key_cols)] = row

    to_append, to_update = [], []
    for key, row in incoming.items():
        if key not in index:
            to_append.append(row)
            continue
        n, current = index[key]
        current = (current + [""] * len(row))[: len(row)]
        changed = any(
            str(new) != str(old)
            for i, (new, old) in enumerate(zip(row, current))
            if i not in key_cols  # the key matched, so don't "fix" its case
        )
        if changed:
            to_update.append({"range": f"A{n}", "values": [row]})

    if to_update:  # request 2: every changed row in one call
        ws.batch_update(to_update, value_input_option="RAW")
    if to_append:  # request 3: every new row in one call
        ws.append_rows(
            to_append,
            value_input_option="RAW",
            insert_data_option="INSERT_ROWS",
            table_range="A1",
        )
    return {"appended": len(to_append), "updated": len(to_update),
            "unchanged": len(incoming) - len(to_append) - len(to_update)}


def sync_with_retry(ws, rows, tries=5, **kwargs):
    """Retry the WHOLE sync, so every attempt starts by re-reading the sheet."""
    for n in range(tries):
        try:
            return sync_rows(ws, rows, **kwargs)
        except gspread.exceptions.APIError as e:
            if e.code not in RETRYABLE or n == tries - 1:
                raise
        except (requests.ConnectionError, requests.Timeout):
            if n == tries - 1:
                raise
        time.sleep(min(2 ** n + random.random(), 60))

What the details are for:

• One read, at most two writes. The Sheets API limit counts requests, not cells, so this is the same cost for 5 rows or 5,000. batch_update writes all changed rows in one request and append_rows adds all new rows in one request. Calling append_row() in a loop is what runs into the 429 quota error.
• norm() on both sides. Keys are compared as stripped, case-folded text. That removes the 42 vs "42" and trailing-space problems from cause 4. get_all_values() returns every cell as text, so the helper compares text with text. That is also why the comparison of the non-key columns uses str().
• De-duplication inside the batch. If your input itself contains the same key twice (an API that returns overlapping pages is a common source), the last one wins instead of both being appended.
• value_input_option="RAW". Values are stored exactly as sent. Numbers stay numbers, and a date string like 2026-10-31 stays that exact text instead of being converted using the spreadsheet's locale. With USER_ENTERED, Sheets would turn that text into a real date shown in your locale, and your key comparison would stop matching.
• table_range="A1" and INSERT_ROWS. Appending works by finding the "table" in a range and writing below it. Pointing it at A1 and asking for inserted rows makes new rows go below the last row of the table instead of overwriting whatever is next to it.
• The retry re-runs everything. sync_with_retry doesn't retry just the failed request. It retries the whole sync_rows, which starts by reading the sheet again. If the first attempt actually wrote the rows before the connection dropped, the second attempt sees them and does nothing. That is what makes the retry safe.

Step 3: Use it

import os
import gspread
from sheet_sync import sync_with_retry

gc = gspread.service_account(filename="service_account.json")
ws = gc.open_by_key(os.environ["SHEET_ID"]).worksheet("Orders")

rows = [
    ["order_id", "customer", "total_eur"],   # header only matters on an empty sheet
    ["A-1001", "Acme", 120.50],
    ["A-1002", "Globex", 80],
]

if not ws.get_all_values():       # first run ever: write the header
    ws.append_row(rows[0])
print(sync_with_retry(ws, rows[1:], key_cols=(0,)))
# first run:   {'appended': 2, 'updated': 0, 'unchanged': 0}
# second run:  {'appended': 0, 'updated': 0, 'unchanged': 2}

Change the total of A-1002 to 95 and run again: you get {'appended': 0, 'updated': 1, 'unchanged': 1}. The row is edited in place. The sheet never grows.

For a composite key, pass several columns: key_cols=(0, 1) means the date in column A and the store in column B together identify a row.

Check it without touching a real sheet

Because the logic is plain Python, you can test it with a fake worksheet, including a blank row in the middle of the data:

class FakeWS:
    def __init__(self, data):
        self.data = [list(r) for r in data]
    def get_all_values(self):
        width = max(map(len, self.data))
        return [[str(c) for c in r] + [""] * (width - len(r)) for r in self.data]
    def batch_update(self, updates, value_input_option=None):
        for u in updates:
            self.data[int(u["range"][1:]) - 1] = list(u["values"][0])
    def append_rows(self, rows, **kwargs):
        self.data += [list(r) for r in rows]

ws = FakeWS([["id", "name", "qty"], ["A1", "Bolt", 5], ["A2", "Nut", 7], ["", "", ""]])
print(sync_rows(ws, [["a1 ", "Bolt", 5], ["A2", "Nut", 9], ["A3", "Washer", 2], ["A3", "Washer", 3]]))
# {'appended': 1, 'updated': 1, 'unchanged': 1}
print(sync_rows(ws, [["A2", "Nut", 9], ["A3", "Washer", 3]]))
# {'appended': 0, 'updated': 0, 'unchanged': 2}

"a1 " matched A1 despite the space and case, A2 was updated in place, and A3 was appended once, with the last quantity.

Gotchas

• Keep the table contiguous. Blank rows in the middle of your data confuse Sheets' "where does the table end" detection, and appended rows can land somewhere you didn't expect. Delete the gaps, or reset the range with a fixed table_range.
• Big sheets. get_all_values() reads everything. For an append-only log with 50,000 rows, read only the key column instead: ws.col_values(1) is one request and much less data. Then compare against that set and skip the update logic.
• Two jobs at once. The helper is safe against re-runs, but there is still a race if two runs read the sheet at the same moment and both decide a row is new. Don't run them at the same time. In GitHub Actions, add a concurrency: group to the workflow so a second run waits for the first.
• Don't swallow errors. The helper retries only 429, 5xx and network errors, never a 403 (that's a permission problem, and retrying hides it). If it finally raises, let the job fail loudly instead of printing the error and exiting "successfully".

Recap

1. Appending isn't idempotent. Re-runs, retries and overlapping runs create duplicates.
2. Choose a key (or hash one), and normalise both sides before comparing.
3. Read once, then write changed rows with one batch_update and new rows with one append_rows.
4. Retry the whole sync, not the single failed request.
5. Test the logic with a fake worksheet before pointing it at a real sheet.

If you want to run this on a schedule without a server, I covered the GitHub Actions setup in this post, and the 403, invalid_grant and 429 errors in this one.

---

Disclosure: I also sell a starter kit that goes further with all of this (a guide, a tested toolkit with backoff and upsert helpers, a connection checker, ready-made GitHub Actions workflows and Sheet templates): https://whop.com/whop-0bab/python-google-sheets-running-on-autopilot-the-starter-kit/. Everything in this post works without it.

Comments

[Trung Thanh Tran]

Really clear write-up, especially retrying the whole sync instead of the single request.

One thing I'd add for sheets that people also edit by hand: get_all_values() returns the formatted text of each cell. If someone applies a number or currency format to the total_eur column, the sheet now returns "€120.50" while the script sends 120.5, so str(new) != str(old) is true on every run. No duplicates, but every row gets rewritten each time and the updated count stops meaning anything.

Reading unformatted values avoids that:

from gspread.utils import ValueRenderOption
existing = ws.get_all_values(value_render_option=ValueRenderOption.unformatted)

Numbers then come back as numbers, so 120.5 compares equal to 120.5 regardless of how the column is displayed.