Formulas in Numbers with Apple Script

Here is something that automagically calculates the next month total formula, and assigns it to that total cell.

-- Based on current month name, construct new total formula for that column
-- and assign it to that month's total cell based on the following criteria:
-- 1) previous month has total value
-- 2) current month has row 2 (week 1) cell value that is non-zero.
-- Assumption: Numbers spreadsheet is already open in Numbers

-- reference: https://discussions.apple.com/thread/250236627
-- version: 1.0
-- tested: macOS High Sierra 10.13.6 (17G5019) with Numbers v5.3
-- VikingOSX, 2019-03-16, Apple Support Communities, No warranties at all.

property mname : month of (current date)

tell application "Numbers"
	tell front document
		tell active sheet's first table
			set {newFormula, prevSum, cell2, curSum} to my new_sum_formula(mname) as list
			if value of cell prevSum is 0 or value of cell prevSum is missing value then
				return
			end if
			if value of cell cell2 > 0 then
				set value of cell curSum to newFormula
			end if
		end tell
	end tell
end tell
return

on new_sum_formula(mname)
	-- returns:
	-- 1) new formula for current month totals
	-- 2) prevSum - cell address of previous months total
	-- 3) cell2 - current month columns cell 2
	-- 4) curSum - address of current months total cell
	-- alpha column letters for year of months
	set letters to "BCDEFGHIJKLM"
	set myindex to 0
	-- get the letter matching the index of the current month
	set alpha to text (myindex + (mname as integer)) of letters
	set curSum to alpha & 7 as text
	set cell2 to alpha & 2
	-- last month alpha
	set beta to text (myindex + (mname as integer) - 1) of letters
	set prevSum to beta & 7 as text
	return {"=SUM(" & alpha & 2 & ":" & alpha & 6 & ")", prevSum, cell2, curSum}
end new_sum_formula

I'd probably take advantage of Numbers scripting properties to do this a little more succinctly:

tell application "Numbers"
	tell front document
		tell active sheet's first table
			tell (row 7's first cell whose value is missing value)
				set colNam to its column's name --> e.g. "C"
				set its value to "=SUM(" & colNam & 2 & ":" & colNam & 6 & ")"
			end tell
		end tell
	end tell
end tell

Or, if one uses a Footer Row "Numbers style" which allows the formula =SUM(C) rather than =SUM(C2:C6) then the script becomes correspondingly simpler:

tell application "Numbers"
	tell front document
		tell active sheet's first table
			tell (row 7's first cell whose value is missing value)
				set colNam to its column's name --> e.g. "C"
				set its value to "=SUM(" & colNam & ")"
			end tell
		end tell
	end tell
end tell

The script looks for the first empty cell in row 7 to place the formula.

SG

You are welcome.

Precisely why I had this moved into the Numbers community. Needed more efficiency.