Reference cells

Reference cells from JavaScript.

In Quadratic, reference individual cells from JavaScript for single values or reference a range of cells for multiple values.

Referencing individual cells

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 
let x = q.cells('A1')

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 JavaScript cell.

Referencing a range of cells

To reference a range of cells, use the same global function q.cells() which returns an array of arrays.

let x = q.cells('A1:A5') // Returns a 1x5 array spanning from A1 to A5

let x = q.cells('A1:C7') // Returns a 3x7 array of arrays spanning from A1 to C7

let x = q.cells('A') // Returns all values in column A into a single-column DataFrame

let x = q.cells('A:C') // Returns all values in columns A to C into a three-column DataFrame

let x = q.cells('A5:A') // Returns all values in column A starting at A5 and going down

let x = q.cells('A5:C') // Returns all values in column A to C, starting at A5 and going down

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 
let x = q.cells("'Sheet_name_here'!A1:C9")

// For individual cell reference 
let x = q.cells("'Sheet_name_here'!A1")

Unbounded ranges

Unbounded column references

To reference all the data in a column or set of columns without defining the range, use the following syntax.

Column references span from set row (row 1 if not defined) to wherever the content in that column ends.

// references all values in the column from row 1 to the end of the content 
let x = q.cells('A') // returns all the data in the column starting from row 1 to end of data 

let x = q.cells('A:D') // returns all the data in columns A to D starting from row 1 to end of data in longest column

let x = q.cells('A5:A') // returns all values from A5 to the end of the content in column A 

let x = q.cells('A5:C') // returns all values from A5 to end of content in C

let x = q.cells("'Sheet2'!A:C") // 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 1
let x = q.cells('1') 

// Returns all values in rows 1 to 3 
let x = q.cells('1:3') 

// Returns all values in Row 1
let x = q.cells('A1:1')

// Returns all values in Row 1 starting at column C
let x = q.cells('C1:1')

// Returns all values in Row 3 starting at column A
let x = q.cells('A3:3')

// Returns all values in Row 3 starting at column C 
let x = 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 A2
let x = q.cells('A1')

// Copy pasting this one row down will keep reference as A1
let x = q.cells('A$1')

// Example using ranges - row references will not change
let x = q.cells('A$1:B$20')

// Only A reference will change when copied down
let x = q.cells('A1:B$20')