How to Read Google Sheets Data and Store It in SQLite with Python?

This tutorial demonstrates a simple automation workflow in Python using Pygsheets to extract rows of data from a Google Sheet and store them in a local SQLite database. By connecting to the sheet, reading structured form responses, and inserting them into a database table, the process eliminates manual data entry and creates a reliable foundation for further automation, such as invoice generation, reporting, or integration with web applications.

Prerequisite:

This tutorial is part of the Automated Sales Invoice Series.

📚 View the Complete Automated Sales Invoice Series

⬅ Previous Part                                                                                                                  ➡ Next Part

Preliminary
Before I begin, please activate the virtual environment and install the required dependencies.

python -m venv venv
venv\Scripts\activate
pip install dotenv pygsheet

However, since SQLite is a standard package, it is pre-installed

Then, we need to set up the file and folder structure, as below:

Everything remains as in the previous tutorial, and what is new here is to create 2 files: one is database.py, and the other is gsheet_to_sql.py. 

Usually, when users fill out the Google Form, a record will be caught in the Google Sheet. However, if I need to back up the data, I can avoid losing it or corrupting it with other data.

Step 1: Set up the SQLite database schema

Before I store the data, I need to create the SQLite database schema. The schema is simply a column for each field where the data will be inserted. 

Following is my code:

import sqlite3

# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('invoices.db')
cursor = conn.cursor()

# Create table (with duplicate protection)
cursor.execute("""
CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    timestamp TEXT,
    customer TEXT,
    address TEXT,
    email TEXT,
    description TEXT,
    rate REAL,
    quantity INTEGER,
    UNIQUE(timestamp, email)
)
""")

I have created a database named invoice.db and set up the respective column names and attribute type for each column.

Step 2: Read data from the Google sheet.

First, I will read the data from the Google Sheet by setting up authentication. I have performed this step in part 1; please refer to the earlier tutorial.

import pygsheets
import os
from dotenv import load_dotenv

load_dotenv()

# Get the Google Service Account JSON file path from the environment variable
path = os.getenv("GSHEET_PATH")

# Authorise pygsheets using the service account file
gc = pygsheets.authorize(service_account_file = path)
  
# Open the Google Spreadsheet named 'Invoice_form'
sh = gc.open('Invoice_form')
  
# Open the specific worksheet (tab) named 'Form_responses'
wk = sh.worksheet_by_title('Form_responses')
print("Gsheet opened")

# Retrieve all records as a list of dictionaries
data = wk.get_all_records()

Then, I will find and open the Google sheet file ('Invoice_form') and its tab name (‘Form_response'). Once it has connected, I will retrieve the data from the Google Sheet row by row. 

Step 3: Store the data in a SQLite database

Before the data is stored in the SQLite database, I need to connect to 'Invoice.db,' which I have created in step 1. It will check how many rows to insert, and after inserting a row, it will automatically go to the next row. Finally, it will save all rows in the database and close the connection.

It is shown in my code below:

import sqlite3

# Connect to SQLite database (creates file if it doesn't exist)
conn = sqlite3.connect('invoices.db')
cursor = conn.cursor()
    
# Counter to track how many new rows are inserted
inserted = 0
    
# Loop through each row retrieved from Google Sheets
for row in data:
    
   # Insert data into 'orders' table
   # INSERT OR IGNORE prevents duplicate entries (based on UNIQUE constraint)
   cursor.execute("""
   INSERT OR IGNORE INTO orders (
      timestamp, customer, address, email, description, rate, quantity
    ) VALUES (?, ?, ?, ?, ?, ?, ?)
    """, (
        row["Timestamp"],
        row["Who is the customer?"],
        row["What is the address?"],
        row["What is the email address"],
        row["What is the description of the goods or services"],
        row["What is the rate?"],
        row["How much is the quantity?"]
        ))

   # cursor.rowcount > 0 means a new row was inserted (not ignored)
   if cursor.rowcount > 0:
       inserted += 1

# Save all changes to the database
conn.commit()
# Close the database connection to free resources
conn.commit()
conn.close()

Final wrap-up: 

This tutorial walks through a practical automation workflow where data is extracted from Google Sheets using pygsheets and stored in a SQLite database. By combining cloud-based data collection with local storage, you create a reliable and scalable pipeline that minimises manual work and ensures data consistency. This foundation can be easily extended into more advanced features such as automated invoice generation, email notifications, and integration with Flask-based web applications—making it a powerful solution for small business automation and internal tools.

Published: May 2026

Last Updated: May 2026

About the Author

Kelvin Loh is a Python developer focused on Flask, desktop applications, and business automation solutions. He shares practical tutorials and real-world coding projects to help developers and small businesses build useful applications.

🚀 Place Order Now 🖼️ View Real Projects ❓ Ask a Question First 💰Try the 98% Open Rate Method