Developing Python for Google Sheets: Traps and tricks

Our mission at Neptyne is to bring the power of Python to spreadsheets. This blog post describes the various issues we encountered bringing Python to Google Sheets and how we mostly overcame them. It should be useful for anybody who is building a Sheets add-on and entertaining for anybody else. If you haven't tried our add-on, give it go.

A Py by any other name

Let’s pretend it is 2021 and we want to trade some crypto in spreadsheets. Let’s start by looking up prices. We can do this in Python:

def coin_price(coin):
    js = requests.get("https://api.coingecko.com/api/v3/coins/" + coin).json()
    return js['market_data']['current_price']['usd']

Open the code editor and paste in the function. But how do you call it? You want to just put into a cell =coin_price(A1), but this we can’t make work in Google Sheets. Add-ons can define custom formulas but only in a static way - you declare in Apps Script what you want to add and that is it. You can't dynamically create custom formulas.

Solution:

Have one custom formula called PY that takes the name of the python function to call as its first parameter. So in sheets in this example you would enter into the cell:

    =PY(“coin_price”, “bitcoin”)

To get the value of bitcoin. It’s not ideal, but apparently good enough for Microsoft.

Who’s calling?

Google’s add-ons come with a nice way to integrate with Google auth. The ScriptApp.getIdentityToken() function returns an OpenID Connect Token which you can then use with Firebase which we were using as an auth backend anyway. Just to show that this post isn’t just negative about add-ons: Nice work Google!

Then again this is true from the context of the add-on, but not so much when a custom formula is invoked - getIdentityToken always returns null in that context. This makes sense if you think about the various contexts in which spreadsheet functions might run, but it makes things tricky when your custom formula does something with a back-end. And if Google doesn’t tell us who is calling, how can we be sure we trust the caller?

‍Solution:

When running a custom formula it mostly doesn’t matter who initiated the call as long as we can prove it came from our add-on and is coming from the right spreadsheet. Apps script has a facility of script properties that are readable by the script but not by the user. So when we deploy the script, we can generate a secret that is shared by the script and our server and we can use this to sign the calls from the custom formulas.

Dude, where’s my code?

When you make a copy of a sheet, add-ons are not notified. Any calls to custom formulas will be copied of course, but our PY formula will fail since the Python code is not copied since we lose access to your script after a copy.

Solution:

Store the code (and the packages needed) in the developer metadata. Apps script allows you to associate arbitrary data with a spreadsheet, sheet, column or row (but not cell?!?). So every time you make a change to the code from the code editor, we send the code over and store it in the metadata. Then when a sheet is copied and you open the code editor and we can’t find any code, but we do see the metadata, we use that instead!

Run it, run it again, Sam

You can’t rerun a (custom) formula in Google Sheets. Going into edit mode and hitting enter just gives you the same result. This seems reasonable for deterministic functions but the world isn’t always like that. Google’s own RANDOM, NOW and GOOGLEFINANCE suffer from the same. But when you are creating your own functions in Python (or Apps Script!) this is extra annoying. It breaks the normal cycle of code, run, be confused. Repeat until no longer confused. The reverse is also a problem - when Sheets opens, it will rerun all custom formulas. That’s not great if your Python scrapes 41 websites in one function.

Solution:

Keep track of whether the Python function being called has changed. If it changes, but Google does not re-evaluate it (which normally it won’t), send a message to the add-on to forcefully re-run the function anywhere it is mentioned in the spreadsheet (set the formula to empty and then back to what it was). If it hasn’t changed, but Google tries to re-evaluate it anyway, return a cached value. 

Whose Line is it Anyway?

When Apps Script throws an error, it will always show a line number. If the Apps Script is part of an add-on this is confusing enough - the user doesn’t have access to the code so what are they going to do with that line number. But if the add-on brings Python to Sheets it is even worse. The error shown is the error in your python code, but the line corresponds to wherever the Apps Script raised it.

Solution:

Create a file whose first 10 000 lines all of the same pattern:

function err_N_(message) { throw new Error(message); }

where N is the line number this function is defined. We can then have a function that takes the line number and the message and calls the right error function and therefore shows the right line to the user. Here’s our implementation free for you to steal.

I can’t let you do that, Dave

Sheet Add-on’s get access to OAuth tokens and you can use these to get access to the GSheet API. Neat, no? But you don’t get the token when a custom formula is executed. You can store the token on your server of course and use it when the custom formula call comes in, but the token is only valid for one hour. A lot of useful functionality is unlocked if you can use the GSheet API though. 

Solution:

Introduce advanced features for which the user has to authenticate separately. It is unfortunate since we don’t need more access than we’ve already asked for: we want to manage the spreadsheet this add-on has been added to, i.e. drive.file. And we can’t ask for just drive.file, because our new grant won’t give us access to the current document. It feels like it should? The add-on has access, it is the same GCP project. So we ask for spreadsheets, which shows the rather scary notification that this script wants to be able to delete all your spreadsheets. We don’t! In fact we really want to restrict the python code to the current spreadsheet. So we don’t actually give the python access to the OAuth token, but have it communicate through a proxy server that inserts the OAuth token, but only if the call is for the current sheet.

Dig if you will the picture

Python is great for creating visualizations, but how do you get those into Google Sheets? The IMAGE google sheets formula can return an image, but you can’t call built-in functions from a custom formula. Add-ons can insert images but again, not from a custom function. The sheets API has no support for inserting images either. Sheets ignores the DPI of inserted images which make graphs look fuzzy. Images do not have ids in Apps Script, but we need to keep track of images produced by Python in order to update them if the code changes - they are attached to an anchor cell, but that cell changes when the image moves.

Solution:

Deploy an API executable. Use the OAuth token acquired when the user enabled advanced features to authorize and the server can insert images. To create sharp looking graphs, render them twice as large then call setHeight/setWidth, but to avoid the visual glitch of the resize, insert the image offscreen and move in view when done. Abuse the alt text of images as ids, but put a  Utilities.sleep() before setting this since otherwise the image is not rendered in the client.

Conclusion

In conclusion, the workspace add-on ecosystem is a powerful way to bring new functionality to the primary leading office suite, but it comes with its restrictions. This post describes some of those restrictions and workarounds. These workarounds are especially relevant if you are trying to build something like Python for Google Sheets but is hopefully useful for your next workspace project too.