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')