Imports and Definitions
Environment
Settings
Account
Help
Active Code Cell Reference
Linking
Cell Output
Language
Delete
Copy
REF
Run
Using the REF function
REF
function in your code that returns a list of lists. The Imports and Definitions tab includes the following pre-defined functions to help convert the returned list of lists to different data structures.Function | Use case | Notes |
---|---|---|
to_df(REF(<CELL_RANGE>)) | Create a DataFrame | to_df assumes your data has headers |
to_array(REF(<CELL_RANGE>)) | Create a NumPy array | to_array assumes all data is of the same type |
to_list(REF(<CELL_RANGE>)) | Create a 1D list | to_list handles wide (1 x n) or tall (n x 1) data |
to_df()
, to_array()
, and to_list()
from the Imports and Definitions tab.</>NoneType
..whl
).Imports
# Add imports
comment.
import
from the packages included in the standard Python or Web R installation and those listed in the Environment tab.Definitions
# Define
comment.
UDFs in Python
@UDF
, as shown in the following example:
=ANACONDA
. If you added the example above to your definitions list, the option to call ANACONDA.MY_CUSTOM_FUNCTION
appears in the dropdown.
ANACONDA.MY_CUSTOM_FUNCTION
, press Tab, and then complete the function.
name
argument to provide a unique name. Set nested
to False to remove ANACONDA.
from the name.UDF.Range
argument tells Excel that the input of the function is a 2D range. Without specifying this, Excel will show a #CALC! Unliftable Array
error if a 2D range is passed into the UDF. Parameters specified as UDF.Range
will always be passed as a 2D array to the function, even if a single cell is passed in.Example usage of UDF.Range
:UDF.Range[str]
.UDFs in R
UDF.register()
and pass the function as an argument, as shown in the following example:
=ANACONDA
. If you added the example above to your definitions list, the option to call ANACONDA.MY_CUSTOM_R_FUNCTION
appears in the dropdown.
ANACONDA.MY_CUSTOM_R_FUNCTION
, press Tab, and then complete the function call.
name
argument to provide a unique name and set nested
to False to remove ANACONDA.
from the name.range_args
parameter tells Excel that the input of the function is a 2D range. Without specifying this, Excel will show a #CALC! Unliftable Array
error if a 2D range is passed into the UDF. Parameters specified as range_args
will always be passed as a 2D array to the function, even if a single cell is passed in.Example usage of range_args
:doc
parameter:Run Isolated
output_of_B2
variable is defined in cell B2 and assigned the string "I'm the B2 cell!"
. When this code is run in the B2 code cell, the B2 cell displays "I'm the B2 cell!"
. However, since both cells are running in isolated mode, when output_of_B2
is referenced in the B4 code cell, the B4 cell displays a #VALUE!
error because B4 cannot access the variable in B2.Using the REF
functionYou can bypass isolation rules as needed using the REF
function to create a reference from one isolated code cell to another.REF("B2")
. When the B4 code cell is run, it returns the value of B2, "I'm the B2 cell!"
. Changes to the B4 cell don’t cause the B2 cell to recalculate, but changes to the B2 cell will cause the B4 cell to recalculate. Code cells can include multiple REF
function references, and changes to any referenced cells (in this example, B2) will cause the referencing cells to recalculate (in this example, B4).Working with code objectsIf you reference a cell that’s set to output a code object, the REF
function will return an instance of that object in the referencing cell.</> list
in B2. In the B4 code cell, we define a variable called output_of_B2
and assign a REF
function that references cell B2. The output mode for the B4 code cell is set to “Excel Values”, so the list spills across multiple cells in the spreadsheet.Benefits of isolated modeThe benefit of using the isolated mode is that referenced cells are not recalculated when changes are made to referencing cells. For complex processes, this allows you to:Run Linked
output_of_B2
variable is defined in cell B2 and assigned the string "I'm the B2 cell!"
. When this code is run in the B2 code cell, the B2 cell displays "I'm the B2 cell!"
. The output_of_B2
variable is then referenced in the B4 code cell, causing the B4 cell to also display "I'm the B2 cell!"
.Benefits of linked modeLinked mode is useful when:Output | Description |
---|---|
Excel Values | When outputting a DataFrame, array, list, and so on, the values will “spill” to fill the required space. If the spill were to overwrite cells containing data, the cell displays a #SPILL error. |
Local Code Object | For certain object types, you can view the contents in a “Card View” by clicking the cell. You can reference this cell and the returned object like you would any other object. |
Cause
Solution