How to Connect Google Sheets to MariaDB

This tutorial shows you how to query a MariaDB database from Google Sheets with Python and xlwings PRO. MariaDB is a fork of MySQL and was created by one of the founders of MySQL when MySQL got bought by Oracle. This tutorial concentrates on querying a MariaDB database directly from Google Sheets. This saves you from the manual and error-prone tasks of having to export data from MariaDB 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?

SQLite | PostgreSQL | MySQL | SQL Server | Oracle

Table of Contents

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

Quickstart

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: mariadb://user:password@host:port/database. This assumes that you have an existing database that you can use. To use the query from the template, you can use the create_data.py file in the scripts directory of the GitHub repo, which will create a table employees with data from the AdventureWorks sample database. Below under Development Setup we’ll see how we can spin up a MariaDB instance locally using Docker if you don’t have one readily available. Or you can use a hosted version of MariaDB, as they are offered by many cloud vendors. Instead of populating the sample table, you could obviously also change the SQL query to work against one of your existing tables, see Show me the Code below.

   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 including imports):

@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 can 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 });
}

runPython is a function of 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 server-based databases like MariaDB, Docker makes it super easy to spin up a local instance for development purposes. The repo on GitHub contains a docker-compose.yaml file where you only need to uncomment the mariadb service before running docker-compose up in a Command Prompt or Terminal: this will run the web app in one container and the MariaDB database in another container. Make sure to follow the more detailed instructions on the README: for instance, it’ll give you instructions on providing a .env file with the proper connection string. In the case of MariaDB running via docker-compose, this is: mariadb://root:MyPassw0rd@mariadb:3306/xlwings. After uncommenting the mariadb service, your docker-compose.yaml should look like this (make sure everything is properly indented!):

version: '3.7'

services:
  app:
    build:
      context: .
    command: uvicorn app.main:app --host 0.0.0.0
    ports:
      - "8000:8000"
    volumes:
      - ./app:/app
      - ./scripts:/scripts
      - ./sqlite-data:/sqlite-data
    env_file:
      - .env

  mariadb:
    image: mariadb:10
    ports:
      - "3306:3306"
    environment:
      MARIADB_DATABASE: xlwings
      MARIADB_ROOT_PASSWORD: MyPassw0rd
    volumes:
      - ./mariadb-data:/var/lib/mysql

For more details about the development workflow with Google Sheets and xlwings, see the remote interpreter docs.

But why is it a good idea to use Python to connect to your MariaDB database and how does it compare to using an off-the-shelf Google Sheets add-on? The next section gives you a few answers.

xlwings vs. Google Sheets add-ons for MariaDB

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 (Render is just an example of how to deploy it). You can choose the provider and location—you can even run it on a physical company server or a Raspberry Pi.
• Google add-ons are usually priced per user, which can get expensive very quickly. xlwings PRO only requires a paid license for developers and is free to use for end-users.
• 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 MariaDB.
• 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 MariaDB database read-only for certain users while giving other users write access. For more info about 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 SQL 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 MariaDB 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, a connection to MariaDB is just a few lines of code away. The same is true for other SQL databases like PostgreSQL, SQL Server, Oracle, or SQLite—separate tutorials will follow.