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_rows() function. For example, xl(1) retrieves the data from cells in the first range of the action.

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_rows(1)

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_rows() 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_rows(1)
4headers = data[0]
5rows = data[1:]
6df = pd.DataFrame(rows, columns=headers)
7df

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_rows(1)
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
3# Load the data
4data = xl_rows(1)
5headers = data[0]
6rows = data[1:]
7
8# Build the DataFrame
9df = pd.DataFrame(rows, columns=headers)
10
11# Manually parse 'Order Date'
12# 1. Excel origin date
13excel_origin = pd.Timestamp('1899-12-30')
14
15# 2. Turn 'Order Date' into number
16df['Order Date'] = pd.to_numeric(df['Order Date'], errors='coerce')
17
18# 3. Now manually add days to the origin
19df['Order Date'] = excel_origin + pd.to_timedelta(df['Order Date'], unit='d')
20
21# Parse 'Cookies Shipped' too
22df['Cookies Shipped'] = pd.to_numeric(df['Cookies Shipped'], errors='coerce')
23
24# Drop missing dates
25df = df.dropna(subset=['Order Date'])
26
27# Group by 'Order Date' and sum 'Cookies Shipped'
28daily_cookies_shipped = df.groupby(df['Order Date'].dt.date)['Cookies Shipped'].sum().reset_index()
29
30# Format 'Order Date' nicely
31daily_cookies_shipped['Order Date'] = daily_cookies_shipped['Order Date'].astype(str)
32
33# Build final result
34result = [['Date', 'Cookies Shipped']]
35result.extend(daily_cookies_shipped.values.tolist())
36
37result

3/ Forecast the revenue in the next 10 days

1import pandas as pd
2import numpy as np
3from sklearn.linear_model import LinearRegression
4from datetime import timedelta
5
6# Load the data
7data = xl_rows(1)
8headers = data[0]
9rows = data[1:]
10df = pd.DataFrame(rows, columns=headers)
11
12# Parse 'Order Date' properly from Excel serial number
13excel_origin = pd.Timestamp('1899-12-30')
14df['Order Date'] = pd.to_numeric(df['Order Date'], errors='coerce')
15df['Order Date'] = excel_origin + pd.to_timedelta(df['Order Date'], unit='d')
16
17# Parse 'Revenue' properly
18df['Revenue'] = pd.to_numeric(df['Revenue'], errors='coerce')
19
20# Group by Order Date and sum Revenue
21daily_revenue = df.groupby(df['Order Date'].dt.date)['Revenue'].sum().reset_index()
22
23# Add 'Days' as sequential integers for model
24daily_revenue['Days'] = range(len(daily_revenue))
25
26# Prepare data for linear regression
27X = daily_revenue['Days'].values.reshape(-1, 1)
28y = daily_revenue['Revenue'].values
29
30# Train the model
31model = LinearRegression()
32model.fit(X, y)
33
34# Prepare future dates
35last_date = pd.to_datetime(daily_revenue['Order Date'].max())
36future_dates = [last_date + timedelta(days=x+1) for x in range(10)]
37future_days = range(len(daily_revenue), len(daily_revenue) + 10)
38
39# Make predictions
40future_X = np.array(future_days).reshape(-1, 1)
41predictions = model.predict(future_X)
42
43# Round predictions to 2 decimal places (for revenue)
44predictions = [round(x, 2) for x in predictions]
45
46# Build final result
47result = [['Date', 'Forecasted Revenue']]
48for date, pred in zip(future_dates, predictions):
49    result.append([date.strftime('%Y-%m-%d'), pred])
50
51result

→ 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_rows() function with the index of the cell range you want to refer. For example, xl_rows(1) retrieves data from the first range of the Python action. xl_rows(2) will return the range of the second range argument.

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.

Can I make HTTP requests using the Python integration?

No, HTTP requests are not allowed in the Python integration in Rows. For security and performance reasons, network access (including requests, http.client, or similar libraries) is disabled in Python functions and Data Tables.