C.4 Classes

C.4.1 class Workbook

Indexing for sheets, rows, and columns is one-based. Nearly all methods will throw exceptions when given invalid parameters.

new();

Create an empty Workbook.

Parameters:

none

Returns:

empty Workbook

DESTROY

Destroys a Workbook.

Parameters:

none

Returns:

none

activate();

Sets this Workbook as the active Workbook. Currently, a Workbook must be the active Workbook before calling methods on it or on Worksheets in the Workbook.

Parameters:

none

Returns:

none

setExcelMode(useExcelMode);

Sets this Workbook to use Excel compatibility mode. Turning on Excel compatibility mode results in the range syntax using : (e.g., A1:B3) rather than .. (e.g., A1..B3) to specify ranges. Also, the Excel-compatible functions that are normally prefixed with XL, such as XLCHOOSE, are not prefixed with XL. When dealing with Excel files, this mode is best.

Parameters:

useExcelMode - if true, Excel compatibility mode is turned on

Returns:

none

open(pFileName);

Opens the specified file and puts the contents into the Workbook.

Parameters:

pFileName - the name of the file in Excel 95/97/2000/XP or NExS XS/XS3 format

Returns:

none

save(pFileName,fileType);

Saves the Workbook to a file.

Parameters:

pFileName - the name of the file
fileType - the type of file (Excel 95 or NExS XS3); see the list of file type constants

NOTE: The Excel 95 file format limits the text in cells to a maximum of 255 characters, and SpreadScript will truncate cells exceeding that. Also, the Excel 95 file format supports up to 256 columns and 16,384 rows. SpreadScript produces an exception for sheets with dimensions exceeding those limits.

Returns:

none

createSheet(pName);

Creates an empty Worksheet in this Workbook. The Workbook places the Worksheet at the end of its list of Worksheets.

Parameters:

pName - the name of the Worksheet

Returns:

the newly created Worksheet

deleteSheet(pSheet);

Deletes and destroys the specified Worksheet in this Workbook. Throws an exception if the Worksheet does not exist in this Workbook.

Parameters:

pSheet - the Worksheet to be deleted

Returns:

none

getSheetCount();

Gets the number of Worksheets in this Workbook.

Parameters:

none

Returns:

the number of Worksheets in this Workbook

getSheet(sheetNum);

Gets the specified Worksheet in this Workbook. Throws an exception if the index is not valid.

Parameters:

sheetNum - which Worksheet to get; the first Worksheet is at index 1

Returns:

the Worksheet at the specified position

getSheetByName(pSheetName);

Gets the Worksheet specified by name in this Workbook. Throws an exception if the named Worksheet does not exist in this Workbook.

Parameters:

pSheetName - the name of the Worksheet to get

Returns:

the Worksheet specified by name in this Workbook

getFileName();

Gets the file name for this Workbook.

Parameters:

none

Returns:

the file name of the Workbook or a null reference if it has no file name

C.4.2 class Worksheet

Indexing for sheets, rows, and columns is one-based. Nearly all methods will throw exceptions when given invalid parameters.

delete();

Delete and destroy this Worksheet.

Parameters:

none

Returns:

none

getName();

Get the name of this Worksheet.

Parameters:

none

Returns:

the name

setName(pName);

Sets the name of this Worksheet.

Parameters:

pName - the name

Returns:

none

needsRecalc();

Indicates whether the sheet needs to be recalculated. May indicate the need to recalc when no recalc is necessary, but is always correct if it returns false (zero).

Parameters:

none

Returns:

true (one) if the sheet requires recalculation

recalc();

Recalculates this Worksheet.

Parameters:

none

Returns:

none

getDimensions();

Gets the minimum bounding rectangle of non-empty cells in this Worksheet.

Parameters:

none

Returns:

a Range object with the sheet's dimensions

performGoalSeek(pGoalSeekContext);

Performs a ``goal seek'' operation that is commonly used in what-if scenarios. It answers the question, ``What input generates this result?'' It accomplishes this by systematically varying the input until the desired result is achieved.

The cell that is to be varied during the goal seek is indicated by (varRow, varCol). The cell whose value is intended to match the goal value is indicated by (targetRow, targetCol). The SpreadScript goal seek algorithm uses the ``regula falsa'' (false position) method which requires the target to be bracketed, in this case by the variables bracketLow and bracketHigh. The way it works is as follows: If storing some value bracketLow in cell (varRow, varCol) causes the value in cell (targetRow, targetCol) to be less than the value goal, and storing some other value bracketHigh in (varRow, varCol) caused the value of (targetRow, targetCol) to be greater than goal, then we say that goal is bracketed by bracketLow and bracketHigh. Goal seek works by iteratively closing down the gap between bracketLow and bracketHigh in such a way that the target always remains bracketed. As the gap narrows, the algorithm will eventually find a value which when stored in (varRow, varCol) will cause the value in (targetRow, targetCol) to be equal (within the numerical precision of the computer) to goal. Since that degree of precision is seldom required in real applications, a tolerance parameter is provided. The goal seek algorithm terminates when the absolute value of the difference between the value in (targetRow, targetCol) and goal is less than or equal to tolerance.

The SpreadScript goal seek algorithm does not require bracketLow and bracketHigh to bracket goal initially. If SpreadScript determines that the target is not bracketed, the gap between bracketLow and bracketHigh is expanded in an attempt to bracket goal. Once bracketing has been achieved, the gap is narrowed until the solution is found.

There are a few pathological cases for which goal seek cannot find a solution:

1. A bracket does not exist; i.e., for all possible values that can be stored in cell (varRow, varCol) the value in (targetRow, targetCol) will either be always greater than goal or always less than goal.

2. A bracket exists, but because the function is not continuous there is no value which can be stored in (varRow, varCol) that will cause the value in (targetRow, targetCol) to be equal to goal.

3. A solution exists, but the function is not well behaved on the interval between the bracket values bracketLow and bracketHigh. This generally means that somewhere between bracketLow and bracketHigh the value of (targetRow, targetCol) will go to plus or minus infinity, which causes the algorithm to break down.

To limit the time spent by goal seek in difficult or pathological cases, the parameter maxNumIter will cause the algorithm to give up if a bracket cannot be found within maxNumIter iterations, or once a bracket is found, a solution cannot be found within maxNumIter iterations. Since the final values of bracketLow and bracketHigh are returned to the caller in the GoalSeekContext, a failed goal seek may be continued simply by calling it again. While most functions converge very quickly (linear functions always converge in one or two iterations), some complex functions may simply require more iterations to find a solution. The programmer must decide what action to take in the event that goal seek fails. To assist in this, the following status codes are returned in GoalSeekContext.status:

• SS_GS_SUCCESS - goal seek completed successfully; (varRow, varCol) contains the value that generates the goal in (targetRow, targetCol)

• SS_GS_TARGET_NOT_FORMULA - the target cell (targetRow, targetCol) does not contain a numerical formula, and therefore a solution does not exist.

• SS_GS_VAR_NOT_NUMBER - the variable cell (varRow, varCol) does not contain a numeric constant.

• SS_GS_FP_ERR - a machine floating point error has occurred during the goal seek process, most likely indicating pathological case 3 above.

• SS_GS_BAD_TARGET - the target cell is not numerical; perhaps it is a text string or an error cell

• SS_GS_BRKT_LIMIT - a bracket was not be found after maxNumIter iterations, possibly indicating pathological case 1 above.

• SS_GS_ITER_LIMIT - the target is bracketed, but a solution was not found after maxNumIter iterations, possibly indicating pathological case 2 above.

To facilitate continuation, performGoalSeek does not restore the original contents of cell (varRow, varCol) in the event of failure. It is therefore the programmer's responsibility to save this value if desired.

The performGoalSeek() method modifies bracketHigh, bracketLow, and iterations after using them. Subsequent calls to continue a goal seek thus should pass in the same GoalSeekContext as used in prior calls.

Example:

Given

A1 = 1
A2 = sqrt(A1)

a call to performGoalSeek() with GoalSeekContext containing

varRow = 1
varCol = 1
targetRow = 2
targetCol = 1
goal = 3.14
tolerance = 1e-9
bracketLow = 0
bracketHigh = 10
iterations = 30

converges to a solution with

A1 = 9.8596

and thus the desired goal has been achieved.

Parameters:

pGoalSeekContext - the GoalSeekContext containing the parameters for the goal seek; upon return, bracketLow, bracketHigh, iterations, and status contain updated values

Returns:

none

insertColumns(firstCol,lastCol);

Inserts columns at the specified location. For example, to insert three columns after column two, use insertColumns(3, 5).

Parameters:

firstCol - the column index at which to insert the columns
lastCol - the last column index to insert

Returns:

none

insertRows(firstRow,lastRow);

Inserts rows at the specified location. For example, to insert three rows after row two, use insertRows(3, 5).

Parameters:

firstRow - the row index at which to insert the rows
lastRow - the last row index to insert

Returns:

none

deleteColumns(firstCol,lastCol);

Deletes columns at the specified location.

Parameters:

firstCol - the column index at which to delete the columns
lastCol - the last column index to delete

Returns:

none

deleteRows(firstRow,lastRow);

Deletes rows at the specified location.

Parameters:

firstRow - the row index at which to delete the rows
lastRow - the last row index to delete

Returns:

none

setDefaultFont(font);

Set the default cell font for this Worksheet.

Parameters:

font - the default font; see the list of font constants

Returns:

none

getDefaultFont();

Gets the default cell font for this Worksheet.

Parameters:

none

Returns:

the default font; see the list of font constants

setDefaultFontSize(size);

Sets the default cell font size for this Worksheet.

Parameters:

size - the default font size; see the list of font size constants

Returns:

none

getDefaultFontSize();

Gets the default cell font size for this Worksheet.

Parameters:

none

Returns:

the default font size; see the list of font size constants

setDefaultFontStyle(style);

Sets the default cell font style for this Worksheet.

Parameters:

style - the default font style; see the list of font style constants

Returns:

none

getDefaultFontStyle();

Gets the default cell font style for this Worksheet.

Parameters:

none

Returns:

the default font style; see the list of font style constants

setDefaultHorizontalJustification(hjust);

Sets the default cell horizontal justification for this Worksheet.

Parameters:

hjust - the default horizontal justification; see the list of horizontal justification constants

Returns:

none

getDefaultHorizontalJustification();

Gets the default cell horizontal justification for this Worksheet.

Parameters:

none

Returns:

the default horizontal justification; see the list of horizontal justification constants

setDefaultWordWrap(wrap);

Sets the default cell word wrapping for this Worksheet.

Parameters:

wrap - the default word wrapping; see the list of word wrapping contants

Returns:

none

getDefaultWordWrap();

Gets the default cell word wrapping for this Worksheet.

Parameters:

none

Returns:

the default word wrapping; see the list of word wrapping constants

setDefaultUnderline(under);

Sets the default cell underlining for this Worksheet.

Parameters:

under - the default underlining; see the list of underlining constants

Returns:

none

getDefaultUnderline();

Gets the default cell underlining for this Worksheet.

Parameters:

none

Returns:

the default underlining; see the list of underlining constants

setDefaultFormatAndPlaces(format,places);

Sets the default cell format and number of decimal places for this Worksheet.

Parameters:

format - the default format; see the list of format constants
places - the number of places

Returns:

none

getDefaultFormatAndPlaces();

Gets the default cell format and number of decimal places for this Worksheet.

Parameters:

none

Returns:

a list/array containing the default format (see the list of format constants) and decimal places

setDefaultFormat(format);

DEPRECATED Use setDefaultFormatAndPlaces() instead.
Sets the default cell format for this Worksheet.

Parameters:

format - the default format; see the list of format constants

Returns:

none

getDefaultFormat();

DEPRECATED Use getDefaultFormatAndPlaces() instead.
Gets the default cell format for this Worksheet.

Parameters:

none

Returns:

the default format; see the list of format constants

setDefaultPlaces(places);

DEPRECATED Use setDefaultFormatAndPlaces() instead.
Sets the number of decimal places for numbers in cells in this Worksheet whose format is set to the default format SS_FMT_DEFAULT.

Parameters:

places - the number of places

Returns:

none

getDefaultPlaces();

DEPRECATED Use getDefaultFormatAndPlaces() instead.
Sets the number of decimal places for numbers in cells in this Worksheet whose format is set to the default format SS_FMT_DEFAULT.

Parameters:

none

Returns:

the number of places

setDefaultColumnWidth(colWidth);

Sets the default width for columns in this Worksheet.

Parameters:

colWidth - the default column width in units of 1/100th of the zero character's width

Returns:

none

getDefaultColumnWidth();

Gets the default width for columns in this Worksheet.

Parameters:

none

Returns:

the default column width in units of 1/100th of the zero character's width

setDefaultRowHeight(rowHeight);

Sets the default height for rows in this Worksheet.

Parameters:

rowHeight - the default row height in units of 1/20th of a point, also known as twips (twentieths of a point)

Returns:

none

getDefaultRowHeight();

Gets the default height for rows in this Worksheet.

Parameters:

none

Returns:

the default row height in units of 1/20th of a point, also known as twips (twentieths of a point)

setColumnWidths(firstCol,lastCol,colWidth);

Sets the widths of the specified columns.

Parameters:

firstCol - the starting column index
lastCol - the last column index
colWidth - the column width in units of 1/100th of the zero character's width

Returns:

none

getColumnWidth(col);

Gets the width of the specified column.

Parameters:

col - the column index

Returns:

the column width in units of 1/100th of the zero character's width

setRowHeights(firstRow,lastRow,rowHeight);

Sets the heights of the specified rows.

Parameters:

firstRow - the starting row index
lastRow - the last row index
rowHeight - the row height in units of 1/20th of a point

Returns:

none

getRowHeight(row);

Gets the height of the specified row.

Parameters:

none

Returns:

the row height in units of 1/20th of a point

getType(row,col);

Gets the type of cell contents for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the type of cell contents; see the list of cell type constants

setText(row,col,pString);

Sets the specified cell to contain the specified string.

NOTE: Microsoft Excel 95 file format supports up to 255 characters in a cell. If there are more present, the string will be truncated to 255 characters when saving to that file format.

Parameters:

row - the row index of the cell
col - the column index of the cell
pString - the text string

Returns:

none

getText(row,col);

Gets the contents of the specified cell as a string.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the cell contents as a string

setNumber(row,col,val);

Puts a number in the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
val - the number

Returns:

none

getNumber(row,col);

Gets the contents of the specified cell as a number.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the number in the cell, or 0.0 if the cell doesn't contain a number

setFormula(row,col,pFormula);

Puts the formula in the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
pFormula - the formula string in SpreadScript syntax

Returns:

none

getFormula(row,col);

Gets the formula contained in the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the formula string in SpreadScript syntax, or an empty string if the cell doesn't contain a formula

setFont(row,col,font);

Sets the font for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
font - the font; see the list of font contants

Returns:

none

getFont(row,col);

Gets the font for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the font; see the list of font contants

setFontSize(row,col,size);

Sets the font size for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
size - the font size; see the list of font size contants

Returns:

none

getFontSize(row,col);

Gets the font size for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the font size; see the list of font size constants

setFontStyle(row,col,style);

Sets the font style for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
style - the font style; see the list of font style contants

Returns:

none

getFontStyle(row,col);

Gets the font style for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the font style; see the list of font style constants

setHorizontalJustification(row,col,hjust);

Sets the horizontal justification for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
hjust - the horizontal justification; see the list of horizontal justification contants

Returns:

none

getHorizontalJustification(row,col);

Gets the horizontal justification for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the horizontal justification; see the horizontal justification constants list

setWordWrap(row,col,wrap);

Sets the word wrapping for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
wrap - the word wrapping; see the list of word wrapping contants

Returns:

none

getWordWrap(row,col);

Gets the word wrapping for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the word wrapping; see the list of word wrapping contants

setUnderline(row,col,under);

Sets the underlining for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
under - the underlining; see the list of underlining contants

Returns:

none

getUnderline(row,col);

Gets the underlining for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the underlining; see the list of underlining contants

setFormatAndPlaces(row,col,format,places);

Sets the format and the number of decimal places for numbers in the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
format - the format; see the list of format contants
places - the number of decimal places

Returns:

none

getFormatAndPlaces(row,col);

Gets the format and number of decimal places for the specified cell.

Parameters:

none

Returns:

a list/array containing the format (see the list of format contants) and places

setFormat(row,col,format);

DEPRECATED Use setFormatAndPlaces() instead.
Sets the format for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
format - the format; see the list of format contants

Returns:

none

getFormat(row,col);

DEPRECATED Use getFormatAndPlaces() instead.
Gets the format for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the format; see the list of format contants

setPlaces(row,col,places);

DEPRECATED Use setFormatAndPlaces() instead.
Sets the number of decimal places for numbers in the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
places - the number of decimal places

Returns:

none

getPlaces(row,col);

DEPRECATED Use getFormatAndPlaces() instead.
Gets the number of decimal places for numbers in the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the number of decimal places

setBorder(row,col,pBorder);

Sets the borders for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell
border - the Border object with the cell borders

Returns:

none

getBorder(row,col);

Gets the borders for the specified cell.

Parameters:

row - the row index of the cell
col - the column index of the cell

Returns:

the Border object with the cell borders

C.4.3 class Border

new(top,bottom,left,right);

Creates a Border object with the specified borders. See the list of border style constants.

Parameters:

top - the border style for the top edge of a cell
bottom - the border style for the bottom edge of a cell
left - the border style for the left edge of a cell
right - the border style for the right edge of a cell

Returns:

the newly created Border object

DESTROY

Destroys this Border object.

Parameters:

none

Returns:

none

setBottom(type);

Sets the bottom border style of a cell.

Parameters:

type - the style of border; see the list of border style constants

Returns:

none

getBottom();

Gets the bottom border style of a cell.

Parameters:

none

Returns:

the style of the bottom border; see the list of border style constants

setTop(type);

Sets the top border style of a cell.

Parameters:

type - the style of border; see the list of border style constants

Returns:

none

getTop();

Gets the top border style of a cell.

Parameters:

none

Returns:

the style of the top border; see the list of border style constants

setLeft(type);

Sets the left border style of a cell.

Parameters:

type - the style of border; see the list of border style constants

Returns:

none

getLeft();

Gets the left border style of a cell.

Parameters:

none

Returns:

the style of the left border; see the list of border style constants

setRight(type);

Sets the right border style of a cell.

Parameters:

type - the style of border; see the list of border style constants

Returns:

none

getRight();

Gets the right border style of a cell.

Parameters:

none

Returns:

the style of the right border; see the list of border style constants

C.4.4 class Range

new(pSheet,rowUL,colUL,rowLR,colLR);

Creates a new Range object.

Parameters:

pSheet - the parent Worksheet
rowUL - upper left row
colUL - upper left column
rowLR - lower right row
colLR - lower right column

Returns:

new Range object

DESTROY

Destroys a Range object.

Parameters:

none

Returns:

none

Range_createFromString(pSheet,pString);

Creates a new Range object from a string description, such as ``A1..B3''.

Parameters:

pString - the string describing the range (e.g., ``A1..B3'')

Returns:

new Range object or NULL/None if the string is not a valid range

toString();

Gets a string representation of this Range object, such as ``A1..B3''.

Parameters:

none

Returns:

the string describing the range (e.g., ``A1..B3'')

getBounds();

Gets the bounds of this range.

Parameters:

none

Returns:

a list/array containing the bounds of this range.

getMinRow();

Gets the minimum row in this range.

Parameters:

none

Returns:

the minimum row in this range

getMaxRow();

Gets the maximum row in this range.

Parameters:

none

Returns:

the maximum row in this range

getMinColumn();

Gets the minimum column in this range.

Parameters:

none

Returns:

the minimum column in this range

getMaxColumn();

Gets the maximum column in this range.

Parameters:

none

Returns:

the maximum column in this range

getParent();

Gets a reference to the range's parent Worksheet.

Parameters:

none

Returns:

a reference to the parent Worksheet

setText(pString);

Sets each cell in this range to contain the specifed string.

Parameters:

pString - the string

Returns:

none

setNumber(val);

Sets each cell in this range to contain the specified numerical value.

Parameters:

val - the numerical value

Returns:

none

setFormula(pFormula);

Sets each cell in this range to contain the specified formula. Relative cell references are adjusted as they would be when pasting a formula in an interactive spreadsheet.

Parameters:

pFormula - the formula string

Returns:

none

setFont(font);

Sets the font for cells in this Range.

Parameters:

font - the font; see the list of font contants

Returns:

none

setFontSize(size);

Sets the font size for cells in this Range.

Parameters:

size - the font size; see the list of font size contants

Returns:

none

setFontStyle(style);

Sets the font style for cells in this Range.

Parameters:

style - the font style; see the list of font style contants

Returns:

none

setHorizontalJustification(hjust);

Sets the horizontal justification for cells in this Range.

Parameters:

hjust - the horizontal justification; see the list of horizontal justification contants

Returns:

none

setWordWrap(wrap);

Sets the word wrapping for cells in this Range.

Parameters:

wrap - the word wrapping; see the list of word wrapping contants

Returns:

none

setUnderline(under);

Sets the underlining for cells in this Range.

Parameters:

under - the underlining; see the list of underlining contants

Returns:

none

setFormatAndPlaces(format,places);

Sets the format and number of decimal places for cells in this Range.

Parameters:

format - the format; see the list of format contants
places - the number of decimal places

Returns:

none

setBorder(pBorder);

Sets the borders for cells in this Range.

Parameters:

pBorder - the Border object with the cell borders

Returns:

none

setBorderEdges(pEdgeBorder,pInteriorBorder);

Sets the borders around the outermost edges of this range and, optionally, the interior edges. In other words, the top row of cells gets the top boder, the left side the left border, the bottom side the bottom border, and the right side the right border. If the interior border is specified, it is set on the interior cell boundaries.

Parameters:

pEdgeBorder - the Border object with the cell borders for outer edges of the outer cells
pInteriorBorder - the Border object with the cell borders for the interior cell edges; this argument is optional

Returns:

none

exportTSV(pFileName);

Exports this range as tab separated values to the specified file.

Parameters:

pFileName - the destination file

Returns:

none

exportCSV(pFileName);

Exports this range as comma separated values to the specified file.

Parameters:

pFileName - the destination file

Returns:

none

exportHTML(pFileName);

Exports this range as a formatted HTML table to the specified file.

Parameters:

pFileName - the destination file

Returns:

none

exportLaTeX2E(pFileName);

Exports this range as a LaTeX 2E table to the specified file.

Parameters:

pFileName - the destination file

Returns:

none

exportLaTeX209(pFileName);

Exports this range as a LaTeX 2.09 table to the specified file.

Parameters:

pFileName - the destination file

Returns:

none

importTSV(pFileName,monthDayOrder);

Imports the tab separated values from the specified file and inserts them into this range.

Parameters:

pFileName - the destination file

Returns:

none

importCSV(pFileName,monthDayOrder);

Imports the comma separated values from the specified file and inserts them into this range.

Parameters:

pFileName - the destination file

Returns:

none

copy(pDstRange,propsToCopy);

Copies cells, including formulas, from this range to the destination range. The relative addresses in the formulas will be properly adjusted. The shape of the source range must match the destination range or the source or destination range must be a single cell.

Parameters:

pDstRange - the destination range or upper left cell
propsToCopy - which cell properties to copy; see the list of cell property constants

Returns:

none

copyValues(pDstRange,propsToCopy);

Copies cell values, without formulas, from this range to the destination range. The shape of the source range must match the destination range or the source or destination range must be a single cell.

Parameters:

pDstRange - the destination range or upper left cell
propsToCopy - which cell properties to copy; see the list of cell property constants

Returns:

none

clear();

Clears the contents of the cells in this range.

Parameters:

none

Returns:

none

move(dstRow,dstCol);

Moves cells from this range to the destination cell representing the upper left corner of the new location. All formulas, named ranges, etc. referencing cells in the moved range are updated.

Parameters:

dstRow - the destination row index
dstCol - the destination column index

Returns:

none

scroll(rowDelta,colDelta);

Moves cells from this range by the specified amount. The difference between moveRange and scrollRange is that all formulas, named ranges, etc. referencing cells in the moved range are not updated.
This is useful, for example, when retrieving data from a live source and needing to compute the average (or other statistics) of the last 10, 20, and 50 values. The formulas that reference the data cells aren't changed, but the data in the cells change. If this range represents A1..A50, calling scrollRange(1, 0) would move 49 old values down, leaving the first row empty, and putting new live data in the first row would keep the 50 most recent values in the first 50 rows of column A.

Parameters:

rowDelta - the number of rows by which to shift the range; a positive number moves the range down in the sheet, and negative moves it up
colDelta - the number of columns by which to shift the range; a positive number moves the range left in the sheet, and negative moves it right

Returns:

none

sortRows(pSort);

Sort the rows of this range into ascending or descending order based on the values in specified columns. Before sorting a range of cells, consider these rules that SpreadScript follows for sorting:

• Cells containing text (labels) or text-valued formulas are sorted in lexical order (alphabetical order following the same sorting conventions as a dictionary). Cells containing numeric formulas or constants are sorted by the numeric value of the cell.

• When sorting a mixed range of numeric and string values, SpreadScript assigns the following priority order to different cell types:
  • cells containing text values are less than those with numeric values,

  • numeric values are ordered from largest-magnitude negative to largest-magnitude positive,

  • and empty cells are considered highest-ranking for ascending sorts and lowest-ranking for descending sorts. This places empty cells at the bottom of the results.

• SpreadScript does not modify range references within the sorted range. This avoids the risk of cells in the referenced range becoming non-contiguous.

• When the sort includes named cells, SpreadScript updates the definition of the named cell to reflect its new location. Named ranges are not changed.

• SpreadScript keeps track of what it moves and where - and updates cell references throughout the spreadsheet to reflect the changes that occurred while sorting. However, SpreadScript lets you choose not to update cell references, if you wish.

Parameters:

pSort - the SortContext object describing how to perform the sort

The SortContext parameter specifies how the sort is performed. The columns specified as keys are used as primary, secondary, etc. keys to sort the data. Up to five keys may be used. The sort uses keys beyond the primary key in cases where the primary does not differentiate the entries. For each key, an order of ascending or descending is specified. If the update field in SortContext is true, updates cell references according to where the referenced cell has moved.

Returns:

1 if successful, 0 otherwise