The code window on the right side of all Row Zero workbooks is a Python development environment. The window enables writing custom Python functions that are referenced in the spreadsheet, importing data through Python packages and API connections, and importing popular Python libraries. Code written in the code window CANNOT reference or modify data in the spreadsheet. Think of the code window as a place to create helper functions that perform complex computations or pull data through python libraries, which can all be used and referenced in the spreadsheet in the spreadsheet. The code window can be executed by clicking the ‘RUN’ button or hitting shift+enter.
- Note: Spreadsheet data cannot be referenced in the code window
Custom Python functions can be defined in the code window. Once defined, they can be used in the spreadsheet by calling the function after an ‘=.’
# Define a custom function def foo(x): return "Hello " + str(x) # Type '=foo(x)' in a spreadsheet cell and pass in an argument, like A1.
Import Python Packages
Popular Python packages, like numpy, scipy, pandas, and others can be imported into the workbook with the normal python syntax of ‘import numpy as np.’ If an attempt to import a module returns a ‘modulenotfound’ error, the module is not yet supported. Requests for additional module/package support can be sent to firstname.lastname@example.org. Requests will be added promptly.
# Example import Python packages import pandas as pd import numpy as np
Many python packages are available that make it easy to import data. To do so, best practice to create a function that executes a command to the python package and retrieves data. The newly created function can then be used in the spreadsheet and reference other cells in the spreadsheet as inputs to the functions.
Below is an example using the YFinance python package. YFinance calls the Yahoo Finance API to retrieve stock information. In the example below a function called 'STOCK()' is created, that expects a stock ticker variable and retrieves the appropriate stock data. The function is defined in the code window and then used in the spreadsheet to reference cells that contain various stock tickers. The code window must be executed to retrieve the stock information by pressing 'Ctrl + Enter' or clicking the 'run' button.
import yfinance def STOCK(ticker): return yfinance.Ticker(ticker).history(period="max")