Apps Plotter Workbook Image Processing Script Editor Command Editor std Library Overview Road Map Developer

Range Class

A range is a user specified region within a worksheet. It does not have a reserved internal data storage; only reads from and writes to the specified region by the user.


Creating Range

Unlike Worksheet, where indices (1,1) refers to A1, for a Range, (1,1) refers to the topleft corner of the specified region.

-- (1,1) refers to B1

--Create a range from the selection of the user
>> range=ws:selection()


Important note: If the Worksheet owning the range(s) is closed, the range(s) becomes invalid.

--Create a range
>>"Sheet 1!A1:B5")

--check the range
Sheet 1!A1:B5

--close the worksheet manually and check the range
userdata --no more access to the range



Accessing cells

You can read from and write to the region specified by the range inside the Worksheet.

--create a range [region is 2x2, from B2 to C3]
>>"Sheet 1!B2:C3")

--set B2 value as 10
>>r:set(1, 1, 10)

--read from B2
>>r(1, 1)

--set C3 value as "a"
>>r:set(2, 2, "a")

--read from C3
>>r(2, 2)

--Attempt to write at index (3,3)
>>r:set(3, 3, "c")
ERROR: Requested row is out of boundaries of the range



Member functions



col(i) → Array

Returns the ith column as an Array object.




coords() → Lua table, Lua table

Returns the top-left and bottom-right coordinates, with respect to the worksheet. The Lua table has keys r and c to show the location of row and column as integers, respectively.

>>"Sheet 1!B2:C5")

>>t1, t2=r:coords()

c=2    r=2

c=3    r=5




get(i, j) → number/string

Returns the element at ith row and jth column. If range is a Range variable, then instead of range:get(i, j), range(i, j) can be used as well.




parent() → Worksheet

Returns the Worksheet which owns the range.

Sheet 1!A1:B2

Sheet 1





Selects the range on the worksheet.

Sheet 1!A1:B2





set(i, j, val)

Sets the value of the cell at ith row and jth column with the value, val.




sort(pos, order, casesensitive=false)

Sorts the whole Range according to the selected column position, pos, by given order. If the parameter order="A" or order="D" then the Range is sorted in ascending or descending order, respectively. By default, the sorting is case-sensitive. Note that, numbers are considered to be smaller than letters in a sort order.




subrange({row=, col=}, nrows=, ncols=) → Range

Returns a range defined within the current range object. If the defined range exceeds the boundaries of the current range, then a nil value is returned.

Arguments row and col are top-left coordinates of the defined range and must be greater than zero and must be within the boundaries of the current range. The top-left coordinates of current range is (1, 1). nrows is the number of rows included in the defined range and similarly ncols is the number of columns included in the defined range. If nrows or ncols is assigned a negative number, then all the rows or columns of the current range as of the defined top-left coordinate is included in the defined sub-range.

--rng is the current range object (already has been created)
Sheet 1!B4:D10

--We are defining a range within rng
>>rng:subrange({row=1, col=1}, 2, -1)
Sheet 1!B4:D5




tostring() → string

Returns information on the selection as string, such as in the first example, range:tostring() will return "Sheet1!B1:B5".