To reference an individual cell, use the global function q.cells which returns the cell value.
# NOTE: uses the same A1 notation as Formulas# Reads the value in cell A1 and places in variable x x = q.cells('A1')
You can reference cells and use them directly in a Pythonic fashion.
q.cells('A1')+ q.cells('A2')# Adds the values in cells A1 and A2
Any time cells dependent on other cells update, the dependent cell will also update. This means your code will execute in one cell if it is dependent on another. This is the behavior you want in almost all situations, including user inputs in the sheet that cause calculation in a Python cell.
Referencing a range of cells
To reference a range of cells, use the same global function q.cells() which returns a Pandas DataFrame.
q.cells('A1:A5')# Returns a 1x5 DataFrame spanning from A1 to A5q.cells('A1:C7')# Returns a 3x7 DataFrame spanning from A1 to C7q.cells('A')# Returns all values in column A into a single-column DataFrameq.cells('A:C')# Returns all values in columns A to C into a three-column DataFrameq.cells('A5:A')# Returns all values in column A starting at A5 and going downq.cells('A5:C')# Returns all values in column A to C, starting at A5 and going downq.cells('A1:1')# Returns all values in row 1, starting at column Aq.cells('C5:5')# Returns all values in row 5, starting at column C
If the first row of cells is a header, you should set first_row_header as an argument. This makes the first row of your DataFrame the column names, otherwise will default to integer column names as 0, 1, 2, 3, etc.
Use first_row_header when you have column names that you want as the header of the DataFrame. This should be used very commonly. You can tell when a column name should be a header when the column name describes the data below.
# first_row_header=True will be used any time the first row is the intended header for that data.q.cells('A1:B9', first_row_header=True)# returns a 2x9 DataFrame with first rows as DataFrame headers
As an example, this code references a table of expenses, filters it based on a user-specified column, and returns the resulting DataFrame to the spreadsheet.
# Pull the full expenses table in as a DataFrameexpenses_table = q.cells('B2:F54', first_row_header=True)# Take user input at a cell (Category = "Gas")category = q.cells('A2')# Filter the full expenses table to the "Gas" category, return the resulting DataFrameexpenses_table[expenses_table["Category"]== category]
Referencing another sheet
To reference another sheet's cells or range of cells use the following:
# Use the sheet name as an argument for referencing range of cells q.cells("'Sheet_name_here'!A1:C9")# For individual cell reference q.cells("'Sheet_name_here'!A1")
Unbounded references
Unbounded column references
To reference all the data in a column or set of columns without defining the range, use the following syntax.
Unbounded column references span from the row set (row 1 if not defined) to wherever the content in that column ends. Ranged references are always returned as DataFrames. Wherever gaps in data exist, None is filled in instead.
# references all values in the column from row 1 to the end of the content q.cells('A')# returns all the data in the column starting from row 1 to end of data q.cells('A:D')# returns all the data in columns A to D starting from row 1 to end of data in longest columnq.cells('A5:A')# returns all values from A5 to the end of the content in column A q.cells('A5:C')# returns all values from A5 to end of content in Cq.cells('A:C', first_row_header=True)# same rules with first_row_header apply q.cells("'Sheet2'!A:C", first_row_header=True)# same rules to reference in other sheets apply
Unbounded row references
To reference all the data in a row or set of rows without defining the range, use the following syntax.
Row references span from the row set to wherever the content in that row ends.
# Returns all values in Row 1q.cells('1')# Returns all values in rows 1 to 3 q.cells('1:3')# Returns all values in Row 1 starting at column Aq.cells('A1:1')# Returns all values in Row 1 starting at column Cq.cells('C1:1')# Returns all values in Row 3 starting at column Aq.cells('A3:3')# Returns all values in Row 3 starting at column C q.cells('C3:3')
Relative vs absolute references
By default when you copy paste a reference it will update the row reference unless you use $ notation in your references.
# Copy pasting this one row down will change reference to A2q.cells('A1')# Copy pasting this one row down will keep reference as A1q.cells('A$1')# Example using ranges - row references will not changeq.cells('A$1:B$20')# Only A reference will change when copied downq.cells('A1:B$20')