Text Item Questions

[Mark_Harries]

A question for folks as I am a bit stuck and trying to figure my head around this issue, curious if any of the hive mind here have any clarity they can provide.

Essentially I have the table below that I am using for separate sheets within a spreadsheet (Either yearly or quarterly), what I am attempting to do is to have it read through the table and get the period (P1 for example), Start date and end date, then if the current date is within those dates to set the page to said date, for example if it was this day (03/09/25) then it would set the page to sheet P6 for example.

Where I’m getting stuck is in the process of reading the items back in, so I can then use it in the process, essentially I am wanting to break it into 3 items (Currently using tab for this) so I can then use an if statement to be able to compare it to the current date then set it to the applicable sheet, I’ve been trying to use text item statements but when I do so it doesn’t compile so I am coming to ask for a bit of a more experienced eye and to learn hopefully, I’v been reading up on the use of TID’s but still is a bit of a mystery to me.

Test code I am playing with currently:

use AppleScript version "2.8" -- Yosemite (10.10) or later
use scripting additions
use framework "Appkit"

on run
	
	set fY to "Fiscal Year"
	set currentDate to current date -- Complete date
	
	tell application "Numbers"
		
		set docMain to (choose file with prompt "Please select Fiscal sheet") as text
		
		set MyTable to table fY of sheet fY of document 1
		set myList to {}
		
		launch
		activate
		set myDoc to open (docMain)
		
		repeat with i from 2 to (count of rows of MyTable) -- Top row is just titles
			repeat with c from 1 to (count of columns of MyTable)
				set cellValue to value of cell c of row i of MyTable
				set end of myList to cellValue
			end repeat
		end repeat
		
		return myList
		try
			set oldDelims to AppleScript's text item delimiters
			set AppleScript's text item delimiters to tab
			(* set sDay to every text item 2 of myList
set eDay to every text item 3 of myList *)
			set AppleScript's text item delimiters to oldDelims
		on error
			set AppleScript's text item delimiters to oldDelims
		end try
		
		
	end tell
	
end run

[robertfern]

Here’s what I have so far…

use AppleScript version "2.8" -- Yosemite (10.10) or later
use scripting additions

on run
	local myList, myDoc, fy, tid, cdate, fp
	set docMain to (choose file with prompt "Please select Fiscal sheet") as text
	set fy to "Fiscal Year"

	tell application "Numbers"
		activate
		set myDoc to open docMain
		set MyTable to table fy of sheet fy of myDoc
		set myList to {}
		repeat with i from 2 to (count rows of MyTable) -- Top row is just titles
			tell row i of MyTable
				set end of myList to {value of cell 1, value of cell 2, value of cell 3}
			end tell
		end repeat
	end tell
	set cdate to current date
	set fp to 0
	repeat with i from 1 to count myList
		if cdate < ((item 3 of item i of myList) + 1 * days) then
			set fp to item 1 of item i of myList
			exit repeat
		end if
	end repeat
	
	return fp -- current period
	-- Text Items Delimiters not needed to read a Table.
	set tid to my text item delimiters
	set my text item delimiters to tab
	try
		-- set sDay to every text item 2 of myList set eDay to every text item 3 of myList
	end try
	set my text item delimiters to tid
end run

[Nigel_Garvey]

Hi.

If the table’s rows and columns are named as in the image, and the date cells are formatted so that their ‘values’ are returned as AppleScript dates, then the name of the current fiscal period is the name of the row of the first cell of column “End Date” whose value’s greater than or equal to (or does not come before) the current date:

on run
	
	set fY to "Fiscal Year"
	set currentDate to current date -- Complete date
	set currentDate's time to 0
	
	tell application "Numbers"
		-- launch
		activate
		-- Assuming that 'document 1' is already open in Numbers:
		set MyTable to table fY of sheet fY of document 1
		set fiscalPeriod to name of row of first cell of column "End Date" of MyTable whose (value does not come before currentDate)
		
		set docMain to (choose file with prompt "Please select Fiscal sheet") as text
		set myDoc to open (docMain)
		set myDoc's active sheet to myDoc's sheet fiscalPeriod
	end tell
	
end run