TUTORIAL

How to Connect Google Sheets to SQL Server

By Felix Zumstein - Comments

Last updated on August 10, 2022

Google Sheets SQL Server

This tutorial shows you how to query a SQL Server database from Google Sheets with Python and xlwings PRO. SQL Server is Microsoft’s flagship database product and is widely used in the corporate world. This tutorial concentrates on querying a SQL Server database directly from Google Sheets. This saves you from the manual and error-prone tasks of having to export data from SQL Server 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 | MariaDB | Oracle

Table of Contents

  1. Quickstart
  2. Show me the code!
  3. Development setup
  4. xlwings vs. Google Sheets add-ons for SQL Server
  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: Google Sheets SQL Server Render Dashboard (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: mssql://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 SQL Server instance locally using Docker if you don’t have one readily available. Or you can use a hosted version of SQL Server such as Azure SQL database. 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: Google Sheets SQL Server Dashboard

    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 SQL Server, 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 mssql service before running docker-compose up in a Command Prompt or Terminal: this will run the web app in one container and the SQL Server 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 SQL Server running via docker-compose, this is: mssql://SA:MyPassw0rd@mssql:1433/master. After uncommenting the mssql 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

  mssql:
    # 120 days evaluation version of Developer edition
    image: mcr.microsoft.com/mssql/server:2022-latest
    ports:
      - "1433:1433"
    environment:
      ACCEPT_EULA: Y
      SA_PASSWORD: MyPassw0rd
      MSSQL_PID: Developer
    volumes:
      - ./mssql-data/data:/var/opt/mssql/data

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 SQL Server 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 SQL Server

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 SQL Server.
  • 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 SQL Server 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 SQL Server to another SQL database like PostgreSQL or MySQL? 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 SQL Server is just a few lines of code away. The same is true for other SQL databases like PostgreSQL, MySQL, Oracle, or SQLite—separate tutorials will follow.

xlwings Newsletter

Everything about xlwings and Python in Excel