Show
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:
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
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:
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:
In the next section, we’ll see how xlwings works and how it ships around these issues. The xlwings quickstart for Google Sheetsxlwings 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:
The only caveat is that Google Sheets needs to be able to reach your web app:
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 Hit the How does this work? The
The
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): 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 authorizationAuthentication 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
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 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. SecuritySharing 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:
The Apps Script module will only contain non-sensitive information, as long as you’re using 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 triggersxlwings can take advantage of the same possibilities that you have when running Google Apps Script code:
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 plotsNumPy 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 Using a Matplotlib plot—as generated by pandas—looks something like this:
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 deploymentThe 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 Let’s conclude this post with a quick review of the roadmap! RoadmapIt’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:
The exact order will be dictated by you, the user, of course. ConclusionThe 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. How do you access data from Google Sheets in Python?A Google account.. Step 1: Install the Google client library. To install the Google client library for Python, run the following command: pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib. ... . Step 2: Configure the sample. To configure the sample: ... . Step 3: Run the sample. To run the sample:. How do you automate data entry in Google Sheets using Python?The data inside the worksheets can be accessed through 'Cell' objects which supports formatting, formulas, etc.. Step 1: Enable APIs for Google Sheets and Google Drive. A. ... . Step 2: Create a Service Account and fetch credentials. ... . Step 3: Add Service Account as an editor. ... . Step 4: Authorize pygsheets.. Can you run code in Google Sheets?All you need to do is invest some time in getting the code ready once, and whenever you have to do the same steps again, you simply run the script code in Google Sheets and let GAS do all the heavy lifting for you.
|