Using Python in Rows

Rows now supports Python scripting within your spreadsheets, giving you the flexibility to process data, automate calculations, and integrate with external APIs—all from within your familiar Rows environment.

Introduction

Python in Rows enables you to write and run Python code directly in your spreadsheet using the new Python integration. This integration allows you to tap into powerful libraries like pandas, numpy, and more, making advanced data analysis and automation easier than ever.

With Python in Rows, you can:

• Transform and clean your data.
• Create custom calculations and functions.
• Integrate with external data sources.
• Build dynamic workflows that update automatically.

Getting Started

Requirements

• Rows Account: You need an active Rows account.
• Data in Your Spreadsheet: Have your data organized in tables or cell ranges.
• Basic Python Knowledge: Familiarity with Python and libraries like pandas can help, but our examples are beginner-friendly.

The Python action

To access the Python integration for writing and editing scripts —

1. Click on the Data button from the format bar
2. Search for Python
3. Under Actions, select Run Python code directly on your spreadsheet data
4. Click Connect

The Python integration action has two parameters:

• script: The script with the Python code.
• range: Cell ranges from your spreadsheet that you want to pass into your script.

Within your Python script, you can access the data from these ranges using the xl() function. For example, xl("'Table1'!A1:D") retrieves the data from cells A1:D on Table1.

Make sure that the ranges you pass on the range parameters are an exact match to what you use in the xl function.

Let's take this Orders table as an example, which has e-commerce orders on top of which you’d like to perform data manipulation using Python.

Note that you'll need to duplicate this spreadsheet onto your own workspace first and here's how you can do it —

• Click on the ⿻+ button in the top right corner
• Click 'Duplicate Spreadsheet'
• Once done, use the ✨ icon in the top right corner of each table and start asking questions

Loading data from a Table to a Python script

To load a range of cells to the script and start using it you'll typically need to do two things:

1. Create a data variable using the xl() function.

1data = xl("'Orders'!A1:I")

This imports the A1:I range of the Orders table into a variable called data. The xl() function always returns a list of values.

2. Convert the list of values to a Panda DataFrame.

A DataFrame is the most popular way to analyze tabular data in Python.

To convert the output from the xl() function into a DataFrame you'll need to:

• Create two variables that split the headers from the rows of the data.
• Use the pandas DataFrame method to create the DataFrame.

Example:

1import pandas as pd
2
3data = xl("'Orders'!A1:I")
4headers = data[0]
5rows = data[1:]
6df = pd.DataFrame(rows, columns=headers)
7df

Note: Python does not support print or return statements. To specify what you want the output of the script to be, simply state the name of the variable/DataFrame at the end of the script.

Transforming the data

After you've loaded the data from a range (or multiple) into the script, you can use the classic Python transformations to do data analysis.

Python for Rows uses Python 3.12 and comes pre-installed with the most popular data analysis and science libraries including: pandas, numpy, scipy, seaborn, scikit-learn and statsmodels.

Outputting the result of the script to the spreadsheet

Rows will automatically transform the result from the script into a Data Table. This means that the result of the script must always be a JSON serializable object like a DataFrame, a list, or a matrix or a string/value.

Examples

Imagine you have a table of data in your spreadsheet with e-commerce orders (like this Orders table) and you’d like to perform data manipulation using Python.

Here are a few examples:

1/ Calculating the average number of Cookies Shipped

1import pandas as pd
2
3data = xl("'Orders'!A1:I")
4headers = data[0]
5rows = data[1:]
6
7df = pd.DataFrame(rows, columns=headers)
8
9df['Cookies Shipped'].mean()

2/ Calculate the number of Cookies Shipped per day

1import pandas as pd
2
3data = xl("'Orders'!A1:I")
4headers = data[0]
5rows = data[1:]
6
7df = pd.DataFrame(rows, columns=headers)
8
9df['Order Date'] = pd.to_datetime(df['Order Date'])
10
11df['Cookies Shipped'] = pd.to_numeric(df['Cookies Shipped'], errors='coerce')
12
13daily_cookies_shipped = df.groupby(df['Order Date'].dt.date)['Cookies Shipped'].sum().reset_index()
14
15daily_cookies_shipped['Order Date'] = daily_cookies_shipped['Order Date'].astype(str)
16
17result = [['Date', 'Cookies Shipped']]
18data_rows = daily_cookies_shipped.values.tolist()
19result.extend(data_rows)
20
21result 

3/ Forecast the number of Cookies Shipped in the next 10 days

1import pandas as pd
2import numpy as np
3from sklearn.linear_model import LinearRegression
4from datetime import datetime, timedelta
5
6data = xl("'Orders'!A1:I")
7headers = data[0]
8rows = data[1:]
9df = pd.DataFrame(rows, columns=headers)
10
11df['Order Date'] = pd.to_datetime(df['Order Date'])
12df['Cookies Shipped'] = pd.to_numeric(df['Cookies Shipped'], errors='coerce')
13
14daily_cookies = df.groupby(df['Order Date'].dt.date)['Cookies Shipped'].sum().reset_index()
15
16daily_cookies['Days'] = range(len(daily_cookies))
17
18X = daily_cookies['Days'].values.reshape(-1, 1)
19y = daily_cookies['Cookies Shipped'].values
20
21model = LinearRegression()
22model.fit(X, y)
23
24last_date = daily_cookies['Order Date'].max()
25future_dates = [last_date + timedelta(days=x+1) for x in range(10)]
26future_days = range(len(daily_cookies), len(daily_cookies) + 10)
27
28future_X = np.array(future_days).reshape(-1, 1)
29predictions = model.predict(future_X)
30
31predictions = [int(round(x)) for x in predictions]
32
33result = [['Date', 'Forecasted Cookies']]
34for date, pred in zip(future_dates, predictions):
35    result.append([date.strftime('%Y-%m-%d'), pred])
36
37result

→ Test these and other examples in the Python Showcase spreadsheet

Frequently Asked Questions (FAQs)

What Python libraries are available in Rows?

Rows comes pre-installed with the most popular data analysis libraries including: pandas, numpy, scipy, matplotlib, seaborn, scikit-learn and statsmodels.

How do I reference cell ranges in my Python script

Use the xl() function with the cell or range reference as a string. For example, xl("Sheet1!A1:B10") retrieves data from the specified range.

How should my Python script return data?

Your script must return a JSON serializable object. A common pattern is to return a list-of-lists where the first list contains column headers, and subsequent lists represent data rows, like a Panda DataFrame.

Alternatively, the script can return single values.

What happens if I rename source tables?

Renaming a Table will not be automatically updated in the script. If the script references a Table by name, you’ll need to manually update the script to use the new Table name.