Last updated: 1999.08.26
Draw Cell BordersGenerally, to manipulate cells, they must be referred to as elements of the spreadsheet, which is a property of the document, or elements of a spreadsheet frame. For the former this would be of the form:
tell application "AppleWorks"
tell spreadsheet of front document
-- script goes here
A spreadsheet or database can also call custom functions created in AppleScript, using AppleWorks' MACRO function. To call an AppleScript compiled script file called "My Script File", containing the routine "calculate", for example, a cell or database definition should contain a formula of the form:
=MACRO("My Script File",2,"calculate",reference1, reference2, ...)
and the script file should contain a routine of the form:
on calculate(input1, input2, ...)
-- do some calculations
set myResult to <the final answer>
Note that the routine name must be all lowercase (bug in AppleWorks).
You have to change the formula to get it to work in some countries (eg not Australia or USA). For instance, in the Danish version of ClarisWorks:
=MAKRO("My Script File";2;"calculate"; reference1, reference2, ...)
Some sample scripts appear below. General installation and use instructions appear on our scripts page. This page is part of BareFeetWare's AppleScripting AppleWorks project.
Draws a grid of lines over the borders of the selected cells. Then you can can select those lines, group them, change their color, pattern and thickness. Provides greater flexibility than AppleWorks built in borders. Of course, you can disable the display of the spreadsheet grid for a better effect.Example
Part of the Enhancement Pack for AppleWorks. 1999.08.26Shade Alternate Rows
Shades every second row of cells in the selection. Sets the fill color to light blue. In most cases, this can be better achieved using AppleWorks styles.Script
Download: Shade Alternate Rows. 1999.07.07Mode
Calculates the mode (score/s with the highest frequency) of the selected cells or the cells entered in a formula. Run by one of:Script
- Select some cells in a spreadsheet, run the script from the Scripts menu. A dialog box will display the mode.
- or Enter a formula into a cell: =MACRO("Mode",2,"calculate",A1..A5), changing the cell range to refer to your desired input cells. The formula cell will display the mode calculation results and update if any data cells are changed.
Part of the Enhancement Pack for AppleWorks. 1999.07.04Regression: Power & Exponential
I have now made the two scripts for Exponential Regression and Power Regression. The scripts have used your Linear Regression as a template. I have marked the changes from the original Linear Regression with a "--!".Script
Both scripts require the Satimage scripting addition. Place it in your Scripting Additions folder.
Author: Per Hammershoej Jensen. Last updated 1999.06.22.
Download: Exponential Regression and Power regression. Both require the Satimage scripting addition.Rank
Calculates the rank of a value within a range of cells. The rank of a number within a list is the position at which it would appear if the list was sorted.Script
eg 34 has a rank of 2 in the cell range containing 67, 15, 54, 34, 76.
Enter a formula into a cell: =MACRO("Rank",2,"calculate",theCell+0,theList), changing theCell and theRange to refer to your desired input cells.
eg: If you have a list of numbers in the cells A1 through A5 and want to display the rank of each number in the B column next to each, you would enter =MACRO("Rank",2,"calculate",A1+0,A$1..A$5) into cell B1, then fill it down to B5.
The formula cell will display the rank calculation results and update if any data cells are changed.
Author: BareFeetWare. Inspired by Jim Beckers.
Part of the Enhancement Pack for AppleWorksLink Cell
Link to a cell in another spreadsheet. To get to a cell from another source spreadsheet, use the formula:Script
where documentName is the name of the source spreadsheet document and cellName is the name or location of the source cell, eg "sub total" or "B6".
Note that the formula will not auto recalculate when the source cell is changed, but will recalculate when the link cell is moved, edited, or another cell on which it depends is changed.
Author: BareFeetWare. Last updated 1999.06.10.
Download: Link CellLinear Regression
Uses the linear regression (aka least square fitting) method to calculate the slope, y intercept and correlation coefficient of the line best fitting x and y data. Run by one of:Script
Author: BareFeetWare. Last updated 1999.06.02.
- Select x and y data in two columns in a spreadsheet, run the script from the Scripts menu. A dialog box will display the results.
- Or, enter a formula into a cell: =MACRO("Linear Regression",2,"slope",x_range, y_range), changing the cell ranges to refer to your desired input cells. Replace slope with intercept or correlation in another cell to calculate the other results. The formula cells will display the calculation results and update if any data cells are changed.
Part of the Enhancement Pack for AppleWorks including sample spreadsheet using the function.Separate First Words
Select some spreadsheet cells in a column and the blank column to its right. Run this script. The first column of cells will contain the first word of the original cells. The rest of the words of each cell will appear in the blank cell to the right.Example
Download: Separate First WordsMedian
Calculates the median (middle score of all sorted) of the selected cells or the cells entered in a formula. Run by one of:Script
- Select some cells in a spreadsheet, run the script from the Scripts menu. A dialog box will display the median.
- or Enter a formula into a cell: =MACRO("Median",2,"calculate",A1..A5), changing the cell range to refer to your desired input cells. The formula cell will display the median calculation results and update if any data cells are changed.
Part of the Enhancement Pack for AppleWorks. Last updated 1999.07.04: takes 50% of time on large samples.
Upper Case CellsAuthor: Hugh Dixon <HDixon@bepp.co.uk>
Download: Running Total
Operates a "running total" on a spreadsheet, with a figure for payment/withdrawal prompted for and inserted into cell B2, and the running total inserted into cell B3. Does not trap inputting errors (letters, signs etc) and does not check that front document is a spreadsheet. Does accept negative numbers.
Changes the text in every selected cell to upper case. Author: BareFeetWare.Script
Download: Upper Case CellsAlternate Column Widths
Sets the width of each column in the selection to match the first or second column. Odds match first, evens match second. Author: BareFeetWare.Script
Download: Alternate Column WidthsNegative Cells Red
Sort Through ColumnsPart of the Enhancement Pack for AppleWorks.
Select a range of cells in an AppleWorks spreadsheet. Run this script (from the scripts menu) to set the text color to red for all cells containing negative numbers.
-4 5 8
-4 5 8 3 6 -43 3 6 -43 -7 -89 1 -7 -89 1 2 -5 76 2 -5 76
Today in CellDownload: Sort Through Columns
Select a range of cells in a spreadsheet. This script will sort all cells so that they appear in ascending order with smallest at top of column 1, then larger progressing down column 1 then filling next columns. During run, the user must click OK in the Sort dialog.
4 5 8
1 5 8 3 6 43 2 5 43 7 89 1 3 6 76 2 5 76 4 7 89
ContactDownload: Today In Cell Author: BareFeetWare.
Set the value of a the first cell in the selection to today's date.
Please don't hesitate to contact us if you have any questions.
© 1999 BareFeetWare
Return to Parent Folder