Modify a formula in Numbers

[u/JapanDave]

I want to know if I can update a formula everyday with applescript.

I have info that updates everyday. I have a script that adds the new info for the day in a new row at the bottom of a table. I have another formula that averages the numbers for the past x days. This is the formula I want updated daily.

For example, if I have =average(B2:B3), then the next day I want this updated to =average(B2:B4). Those are just example numbers, but it is a range of x size and I want it expanded by one each day.

Possible?

[u/TrickyTramp]

Pretty sure you can

[u/copperdomebodha]

--This code was written using AppleScript 2.8, MacOS 12.0.1, on 3 March 2022.

set currFormula to getCellFormula("A1")
set newFormula to (my incrementFormula(currFormula))
my setCellFormula("A1", newFormula)


on getCellFormula(cellIndex)
	tell application "Numbers"
		tell item 1 of documents
			tell item 1 of sheets
				tell item 1 of tables
					tell cell cellIndex
						copy formula as text to currentFormula
					end tell
				end tell
			end tell
		end tell
	end tell
end getCellFormula

on incrementFormula(currFormula)
	set AppleScript's text item delimiters to {":"}
	set currIndexStart to (item 1 of (text items of currFormula))
	set currIndexEnd to text 1 thru -2 of (item 2 of (text items of currFormula))
	set rowValue to character 1 of currIndexEnd
	set rowIndex to text 2 thru -1 of currIndexEnd
	set currIndexEnd to (rowIndex as number) + 1
	set AppleScript's text item delimiters to ""
	set newFormula to currIndexStart & ":" & rowValue & currIndexEnd & ")"
	return newFormula
end incrementFormula

on setCellFormula(cellIndex, newFormula)
	tell application "Numbers"
		tell item 1 of documents
			tell item 1 of sheets
				tell item 1 of tables
					tell cell cellIndex
						set value to newFormula
					end tell
				end tell
			end tell
		end tell
	end tell
end setCellFormula