xlwings Server PRO#

This feature requires at least v0.27.0.

Instead of installing Python on each end-user’s machine, you can work with a server-based Python installation. It’s essentially a web application, but uses your spreadsheet as the frontend instead of a web page in a browser. xlwings Server doesn’t just work with the Desktop versions of Excel on Windows and macOS but additionally supports Google Sheets and Excel on the web for a full cloud experience. xlwings Server runs everywhere where Python runs, including Linux, Docker and WSL (Windows Subsystem for Linux). it can run on your local machine, as a (serverless) cloud service, or on an on-premise server.

Important

This feature currently only covers parts of the RunPython API (UDFs are not yet supported). See also Limitations and Roadmap.

Why is this useful?#

Having to install a local installation of Python with the correct dependencies is the number one friction when using xlwings. Most excitingly though, xlwings Server adds support for the web-based spreadsheets: Google Sheets and Excel on the web.

To automate Office on the web, you have to use Office Scripts (i.e., TypeScript, a typed superset of JavaScript) and for Google Sheets, you have to use Apps Script (i.e., JavaScript). If you don’t feel like learning JavaScript, xlwings allows you to write Python code instead. But even if you are comfortable with JavaScript, you are very limited in what you can do, as both Office Scripts and Apps Script are primarily designed to automate simple spreadsheet tasks such as inserting a new sheet or formatting cells rather than performing data-intensive tasks. They also make it very hard/impossible to use external JavaScript libraries and run in environments with minimal resources.

Note

From here on, when I refer to the xlwings JavaScript module, I mean either the xlwings Apps Script module if you use Google Sheets or the xlwings Office Scripts module if you use Excel on the web.

On the other hand, xlwings Server brings you these advantages:

  • Work with the whole Python ecosystem: including pandas, machine learning libraries, database packages, web scraping, boto (for AWS S3), etc. This makes xlwings a great alternative for Power Query, which isn’t currently available for Excel on the web or Google Sheets.

  • Leverage your existing development workflow: use your favorite IDE/editor (local or cloud-based) with full Git support, allowing you to easily track changes, collaborate and perform code reviews. You can also write unit tests using pytest.

  • Remain in control of your data and code: except for the data you expose in Excel or Google Sheets, everything stays on your server. This can include database passwords and other sensitive info such as customer data. There’s also no need to give the Python code to end-users: the whole business logic with your secret sauce is protected on your own infrastructure.

  • Choose the right machine for the job: whether that means using a GPU, a ton of CPU cores, lots of memory, or a gigantic hard disc. As long as Python runs on it, you can go from serverless functions as offered by the big cloud vendors all the way to a self-managed Kubernetes cluster under your desk (see Production Deployment).

  • Headache-free deployment and maintenance: there’s only one location (usually a Linux server) where your Python code lives and you can automate the whole deployment process with continuous integration pipelines like GitHub actions etc.

  • Cross-platform: xlwings Server works with Google Sheets, Excel on the web and the Desktop apps of Excel on Windows and macOS.

Prerequisites#

  • At least xlwings 0.27.0

  • Either the xlwings add-in installed or a workbook that has been set up in standalone mode

  • At least xlwings 0.27.0

  • New sheets: no special requirements.

  • Older sheets: make sure that Chrome V8 runtime is enabled under Extensions > Apps Script > Project Settings > Enable Chrome V8 runtime.

  • At least xlwings 0.27.0

  • You need access to Excel on the web with the Automate tab enabled, i.e., access to Office Scripts. Note that Office Scripts currently requires OneDrive for Business or SharePoint (it’s not available on the free office.com), see also Office Scripts Requirements.

  • The fetch command in Office Scripts must not be disabled by your Microsoft 365 administrator.

Introduction#

xlwings Server consists of two parts:

  • Backend: the Python part

  • Frontend: the xlwings JavaScript module (for Google Sheets/Excel on the web) or the VBA code in the form of the add-in or standalone modules (Desktop Excel)

The backend exposes your Python functions by using a Python web framework. In more detail, you need to handle a POST request along these lines (the sample shows an excerpt that uses FastAPI as the web framework, but it works accordingly with any other web framework like Django or Flask):

@app.post("/hello")
def hello(data: dict = Body):
    # Instantiate a Book object with the deserialized request body
    book = xw.Book(json=data)

    # Use xlwings as usual
    sheet = book.sheets[0]
    sheet["A1"].value = 'Hello xlwings!'

    # Pass the following back as the response
    return book.json()
  • For Desktop Excel, you can run the web server locally and call the respective function from VBA right away (given that you have the add-in installed).

  • For the cloud-based spreadsheets, you have to run this on a web server that can be reached from Google Sheets or Excel on the web, and you have to paste the xlwings JavaScript module into the respective editor. How this all works, will be shown in detail under Cloud-based development with Gitpod.

The next section shows you how you can play around with the xlwings Server on your local desktop before we’ll dive into developing against the cloud-based spreadsheets.

Local Development with Desktop Excel#

The easiest way to try things out is to run the web server locally against your Desktop version of Excel. We’re going to use FastAPI as our web framework. While you can use any web framework you like, no quickstart command exists for these yet, so you’d have to set up the boilerplate yourself.

Start by running the following command on a Terminal/Command Prompt. Feel free to replace demo with another project name and make sure to run this command in the desired directory:

$ xlwings quickstart demo --fastapi

This creates a folder called demo in the current directory with the following files:

app.py
demo.xlsm
main.py
requirements.txt

I would recommend you to create a virtual or Conda environment where you install the dependencies via pip install -r requirements.txt. In app.py, you’ll find the FastAPI boilerplate code and in main.py, you’ll find the hello function that is exposed under the /hello endpoint.

To run this server locally, run python main.py in your Terminal/Command Prompt or use your code editor/IDE’s run button. You should see something along these lines:

$ python main.py
INFO:     Will watch for changes in these directories: ['/Users/fz/Dev/demo']
INFO:     Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)
INFO:     Started reloader process [36073] using watchgod
INFO:     Started server process [36075]
INFO:     Waiting for application startup.
INFO:     Application startup complete.

Your web server is now listening, so let’s open demo.xlsm, press Alt+F11 to open the VBA editor, and in Module1, place your cursor somewhere inside the following function:

Sub SampleRemoteCall()
    RunRemotePython "http://127.0.0.1:8000/hello", apiKey:="DEVELOPMENT"
End Sub

Then hit F5 to run the function—you should see Hello xlwings! in cell A1 of the first sheet. To move this to production, you need to deploy the backend to a server, set a unique API key and adjust the url/apiKey in the RunRemotePython function accordingly, see Production Deployment.

The next sections, however, show you how you can make this work with the Google Sheets and Excel on the web.

Cloud-based development with Gitpod#

Using Gitpod is the easiest solution if you’d like to develop against either Google Sheets or Excel on the web.

If you want to have a development environment up and running in less than 5 minutes (even if you’re new to web development), simply click the Open in Gitpod button to open a sample project in Gitpod (Gitpod is a cloud-based development environment with a generous free tier):

Open in Gitpod

Opening the project in Gitpod will require you to sign in with your GitHub account. A few moments later, you should see an online version of VS Code. In the Terminal, it will ask you to paste the xlwings license key (get a free trial key if you want to try this out in a commercial context or use the noncommercial license key if your usage qualifies as noncommercial). Note that your browser will ask you for permission to paste. Once you confirm your license key by hitting Enter, the server will automatically start with everything properly configured. You can then open the app directory and look at the main.py file, where you’ll see the hello function. This is the function we’re going to call from Google Sheets/Excel on the web in just a moment. The other file in this directory, app.py contains all the FastAPI boilerplate code. Let’s leave this alone for a moment and look at the js folder instead. Open the file according to your platform:

xlwings_google.js
xlwings_excel.ts

Copy all the code, then switch to Google Sheets or Excel on the web, respectively, and continue as follows:

Click on Extensions > Apps Script. This will open a separate browser tab and open a file called Code.gs with a function stub. Replace this function stub with the copied code from xlwings_google.js and click on the Save icon. Then hit the Run button (the hello function should be automatically selected in the dropdown to the right of it). If you run this the very first time, Google Sheets will ask you for the permissions it needs. Once approved, the script will run the hello function and write Hello xlwings! into cell A1.

To add a button to a sheet to run this function, switch from the Apps Script editor back to Google Sheets, click on Insert > Drawing and draw a rounded rectangle. After hitting Save and Close, the rectangle will appear on the sheet. Select it so that you can click on the 3 dots on the top right of the shape. Select Assign Script and write hello in the text box, then hit OK.

In the Automate tab, click on New Script. This opens a code editor pane on the right-hand side with a function stub. Replace this function stub with the copied code from xlwings_excel.ts. Make sure to click on Save script before clicking on Run: the script will run the hello function and write Hello xlwings! into cell A1.

To run this script from a button, click on the 3 dots in the Office Scripts pane (above the script), then select + Add button.

Any changes you make to the hello function in app/main.py in Gitpod are automatically saved and reloaded by the web server and will be reflected the next time you run the script from Google Sheets or Excel on the web.

To test out yahoo, the other function of the sample project, replace hello with yahoo in the runPython function in the xlwings JavaScript module.

Note

While Excel on the web requires you to create a separate script with a function called main for each Python function, Google Sheets allows you to add multiple functions with any name.

Please note that clicking the Gitpod button gets you up and running quickly, but if you want to save your changes (i.e., commit them to Git), you should first fork the project on GitHub to your own account and open it by prepending https://gitpod.io/# to your GitHub URL instead of clicking the button (this works with GitLab and Bitbucket too). Or continue with the next section, which shows you how you can start a project from scratch on your local machine.

An alternative for Gitpod is GitHub Codespaces, but unlike Gitpod, GitHub Codespaces only works with GitHub, has no free tier, and may not be available yet on your account.

Local Development with Google Sheets or Excel on the web#

This section walks you through a local development workflow as an alternative to using Gitpod/GitHub Codespaces. What’s making this a little harder than using a preconfigured online IDE like Gitpod is the fact that we need to expose our local web server to the internet for easy development.

As before, we’re going to use FastAPI as our web framework. While you can use any web framework you like, no quickstart command exists for these yet, so you’d have to set up the boilerplate yourself. Let’s start with the server before turning our attention to the client side (i.e, Google Sheets or Excel on the web).

Part I: Backend#

Start a new quickstart project by running the following command on a Terminal/Command Prompt. Feel free to replace demo with another project name and make sure to run this command in the desired directory:

$ xlwings quickstart demo --fastapi

This creates a folder called demo in the current directory with a few files. Since we’re using an online spreadsheet instead of the Desktop Excel, you can delete demo.xlsm, which should leave you with the following files:

main.py
app.py
requirements.txt

I would recommend you to create a virtual or Conda environment where you install the dependencies via pip install -r requirements.txt. In app.py, you’ll find the FastAPI boilerplate code and in main.py, you’ll find the hello function that is exposed under the /hello endpoint.

The application expects you to set the environment variable XLWINGS_API_KEY to a unique key in order to protect your application from unauthorized access. You should choose a strong random key, for example by running the following on a Terminal/Command Prompt: python -c "import secrets; print(secrets.token_hex(32))". If you don’t set an environment variable, it will use DEVELOPMENT as the API key (only use this for quick tests and never for production!).

To run this server locally, run python main.py in your Terminal/Command Prompt or use your code editor/IDE’s run button. You should see something along these lines:

$ python main.py
INFO:     Will watch for changes in these directories: ['/Users/fz/Dev/demo']
INFO:     Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)
INFO:     Started reloader process [36073] using watchgod
INFO:     Started server process [36075]
INFO:     Waiting for application startup.
INFO:     Application startup complete.

Your web server is now listening, however, to enable it to communicate with Google Sheets or Excel on the web, you need to expose the port used by your local server (port 8000 in your example) securely to the internet. There are many free and paid services available to help you do this. One of the more popular ones is ngrok whose free version will do the trick (for a list of ngrok alternatives, see Awesome Tunneling):

For the sake of this tutorial, let’s assume you’ve installed ngrok, in which case you would run the following on your Terminal/Command Prompt to expose your local server to the public internet:

$ ngrok http 8000

Note that the number of the port (8000) has to correspond to the port that is configured on your local development server as specified at the bottom of main.py. ngrok will print something along these lines:

ngrok by @inconshreveable                                                                                (Ctrl+C to quit)

Session Status                online
Account                       name@domain.com (Plan: Free)
Version                       2.3.40
Region                        United States (us)
Web Interface                 http://127.0.0.1:4040
Forwarding                    http://xxxx-xxxx-xx-xx-xxx-xxxx-xxxx-xxxx-xxx.ngrok.io -> http://localhost:8000
Forwarding                    https://xxxx-xxxx-xx-xx-xxx-xxxx-xxxx-xxxx-xxx.ngrok.io -> http://localhost:8000

To configure the xlwings client in the next step, we’ll need the https version of the Forwarding address that ngrok prints, i.e., https://xxxx-xxxx-xx-xx-xxx-xxxx-xxxx-xxxx-xxx.ngrok.io.

Note

When you’re not actively developing, you should stop your ngrok session by hitting Ctrl-C in the Terminal/Command Prompt.

Part II: Frontend#

Now it’s time to switch to Google Sheets or Excel on the web! To paste the xlwings JavaScript module, follow these 3 steps:

  1. Copy the xlwings JavaScript module: On a Terminal/Command Prompt on your local machine, run the following command:

    $ xlwings copy gs
    
    $ xlwings copy os
    

    This will copy the correct xlwings JavaScript module to the clipboard so we can paste it in the next step.

  2. Paste the xlwings JavaScript module

Click on Extensions > Apps Script. This will open a separate browser tab and open a file called Code.gs with a function stub. Replace this function stub with the copied code from the previous step and click on the Save icon. Then hit the Run button (the hello function should be automatically selected in the dropdown to the right of it). If you run this the very first time, Google Sheets will ask you for the permissions it needs. Once approved, the script will run the hello function and write Hello xlwings! into cell A1.

To add a button to a sheet to run this function, switch from the Apps Script editor back to Google Sheets, click on Insert > Drawing and draw a rounded rectangle. After hitting Save and Close, the rectangle will appear on the sheet. Select it so that you can click on the 3 dots on the top right of the shape. Select Assign Script and write hello in the text box, then hit OK.

In the Automate tab, click on New Script. This opens a code editor pane on the right-hand side with a function stub. Replace this function stub with the copied code from the previous step. Make sure to click on Save script before clicking on Run: the script will run the hello function and write Hello xlwings! into cell A1.

To run this script from a button, click on the 3 dots in the Office Scripts pane (above the script), then select + Add button.

  1. Configuration: The final step is to configure the xlwings JavaScript module properly, see the next section Configuration.

Configuration#

xlwings can be configured in two ways:

  • Via arguments in the runPython (Google Sheets or Excel on the web) or RunRemotePython (Desktop Excel) function, respectively.

  • Via xlwings.conf sheet (in this case, the keys are UPPER_CASE with underscore instead of camelCase, see the screenshot below).

If you provide a value via config sheet and via function argument, the function argument wins. Let’s see what the available settings are:

  • url (required): This is the full URL of your function. In the above example under Local Development with Google Sheets or Excel on the web, this would be https://xxxx-xxxx-xx-xx-xxx-xxxx-xxxx-xxxx-xxx.ngrok.io/hello, i.e., the ngrok URL with the /hello endpoint appended.

  • apiKey (optional): While this is technically optional, it is usually required by the backend. It has to correspond to whatever you set the XLWINGS_API_KEY environment variable on your server and will protect your functions from unauthorized access. It’s good practice to keep your sensitive keys such as the apiKey out of your source code (the JavaScript/VBA module), but putting it in the xlwings.conf sheet may only be marginally better. Excel on the web, however, doesn’t currently provide you with a better way of handling this. Google Sheets, on the other hand, allows you to work with Properties Service to keep the API key out of both the JavaScript code and the xlwings.conf sheet.

    Note

    The API key is chosen by you to protect your application and has nothing to do with the xlwings license key!

  • headers (optional): A dictionary (VBA) or object literal (JS) with name/value pairs. If you set the Authorization header, apiKey will be ignored.

  • exclude (optional): By default, xlwings sends over the complete content of the whole workbook to the server. If you have sheets with big amounts of data, this can make the calls slow or you could even hit a timeout. If your backend doesn’t need the content of certain sheets, you can exclude them from being sent over via this setting. Currently, you can only exclude entire sheets as comma-delimited string like so: "Sheet1, Sheet2".

  • include (optional): It’s the counterpart to exclude and allows you to submit the names of the sheets that you want to send to the server. Like exclude, include accepts a comma-delimited string, e.g., "Sheet1,Sheet2".

Configuration Examples: Function Arguments#

Using only required arguments:

Sub Hello()
    RunRemotePython "http://127.0.0.1:8000/hello", apiKey:="YOUR_UNIQUE_API_KEY"
End Sub

Additionally providing the exclude parameter to exclude the content of the xlwings.conf and Sheet1 sheets:

Sub Hello()
    RunRemotePython "http://127.0.0.1:8000/hello", apiKey:="YOUR_UNIQUE_API_KEY", exclude:="xlwings.conf, Sheet1"
End Sub

Using only required arguments:

function hello() {
  runPython("https://xxxx-xxxx-xx-xx-xxx-xxxx-xxxx-xxxx-xxx.ngrok.io/hello", {
    apiKey: "YOUR_UNIQUE_API_KEY",
  });
}

Additionally providing the exclude parameter to exclude the content of the xlwings.conf and Sheet1 sheets as well as a custom header:

function hello() {
  runPython("https://xxxx-xxxx-xx-xx-xxx-xxxx-xxxx-xxxx-xxx.ngrok.io/hello", {
    apiKey: "YOUR_UNIQUE_API_KEY",
    exclude: "xlwings.conf, Sheet1",
    headers: { MyHeader: "my value" },
  });
}

Using only required arguments:

async function main(workbook: ExcelScript.Workbook) {
  await runPython(
    workbook,
    "https://xxxx-xxxx-xx-xx-xxx-xxxx-xxxx-xxxx-xxx.ngrok.io/hello",
    { apiKey: "YOUR_UNIQUE_API_KEY" }
  );
}

Additionally providing the exclude parameter to exclude the content of the xlwings.conf and Sheet1 sheets as well as a custom header:

async function main(workbook: ExcelScript.Workbook) {
  await runPython(
    workbook,
    "https://xxxx-xxxx-xx-xx-xxx-xxxx-xxxx-xxxx-xxx.ngrok.io/hello",
    {
      apiKey: "YOUR_UNIQUE_API_KEY",
      exclude: "xlwings.conf, Sheet1",
      headers: { MyHeader: "my value" },
    }
  );
}

Configuration Examples: xlwings.conf sheet#

Create a sheet called xlwings.conf and fill in key/value pairs like so:

_images/xlwings_conf_sheet.png

Production Deployment#

The xlwings web server can be built with any web framework and can therefore be deployed using any solution capable of running a Python backend or function. Here is a list for inspiration (non-exhaustive):

Important

For production deployment, always make sure to set a unique and random API key, see Configuration.

If you’d like to deploy the sample project to production in less than 5 minutes, you can do so by clicking the button below, which will deploy it to Heroku’s free tier. Note, however, that on the free plan, the backend will “sleep” after 30 minutes of inactivity, which means that it will take a few moments the next time you call it until it is up and running again. The XLWINGS_API_KEY is auto-generated and you can look it up under your app’s Settings > Config Vars > Reveal Config Vars once the app is deployed. To get the URL, you’ll need to append /hello to the app’s URL that you’ll find in your dashboard.

Deploy

Triggers#

For Google Sheets, you can take advantage of the integrated Triggers (accessible from the menu on the left-hand side of the Apps Script editor). You can trigger your xlwings functions on a schedule or by an event, such as opening or editing a sheet.

Normally, you would use Power Automate to achieve similar things as with Google Sheets Triggers, but unfortunately, Power Automate can’t run Office Scripts that contain a fetch command like xlwings does, so for the time being, you can only trigger xlwings calls manually on Excel on the web. Alternatively, you can open your Excel file with Google Sheets and leverage the Triggers that Google Sheets offers. This, however, requires you to store your Excel file on Google Drive.

Limitations#

  • Currently, only a subset of the xlwings API is covered, mainly the Range and Sheet classes with a focus on reading and writing values and sending pictures (including Matplotlib plots). This, however, includes full support for type conversion including pandas DataFrames, NumPy arrays, datetime objects, etc.

  • You are moving within the web’s request/response cycle, meaning that values that you write to a range will only be written back to Google Sheets/Excel once the function call returns. Put differently, you’ll get the state of the sheets at the moment the call was initiated, but you can’t read from a cell you’ve just written to until the next call.

  • You will need to use the same xlwings version for the Python package and the JavaScript module, otherwise, the server will raise an error.

  • Currently, custom functions (a.k.a. user-defined functions or UDFs) are not supported.

  • For users with no experience in web development, this documentation may not be quite good enough just yet.

Platform-specific limitations:

  • xlwings relies on the fetch command in Office Scripts that cannot be used via Power Automate and that can be disabled by your Microsoft 365 administrator.

  • While Excel on the web feels generally slow, it seems to have an extreme lag depending on where in the world you open the browser with Excel on the web. For example, a hello world call takes ~4.5s if you open a browser in Amsterdam/Netherlands while it takes ~8.5s if you do it Buenos Aires/Argentina.

  • Platform limits with Office Scripts apply.

Roadmap#

  • Complete the RunPython API by adding features that currently aren’t supported yet, e.g., charts, shapes, names collections, tables, etc.

  • Add support for UDFs/custom functions.

  • Improve efficiency.