Writing Directly to Google Sheets

Last year we released Neptyne for Google Sheets and we’ve been overwhelmed with all of the positive feedback and amazing spreadsheets you’ve built.

Today, based on your feedback, we’re releasing a new set of features: The ability to read and write any cell from the spreadsheet.

Until now, you could freely call your Python functions from the spreadsheet, but they would only have access to the values you gave them and could only write back to the sheet by returning values. Functional programming as computer scientists would call this. It is nice and predictable, you can always reason why a cell changes value.

But sometimes you want more direct control. This can be as simple as updating multiple cells in response to a call or doing some expensive API call that you don’t want to execute every time you change an input.

As of today, you can! The easiest way to try this, is to open the code editor and use the REPL, the command line interface at the bottom. Just type:

A1 = “Hello world”

And you should see Hello world in A1. One way you can use this effectively is for scraping data from the web. For example, if you have this function:

import pandas as pd

def emperors():
    url = "https://en.wikipedia.org/wiki/List_of_Roman_emperors"
    return pd.read_html(url)[0]

If you just put =Py(“emperors”) in a cell, you would get Augustus and family, but this function would be executed whenever Google feels like it. If instead in the REPL you enter:

B1 = emperors()

You write the actual values to the sheet. In more complicated sheets this approach can really make things a lot more responsive. And of course you can read the cells back from the REPL too. One cool way to do this, is using the .to_dataframe() method:

df = C1:G6.to_dataframe()

This will read the cells back as a pandas dataframe.

This is just a very simple example of course. For some more context of how you can leverage this functionality check out this post.

‍