AppleScript for Numbers would write to cells but not read

I think the beginning simply needs to be written like this. You can't get the cell of a sheet, it belongs to a table.

tell application "Numbers"

tell table 1 of active sheet of the front document

set lastRow to the count of rows

You can set variables that define objects:

set srceTable to a reference to table "Table 1" of sheet "Sheet 1"

set destTable to a reference to table "Table 1" of sheet "Sheet 2"

Since you work in the same document all the time enclose everything in

tell application "Numbers"

tell document 1

...

...

end tell

end tell

In the end the complete script is:

set srceSheetName to "Sheet 1"

set destSheetName to "Sheet 2"

set srceTblName to "Table 1"

set destTblName to "Table 1"

set srceRng to "H2:H16"

set destRng to "A2:A16"

tell application "Numbers"

tell document 1

set srceTable to a reference to table srceTblName of sheet srceSheetName

set destTable to a reference to table destTblName of sheet destSheetName

set destCol to a reference to destTable's range destRng's column 1

repeat with c in srceTable's range srceRng's cells

set v to c's value

set r to c's row's address

set destCol's cell r's value to v

end repeat

end tell

end tell

It doesn't change anything to the operations but for better comprehension of your spreadsheets (in general) you should give meaningful names to your sheets and tables. Instead of "Table 1" a table could have the name "Input data", a sheet could have the name "2023", etc. When you look at inter-table formulas you have a better grasp of where data comes from.

Actually, as written the script is bound to have the source and destination ranges on the same rows. What if you want to copy from H2:H16 to A4:A18? This updated version makes the script more adaptable as the two ranges can be arbitrarily defined, even one vertically (H2:H9) and the other horizontally (A2:H2), or even linear to square (H2:H9 to A2:B5). The only limit (without error catching) is that the destination range must contain at least as many cells than the source range.

set srceSheetName to "Sheet 1"

set destSheetName to "Sheet 2"

set srceTblName to "Table 1"

set destTblName to "Table 1"

set srceRng to "H2:H16"

set destRng to "A4:A18" -- shape and location is not bound by srceRng's shape and location

tell application "Numbers"

tell document 1

set srceTable to a reference to table srceTblName of sheet srceSheetName

set destTable to a reference to table destTblName of sheet destSheetName

set srceRange to a reference to srceTable's range srceRng

set destRange to a reference to destTable's range destRng

repeat with c from 1 to the count of cells of srceRange

set destRange's cell c's value to srceRange's cell c's value

end repeat

end tell

end tell

I saw your other questions in the other post Looking for a good sample of an apple scr… - Apple Community. This answers them as well.

set srceSheetName to "Sheet 1"

set destSheetName to "Sheet 2"

set srceTblName to "Table 1"

set destTblName to "Table 1"

set srceRng to "H2:H16"

set destRng to "A2:A16"

tell application "Numbers"

tell document 1

set srceTable to table srceTblName of sheet srceSheetName

set srceRange to srceTable's range srceRng

if exists sheet destSheetName then

set destSheet to sheet destSheetName

else

set destSheet to (make new sheet with properties {name:destSheetName})

tell destSheet to delete table 1

end if

tell destSheet

if exists table destTblName then

set destTable to table destTblName

set locked of destTable to false

else

set destTable to (make new table with properties {name:destTblName})

end if

end tell

tell destTable

set row count to srceTable's row count

set column count to srceTable's column count

set header row count to srceTable's header row count

set header column count to srceTable's header column count

set footer row count to srceTable's footer row count

end tell

set destRange to destTable's range destRng

set dc to 0

repeat with c from 1 to the count of cells of srceRange

set v to srceRange's cell c's value

if v is not missing value then

set dc to dc + 1

set destRange's cell dc's value to v

end if

end repeat

end tell

end tell

Explanations about the last script I posted at 2:13 PM.

I had to consider these conditions:

1a) sheet destSheetName exists -> OK

1b) sheet destSheetName doesn't exist -> create a sheet with that name automatically including a default table named "Table 1"; delete that table.

2a) sheet destSheetName already has a table named destTblName -> OK

2b) sheet destSheetName doesn't have a table named destTblName -> create a table with that name.

3) resize table destTblName according to table srceTblName. To be more elaborate I could have avoided to resize a pre-existing table destTblName, but oh well...

All this to make sure at first that I get a sheet named destSheetName with a table named destTblName.

Sure, instead of deleting table "Table 1" after creating destSheetName I could have kept it and just changed its dimensions, but this a two-step process. There could have been a table "Table 1" on an existing destSheetName and I didn't want to mess with it. Remember that sheets and tables can have any name and nothing is to be assumed about existing conditions when making foolproof programs.

Finally, to prevent program crashes if destRange contains less cells than srceRange I would enclose the value setting in try like this:

try

set destRange's cell dc's value to v

end try

Applescript receives dates from Numbers relative to GMT, which means that the result is different whether your current time is in standard or daylight saving period, and whether the dates you're reading are in standard or daylight saving period.

Whenever I have a script that deals with dates obtained from a Numbers spreadsheet I use the following to standardize everything.

use scripting additions -- essential to use (current date) with framework "Foundation"
use framework "Foundation"

global sysZone

on run
	set sysZone to current application's NSTimeZone's systemTimeZone()

	-- rest of the script
	
	tell application "Numbers"

        -- Example of use of the handler
        tell table 1 of sheet 1 of document 1
			
			set aDate to value of cell "D4"
            -- D4 only shows a date (with hidden time 00:00 unless entered explicitely)
			set aTime to value of cell "E4"
            -- E4 only shows a time (with hidden date of when cell entry was made)

            -- combining both informations
			set goodDate to my correctedDate(aDate)
			set time of goodDate to time of my correctedDate(aTime)

         end tell 

	end tell

end run


to correctedDate(_date)
	if class of _date is date then
		return _date + sysZone's secondsFromGMT() - (sysZone's secondsFromGMTForDate:_date)
	else
		return _date
	end if
end correctedDate

I missed an item from your previous post, where you said "I thought it would copy just the string" (of the date).

That's fine if your only intent is to copy a date value from one cell to the other without using it for calculations in the script, in that case you don't need to apply any correction (reading and writing will cancel each other's offset). But if you use the date for any kind of calculation or to output it to a text file or a Calendar event the correction is necessary.

You can read either a cell's formatted value (as it is shown) or its value (as it is stored), for every kind of cell content actually. Using formatted value with dates might be dangerous because while the underlying value is always the same how it's presented changes with the format applied to it in the cell and AppleScript may misinterpret some formats. For example if the format omits the year then the year of the formatted value will be set to the current year and not the year of the date in the cell. Or if the format inverts month and day the result will be wrong. Just be aware of these possibilities.

I also modified my handler following these tests. I had never before written date values back to a cell (only to text files as text) so I had missed a point: a reverse correction is needed. So here is a better version.

to correctedDate(_date, _fromNumbers)
	-- _fromNumbers must be 1 when reading from Numbers or -1 when writing to Numbers
	if class of _date is date then
		return _date + _fromNumbers * ((sysZone's secondsFromGMT()) - (sysZone's secondsFromGMTForDate:_date))
	else
		return _date
	end if
end correctedDate

And to use it for reading and writing:

set goodDate to my correctedDate(value of cell "D4", 1)
set value of cell "D5" to my correctedDate(aDate, -1)

Interesting. So unlike simply adding or subtracting vanilla AppleScript's (time to GMT) this approach can adjust properly for date-times in the past or future, remembering whether daylight savings was in effect on that date, etc?

SG

Yes. But it's only necessary with dates exchanged with Numbers, because within Applescript itself you don't have to worry about that (unless you want to explicitly deal with different time zones or daylight saving settings for other purposes).

For example if in summer you put 2023-12-01 in a cell (which in reality is 2023-12-01 00:00) and you read it, the Applescript variable will be set to 2023-11-30 23:00. So there start problems with date comparisons, text output, etc. The date correction handler avoids all this.

But as I said in the other post, if your only intent is to copy a date value from one cell to the other without using it for other purposes in the script, you don't need to apply any correction because reading and writing will cancel each other's offset.

You use these two functions of framework "Foundation":

current application's NSTimeZone's systemTimeZone()'s secondsFromGMT() -- for this moment

current application's NSTimeZone's systemTimeZone()'s secondsFromGMTForDate:_date -- for a specified date

Not sure I follow entirely, and I know this wasn't the original focus of this thread, but I know about time zone problems that can occur when using AppleScript to access date-times in Numbers because I go back and forth between time zones.

What is the difference between what you are doing using NSTimeZone, and correcting by adding or subtracting (time to GMT) in plain AppleScript?

(time to GMT) seems to be the current offset (when the script is run). I've had trouble correcting past date-times because of daylight savings time.

Lets' say you have a series of past date-times entered in Numbers when your machine was set to a different time zone (for example, when you were traveling). Some of those date-times were entered when daylight savings time was in effect, some after the switch back to standard time.

Now you find yourself in a different time zone and you want to "correct" the date-times. Does secondsFromGMTForDate: really know about the offset of local time to GMT/UTC at different dates in different locations, accounting for daylight savings time and all that?

SG

AppleScript's (time to GMT) is the same thing than current application's NSTimeZone's systemTimeZone()'s secondsFromGMT(). The new interesting player is secondsFromGMTForDate because it allows you to know what will be the effect of daylight-saving at a certain date.

From the Apple document referenced below:

The system class property returns the time zone currently used by the system, if known. This value is cached once the property is accessed and doesn't reflect any system time zone changes until you call the resetSystemTimeZone() method. The local class property returns an autoupdating proxy object that always returns the current time zone used by the system. You can also set the default class property to make your app run as if it were in a different time zone than the system.

I started getting interested in that because I built a sports schedule in Numbers with games running from September to April. At one point I noticed that all games between November and March were on the wrong day when output in a text file. I was reading the date in a cell (which by default had time 00:00) and was combining it with the time from another cell (which had the date from when I had typed it). So summer to summer was ok, winter to winter was ok, but reading a date from the other season was always producing the wrong result.

By using the date correction handler I was able to cancel all the introduced offsets, no matter at what date I was executing the script. Basically it subtracts the GMT offset at the specified date from the GMT offset today. Same season, result is 0. Different seasons, result is +1h or -1h (here anyway).

I got some inspiration from this MacScripter post and read this Apple documentation for more details. I also got help on AskDifferent about defaultTimeZone.

Start with this and have fun. If you have other questions don't hesitate to ask.