A New Way to Automate Google Sheets with Python

It’s been possible to automate Google Sheets via the Google Sheets API for many years and there are multiple Python packages available that wrap this API: there’s gspread, pygsheets, ezsheets, and Google’s own google-api-python-client. All these packages talk from Python to Google Sheets and require you to create a Google Cloud Platform project, download a credentials file, and potentially share your spreadsheet with the user from the credentials file. This can be a relatively painful process, especially if you’re new to Google’s Cloud Console. Nevertheless, the Sheets API approach is the right solution for many use cases: for example, dumping data into Google Sheets for reporting purposes or using Google Sheets as a database in your Python program.

I, however, was missing the Google Apps Script experience in Python, i.e., I wanted to develop interactive spreadsheet apps directly in Google Sheets. So I went ahead and added Google Sheets support to xlwings: instead of using the Sheets API to talk from Python to Google Sheets, xlwings goes the other way around and talks from Google Sheets to Python. The whole thing is really just a web app with a Google Sheets frontend (yes, it works with your favorite web framework!). Apart from sparing you the frontend work, this approach comes with a couple of nice features, such as:

• Ease of use: no need for a Google Cloud Platform project, a credentials file, or sharing with a specific user
• Authorization: leverage the Google identity and restrict certain functionality to specific users or groups
• Scheduler: run your Python scripts automatically (e.g. every hour) via Google Sheets Triggers
• Security: no need to store credentials, proprietary source code, or other sensitive data in Google Sheets
• API: you’re not limited by the Sheets API, but can use everything that the Google Apps Script API offers

Before walking you through these points in a little more detail, let me reiterate that xlwings is not a replacement for the Sheets API approach (in fact, I am planning to wrap the Sheets API with xlwings, too)! Rather, it’s an alternative that allows you to create new types of spreadsheet applications that were previously only possible by writing JavaScript code. You could also look at it as a competitor to low-code tools for building internal apps (Retool, Internal, etc.). As a side benefit, xlwings code will also work with Microsoft Excel across Windows, macOS, and the web.

Note that Google Sheets support is part of xlwings PRO: it’s free for non-commercial use but requires a paid plan for commercial use. And finally: this post stays high-level, so if you want more details, check out the sample GitHub repos that I will link to in this post, have a look at the corresponding xlwings docs, or check my recent webinar. By the way, the screenshot of the GitHub issues dashboard is taken from this repo.

Table of Contents

1. How does the traditional Sheets API work?
2. The xlwings quickstart for Google Sheets
3. Authentication and authorization
4. Security
5. Run your code via buttons, custom menus, and triggers
6. DataFrames and plots
7. Production deployment
8. Roadmap
9. Conclusion

How does the traditional Sheets API work?

Since all of the currently available Python packages such as gspread build on the Sheets API, their pre-requisites all look the same:

• In the Google Cloud Console:
  • Create a Google Cloud Platform project
  • Enable the correct APIs
  • Create the proper credentials and download them as JSON file
• You might need to share your spreadsheet with the user from the created credentials
• Run your Python code from outside Google Sheets, e.g., by running it from the command line on your laptop or server

There are lots of use cases where this approach works perfectly fine: running a cron job on a server to regularly push data into your sheet is a popular one. There are, however a few limitations with the Sheets API:

• You can only automate what the Sheets API exposes: for example, inserting a picture on top of the cells isn’t supported
• While cron jobs work great, you can’t have business users run your code ad-hoc without building some kind of app for them
• Not everybody in your Google Workspace organization has the right to set up a Google Cloud Project
• You need to make sure that the credentials file is properly secured

In the next section, we’ll see how xlwings works and how it ships around these issues.

The xlwings quickstart for Google Sheets

xlwings for Google Sheets is really just a web app that uses Google Sheets as the frontend, thereby saving you from having to deal with HTML, CSS, and JavaScript. It doesn’t require you to set up a Google Cloud Project, and boils down to just two steps:

1. Copy/paste the xlwings JavaScript module into the Apps Script editor
2. Run the Python web server with your xlwings code

The only caveat is that Google Sheets needs to be able to reach your web app:

• For development, you can use a cloud-based IDE such as GitPod or GitHub Codespaces or—if you prefer to work on your local laptop—expose the port of your web server with a tool like ngrok.
• For production, there isn’t anything special to consider as long as your server/cloud is reachable from Google Sheets.

To get up and running in literally 2 minutes, head over to the quickstart project on GitHub and hit the Gitpod button in the README. Then, in your Google Sheet, go to Extensions > Apps Script and paste the code you’ll find under js/xlwings_google.js. That’s it!

Hit the Save button in the Apps Script editor before clicking on the Run button to call the hello function: this will write Hello xlwings! into cell A1 of your spreadsheet, and clicking it again will change it to Bye xlwings!. Note that if you do this the very first time, Google will ask you to authorize the script.

How does this work? The hello function looks like this (part of the JavaScript code that we pasted into the Apps Script editor):

function hello() {
    runPython("https://your-url/hello", { apiKey: "your-api-key" });
}

The runPython call requires the URL of your Python backend for that specific function and an API key. To see the Python code that we’re calling, open app/main.py where you’ll see the corresponding endpoint:

@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]
    if sheet["A1"].value == "Hello xlwings!":
        sheet["A1"].value = "Bye xlwings!"
    else:
        sheet["A1"].value = "Hello xlwings!"

    # Pass the following back as the response
    return book.json()

Note: I am using FastAPI in all of the sample repos, but you could use any other web framework like Django or Flask.

If you’ve used xlwings with Excel before, you’ll feel right at home, as you can use the same syntax. You can see the full code of a minimal quickstart sample either by looking at the repo on GitHub or by running the following command locally (requires xlwings to be installed): xlwings quickstart <projectname> --fastapi. Note that you can also use the xlwings CLI to copy the Google Apps Script module: xlwings copy gs.

If this was a bit too fast, watch this video where I’ll walk you through every step:

Now that we’ve played around with a quickstart project, let’s have a look into a few of the features that xlwings offers in connection with Google Sheets.

Authentication and authorization

Authentication is one of the most critical parts of every app and is usually hard to get right. Google Sheets makes this part embarrassingly simple: replace the API key from the quickstart sample with ScriptApp.getOAuthToken(), which allows the backend to verify the current user. This lets you:

• Remove the sensitive API key from the Apps Script module
• Authenticate everybody from your Workspace domain automatically
• Authorize users for certain actions: for example, you can restrict write access to your database to a few users, while giving everybody read access

To get a better idea, have a look at the following sample repo:

https://github.com/xlwings/xlwings-googlesheets-fastapi-auth

It shows you:

• How to set up authentication
• How to get the user object
• How to perform authorization (using FastAPI’s scope-based approach)

How exactly you’re authorizing your users is up to you: you could use an identity service like Active Directory or Okta or define groups as environment variables. The sample repo shows you how to verify group membership directly against your Google Workspace—this, however, requires you to set up a Google Cloud Project, see the repo’s README for all the details.

Related to authentication/authorization is security in general, which we’ll look at next.

Security

Sharing a Google Sheet is as easy as typing in an email address, but that also means that you can share it accidentally with the wrong person. To minimize the damage, xlwings makes it easy to keep sensitive data out of your spreadsheet:

• Credentials for databases, APIs, etc.
• Other sensitive data like client data
• Business logic (your Python source code)

The Apps Script module will only contain non-sensitive information, as long as you’re using ScriptApp.getOAuthToken() as the apiToken (see the previous section). If your spreadsheet gets into the wrong hands, they will only be able to see the spreadsheet, but they won’t be able to run your Python code. And since you don’t have any credentials in the spreadsheet, you won’t need to rotate them either.

Importantly, credentials can be properly treated as secrets on your backend, using whatever means your provider offers. You can also control who should have access to the Python code (via Git version control, of course).

Now that your app is secure, let’s have a look at different ways of running your code.

Run your code via buttons, custom menus, and triggers

xlwings can take advantage of the same possibilities that you have when running Google Apps Script code:

• Buttons: in Google Sheets, you can click on Insert > Drawing, draw something that looks like a button (e.g., a rounded rectangle), and hit Save and Close. Then, right-click on the button so the three dots appear, click on them and select Assign script, then write the name of the function, in the case of the quickstart sample, this would be hello.
• Custom Menus: with a few lines of JavaScript code, you can run your function from the menu, see the official docs.
• Triggers: in the Apps Script editor, click on the alarm clock icon in the sidebar on the left. Then, at the bottom-right, click on the blue Add Trigger button: this will allow you to run your function periodically or based on a spreadsheet action like the open event.

If you are into scientific computing or machine learning, chances are that your buttons and menus will run some pandas code. Let’s have a closer look at how you work with the scientific Python stack in the next section!

DataFrames and plots

NumPy arrays, pandas DataFrames, and Matplotlib are at the very heart of scientific computing with Python. Accordingly, xlwings supports all of them out of the box. Plotly is also supported, and every other plotting library will also work, as long as the plots can be exported to a picture. The screenshot at the beginning of this section is taken from https://github.com/xlwings/xlwings-googlesheets-pandas-plots and as you will see there, writing a DataFrame df to Google Sheets boils down to:

mysheet["A1"].value = df

Using a Matplotlib plot—as generated by pandas—looks something like this:

ax = df.plot(figsize=(12, 8))
mysheet.pictures.add(image=ax.get_figure(), anchor=sheet["E9"])

For more details, have a look at the repo.

In the next section, we’ll have a look at how you can deploy these sample apps into production.

Production deployment

The number one issue with xlwings has always been the deployment of Python—which is the reason I love this part so much: throw a Docker image or Dockerfile at your favorite service for running Docker containers and you’re done! There are a ton of amazing services in the cloud or under your desk, and you can choose the one that fits your requirements. For more information and a few suggestions with regard to services, see the xlwings docs.

Most of the sample repos mentioned in this post have a Dockerfile included that makes it straightforward to deploy them directly from GitHub. An even easier option is to click the Deploy to Render button (this will deploy it to their free tier), see e.g., https://github.com/xlwings/xlwings-googlesheets-pandas-plots.

Let’s conclude this post with a quick review of the roadmap!

Roadmap

It’s still early stage for Google Sheets support in xlwings, however, with pandas DataFrames and Python plots alone, you’ll be able to build amazing things! The next steps will be:

• Add support for custom functions (a.k.a. user-defined functions)
• Add support for the full xlwings API, including charts, named ranges, etc.
• Making everything more efficient

The exact order will be dictated by you, the user, of course.

Conclusion

The new Google Sheets support in xlwings allows you to build tools in Python that you could previously only build in Google Apps Script, i.e., JavaScript. xlwings allows you to leverage Google’s built-in authentication, you can make the spreadsheet tools secure by keeping the sensitive data out of your spreadsheet, and you have total freedom with regard to the web framework and infrastructure for your backend.