Getting started with Neptyne's Advanced Features

Note: This tutorial requires enabling Advanced Features. Head here for more information.

Spreadsheets are great but strictly functional - a formula has inputs and outputs and no side effects. Purists might insist that this is how spreadsheets ought to work, but sometimes you just want to make direct changes or peek beyond your immediate inputs.

With Neptyne Core or after enabling Advanced Features for Google Sheets, the first thing you'll unlock is the ability to read and write cell values from within any custom function! And of course also from the REPL, for those cases where you just want to run something, like a function that imports some data.

Let’s take a look at a simple example, you can follow along by running the code in the REPL (at the bottom right of your code panel).

A1 = 1
A2 = A1 + 1

A

B

C

D

1

1

2

2

3

4

5

6

Notice that A1 and A2 are now 1 and 2. Now change A1 on the spreadsheet to 2… A2 doesn’t change! We’ve simply set the values of these cells without leaving behind a formula that updates every time A1 changes.

We can also read and write values from cell ranges.

my_range = A1:A4
my_range[0] = 100 # Notice indexing starts at 0 in Python

A

B

C

D

1

100

2

2

3

4

5

6

And now A1 is 100! That means that you can easily transform your data back and forth between cell ranges and python lists.

This works in two dimensions as well.

A1:B4[0,1] = "hello"

A

B

C

D

1

100

hello

2

2

3

4

5

6

And now B1 holds that value since it’s in the first row and second column.

Data spilling through cell assignment is also a convenient way to load data into your spreadsheet. Here we're going to load a pandas dataframe, a popular format for storing data within python.

import pandas as pd

# initialize list of lists
data = [['tom', 10], ['nick', 15], ['juli', 14]]

# Create the pandas DataFrame
df = pd.DataFrame(data, columns=['Name', 'Age'])

A1 = df

A

B

C

D

1

Name

Age

2

tom

10

3

nick

15

4

juli

14

5

6

Notice that our dataframe has spilled into surrounding cells! You can learn more about spilling data types here.

Where will this be the most useful?
• One time setup for loading data into your spreadsheet.
• Prevent cells from re-evaluation on sheet load.
• Adding interactivity through data validations, or other user driven actions.
• Inserting and deleting data from the sheet programmatically.
• Running functions on a fixed interval.
• And much more.

Continue to Charts with Plotly to learn how to use the powerful Plotly Python library to create automatically updating plots within your sheet.