Custom Formulas and Data Spilling

If you haven't gotten setup with the extension yet, start with the Quickstart Guide

Spilling is the process where a formula returns more than one value and fills up one or more adjacent cells. This is a standard feature of Google Sheets but proves extremely useful when returning tables from Python. In this tutorial we'll play around with emojis, but the same principles work for dataframes and other data structures.

Open the Neptyne sidebar, and paste the following code into the editor in the top right section.

import random
import pandas as pd

emojis = ["😌", "🫠", "🙀", "🍇", "⚽"]


def random_emojis(n=1):
    return [random.choice(emojis) for i in range(n or 1)]


def multi_dim_array(rows, cols):
    return [list(range(cols)) for row in range(rows)]


def sample_df():
    return pd.DataFrame(
        {
            'num_legs': [2, 4, 8, 0],
            'num_wings': [2, 0, 0, 0],
            'num_specimen_seen': [10, 2, 1, 8],
        },
        index=['falcon', 'dog', 'spider', 'fish'],
    )

You can test out the function by running random_emojis() in the REPL at the bottom right of the sidebar. Notice that this returns an array with 1 emoji. If you type random_emojis(4) you’ll get an array with 4 emojis!

Now let’s add it to the spreadsheet. In B1, enter 1. In A1 enter: =PY("random_emojis", B1) This will call the python function random_emojis, and pass B1 as the first parameter, n. You should see an emoji in A1! But why isn't it wrapped in a list?

Change B1 to 4, you’ll notice that you have emojis from A1:A4. That’s because when using the Neptyne extension, list values will spill and fill as many cells as needed in a downwards direction.

Spilling can happen across columns too. Multidimensional arrays can spill in two directions. Run multi_dim_array(3,2) in the REPL and notice that it generates nested arrays. When you move it into the sheet by putting in C1: =Py("multi_dim_array", 3, 2) it spills to 3 rows and 2 columns.

=Py(“random_emojis”, B1)

A

B

C

D

1

🍇

4

0

1

2

🫠

0

1

3

🙀

0

1

4

🍇

5

6

Many more types will spill such as pandas dataframes or dictionaries will spill, so you have many different options for loading your data into the sheet! Try swapping the expression in C1 to =Py("sample_df") and notice that this spills into a table!

Continue to Using Python PIP Packages to learn how to install python pip packages and apply the sci-kit learn package to classify types of flowers.