How to Connect Google Sheets to SQLite

This tutorial shows you how to query a SQLite database from Google Sheets with Python and xlwings PRO. SQLite is an open-source, file-based database that’s very powerful yet super easy to set up. The Litestream project has further enhanced SQLite’s capabilities, allowing you to continuously stream database changes to an S3-compatible storage. This gives you the power to restore your SQLite database to a specific point in time, a feature that was previously reserved to server-based databases. For this tutorial, however, we’ll leave Litestream aside and concentrate on querying a SQLite database directly from Google Sheets. This saves you from the manual and error-prone tasks of having to export data from SQLite into CSV files, only to import them again into Google Sheets. You’re also able to give users read-only or write access to specific data.

Looking for a different database?

PostgreSQL | MySQL | MariaDB | SQL Server | Oracle

Table of Contents

1. Quickstart
2. Show me the code!
3. Development setup
4. xlwings vs. Google Sheets add-ons for SQLite
5. Conclusion

Quickstart

Prefer watching a video? Here’s the quickstart in video form:

You’ll be using xlwings’ runPython function in Google Apps Script to call the Python backend that runs on your server or cloud service. To get a feeling for how quickly you can get this to work, follow these steps:

1. Head over to the project on GitHub: xlwings-googlesheets-sql
2. Click the Deploy to Render button on the project’s README to spin up a free Render app (you can also fork the repo first on GitHub to be able to make changes to it). Instead of Render, you could also deploy the app to any service that can deal with Python or Docker.

3. Fill in the following information on Render: (1) A name, e.g., xlwings-googlesheets-sql
   (2) Your Google workspace domain(s) in the notation of a Python list: ["your-domain.com"] or ["your-first-domain.com", "your-other-domain.com"]. These are the Google workspace domains that will be granted access to the app.
   (3) Your xlwings PRO license key (get your free trial key here). Note that xlwings PRO is free for non-commercial use, see the docs.
   (4) Your database connection string: sqlite:///sqlite-data/xlwings.db. Note that this is a relative path, which makes the connection string work for local setups as well as when running it from inside a Docker container (an absolute path would require an additional slash: sqlite:////sqlite-data/xlwings.db). This path points to the xlwings.db file in this repository, in the sqlite-data folder.

   After hitting Apply, it will take a minute or two until your app is up and running. Check the progress on Render’s Dashboard (click on the Render logo on the top-left to get there).

4. Create a copy of the Google Sheet template by clicking on Use Template at the top right.
5. In Google Sheets, open the Apps Script editor by going to Extensions > Apps Script, then paste in the URL of your Render app (you’ll find it when clicking on your project on the Render Dashboard, at the top of the page).

6. In Google Sheets, click the Run button to query the database, optionally providing parts of a job title in B5 and (un)checking the salaried? checkbox:

   Note: when you run this the very first time, the script will ask for the required permissions, which you will need to confirm. Once confirmed, run the script again.

After this quick introduction, let’s have a look at what happens behind the scenes and how you can change the SQL query.

Show me the code!

The sample app is a simple FastAPI web app and the whole magic happens in the select endpoint under app/api/employees.py (check the repo for the full code):

@router.post("/select")
def select_employees(
    data: dict = Body,
    current_user: User = Security(authenticate),
    db: Session = Depends(get_db),
):
    # Spreadsheet objects
    book = xw.Book(json=data)
    sheet = book.sheets[0]
    result_cell = sheet["D1"]

    # Get the query parameters as dictionary
    params = sheet["A5:B5"].options(dict, expand="down").value

    # You can log who is running the query
    logger.info(f"Running 'select employees' query for user {current_user.email}")

    # SQL Query using SQLAlchemy placeholders
    sql = """
    SELECT *
    FROM employees
    WHERE salaried_flag = :salaried_flag
    """
    if params["job title"]:
        sql += "AND LOWER(job_title) LIKE LOWER(:job_title)"

    # Execute the query via SQLAlchemy
    result = db.execute(
        text(sql),
        {
            "salaried_flag": params["salaried?"],
            "job_title": f"%{params['job title']}%",
        },
    )

    # Delete existing data in the spreadsheet and write the result back
    result_cell.expand().clear_contents()
    result_cell.value = process_cursor_result(result)

    return book.json()

Note: while the repo uses FastAPI, you can use xlwings with any other Python web framework, including Django, Flask, etc.

As you can see from the snippet, xlwings makes it easy to read in the parameters from Google Sheets (the checkbox automatically returns as True or False). db is a SQLAlchemy session that we use to execute the query. At the end of the snippet, you’ll see how xlwings allows you to push the result back to the desired cell in Google Sheets.

To call this endpoint, use the runPython function under Google Apps Script like so:

const base_url = "https://your-url.com";
const token = ScriptApp.getOAuthToken();

function selectEmployees() {
  runPython(base_url + "/employees/select", { apiKey: token });
}

This depends on the xlwings Apps Script module, which is already present in the template of this tutorial. If you would start from scratch with a new Google Sheet, you can copy/paste the xlwings module by running xlwings copy gs on a machine where you have Python and xlwings installed.

While deploying the web app to a service like Render is a good idea for production, it would be too cumbersome for development purposes. Therefore, the next section gives you a quick overview of what development looks like.

Development setup

For easy development, you can run

• the app from your local laptop and expose it by using a service such as ngrok so that Google Sheets can access your web server that runs on localhost, or
• use a web-based IDE like GitPod or GitHub Workspaces: they can easily expose the port of your web app so that Google Sheets can access it (see the GitPod button on the README on GitHub)

For more details, see the respective section in the docs.

But why is it a good idea to use Python to connect to your SQLite database rather than using a Google Sheets add-on? The next section gives you a few reasons.

xlwings vs. Google Sheets add-ons for SQLite

By using a custom Python backend, you get a few advantages compared to off-the-shelf (“no-code”) Google Sheets add-ons:

• No need to trust a third-party vendor with your sensitive data: you can run the query on your desired infrastructure (the Render app is just one way to deploy it). You can choose the provider and location—you can even run it on a physical company server or a Raspberry Pi.
• You can keep connection strings and other sensitive data out of your Google Sheets—they can be properly secured as secrets on the server side and can’t be accessed by spreadsheet users.
• You can use a really powerful server if you want to do some CPU or memory-intense stuff besides querying SQLite.
• You get access to the user who runs the query and you can log whenever someone executes a query.
• You can easily make a few tables or the whole SQLite database read-only for certain users while giving other users write access. For more info on authorization, see https://github.com/xlwings/xlwings-googlesheets-auth.
• You can use the whole spreadsheet to design your very own user interface rather than having to follow the design of the no-code solution.
• If you have other data sources that you need to pull in and combine with the SQLite results, you can do this easily, as it’s just Python code.
• Want to refresh the query every day or every hour? Set up a Trigger under Extensions> Apps Script.
• Want to change the data source from SQLite to another SQL database like PostgreSQL or SQL Server? The only thing you need to change is the connection string.

Conclusion

xlwings allows you to automate Google Sheets by using Python instead of Google Apps Script (i.e., JavaScript). Since Python can connect to virtually any data source, SQLite connection is just a few lines of code away. The same is true for other SQL databases like PostgreSQL, SQL Server, Oracle, or MySQL—separate tutorials will follow.