Create a Time Log

Short time lurker and new learner here, I am trying to automate my Numbers sheets a bit to streamline my logging, I’ve build a basic kind of prompt based time log that should fill in a spread sheet in Numbers with a date, time started, time finished, then request a type of variable from a list and then I want it to also calculate the time taken in the last cell, my issue is I can get it to see if the table is present, if not build a new one, then collect variables based on prompts and enter them, my issue comes at the end as it struggles with the time items causing a 1700 error, looking to the bright minds who have done this for longer than me for some possible ideas and solutions:

Script thus far:

set columnCount to 5
set rowCount to 5
set headCount to 1
set footCount to 1
set columnFreezeCount to 0
set theTable to "Log"
set rowPopulated to true
set theDate to short date string of (current date) -- Read current date

tell application "Numbers"
	activate
	delay 2
	tell front document
		tell sheet 1
			if not (exists table 1) then
				set theTable to make new table with properties {name:"Log", column count:columnCount, row count:rowCount, header row count:headCount, footer row count:footCount, header column count:columnFreezeCount}
				set headings to {"Date:", "Time Started: (24 hr HH:MM:SS)", "Time Finished: (24 hr HH:MM:SS)", "Task Completed:", "Time Taken:"}
				repeat with i from 1 to 5
					set value of cell i of row 1 of theTable to item i of headings
				end repeat
				set timeStarted to text returned of (display dialog "Enter the time started: (24 hr HH:MM:SS)" default answer "") -- Start Time
				set timeFinished to text returned of (display dialog "Enter the time finished: (24 hr HH:MM:SS)" default answer "") -- Finish Time
				set taskList to {"var1", "var2", "var3", "var 4", "var5", "var 6"} -- Attribute List
				set selectTask to choose from list taskList with prompt "Choose a task:" default items {item 1 of taskList} -- Task List
				-- (set timeTake to (timeFinished) - (timeStarted))
				if timeStarted is "" or timeFinished is "" or selectTask is false then
					display dialog "Invalid Input. Please provie all required information" buttons {"Ok"} default button "Ok"
				end if
				tell theTable
					if row contains text then
						set theTable to add row below
						set newRow to {theDate, timeStarted, timeFinished, selectTask}
						set end of table to newRow
					else
						set theRow to {theDate, timeStarted, timeFinished, selectTask}
					end if
				end tell
				return {}
			else
				set timeStarted to text returned of (display dialog "Enter the time started: (24 hr HH:MM:SS)" default answer "") as text -- Start Time
				set timeFinished to text returned of (display dialog "Enter the time finished: (24 hr HH:MM:SS)" default answer "") as text -- Finish Time
				set taskList to {"var1", "var2", "var3", "var 4", "var5", "var 6"} -- Attribute List
				set selectTask to choose from list taskList with prompt "Choose a task:" default items {item 1 of taskList} -- Task List
				-- (set timeTake to (timeFinished) - (timeStarted))
				if timeStarted is "" or timeFinished is "" or selectTask is false then
					display dialog "Invalid Input. Please provie all required information" buttons {"Ok"} default button "Ok"
				end if
				tell theTable
					if row contains text then
						set theTable to add row below
						set newRow to {theDate, timeStarted, timeFinished, selectTask}
						set end of table to newRow
					else
						set theRow to {theDate, timeStarted, timeFinished, selectTask}
					end if
				end tell
				return {}
			end if
		end tell
	end tell
end tell

Code text reformatted as Mockman describes below by NG. See here for more formatting info (when the server comes back up!). It also helps to enter or paste the code as plain text.

First thing… format your code.

The use of an applescript tag is no longer valid on the site.

Instead, put three back ticks on the line above the top of your code and below the bottom, eg

```
your code
```

The back ticks should be on separate lines. Then people can open your code in their script editor with a minimum of fuss.

Sorry Ken, didn’t realise that was just trying to make it easy for someone to copy and paste I essentially just copied and pasted it from my script editor into the box there so it should have been formatted as needed

That makes sense but unfortunately it’s not how the site works. It gets automatically reformatted into something difficult to work with when it’s just pasted.

By the way, the back tick is the key to the left of the 1 key (and above the tab) — assuming an English keyboard.

So, a couple of things…

First, remove the outer parentheses from around the two set timeTake… lines. They’re unnecessary and for me at least, generate -2741 errors.

The -1700 errors are caused by attempting to use strings (eg “18:15:12”) to do arithmetic. To convert them into something you can do calculations with requires a different approach.

Mark. I looked at your script and found a few issues:

  • The choose from list dialog returns a list and that needs to be set to a string.
  • The row contains text test doesn’t work for me.
  • I can’t find any point in the script where the cells of the spreadsheet are set to the desired values.

It’s sometimes a good idea to start with a basic script that works and then to add the other stuff (like headings). The following worked on my Sonoma computer:

--this script assumes the Numbers app is active and a blank spreadsheet is open
--for testing run the script multiple times in succession

set theDate to short date string of (current date) -- Read current date
set timeStarted to text returned of (display dialog "Enter the time started: (24 hr HH:MM:SS)" default answer "01:33:33") as text -- Start Time
set timeFinished to text returned of (display dialog "Enter the time finished: (24 hr HH:MM:SS)" default answer "02:33:33") as text -- Finish Time
set taskList to {"var1", "var2", "var3", "var 4", "var5", "var 6"} -- Attribute List
set selectTask to item 1 of (choose from list taskList with prompt "Choose a task:" default items {item 1 of taskList}) -- Task List
set columnList to {"A", "B", "C", "D"}
set valuesList to {theDate, timeStarted, timeFinished, selectTask}

tell application "Numbers"
	activate
	tell table 1 of sheet 1 of document 1
		repeat with rowNumber from 1 to 10 -- get row with blank cells (10 is an arbitray number)
			set theRange to "A" & rowNumber & ":D" & rowNumber
			set theValues to value of every cell in range theRange
			if (text of theValues) is {} then exit repeat
		end repeat
		
		repeat with i from 1 to 4 -- fill cells with values
			set value of cell ((item i of columnList) & rowNumber) to (item i of valuesList)
		end repeat
	end tell
end tell

BTW, I think the repeat loop is required to set the values in each cell, although I’m not an expert on the Numbers app and perhaps this can be done without the repeat loop. Calculating the time difference can be added after the basic script is working as desired.

The thought occurred to me that an alternative is to have one or a few spreadsheets instead of creating them on the fly. The spreadsheet would already contain the headers and would calculate the time differences. The following worked on my Sonoma computer, but error correction is needed to check the times input:

set theSpreadsheet to "Macintosh HD:Users:Robert:Working:Time Log.numbers" -- set to desired value

try -- check if spreadsheet exists
	alias theSpreadsheet
on error
	display dialog "The spreadsheet was not found" buttons {"OK"} cancel button 1 default button 1
end try

set theDate to short date string of (current date) -- the current date
set timeStarted to text returned of (display dialog "Enter the time started:" default answer "")
if timeStarted is "" then error number -128
set timeFinished to text returned of (display dialog "Enter the time Finished:" default answer "")
if timeFinished is "" then error number -128
set taskList to {"Breakfast", "Lunch", "Dinner"}
set theTask to item 1 of (choose from list taskList with prompt "Choose a meal:" default items {item 1 of taskList})
set columnList to {"A", "B", "C", "D"}
set valuesList to {theDate, timeStarted, timeFinished, theTask}

tell application "Numbers"
	activate
	open file theSpreadsheet
	tell table 1 of sheet 1 of document 1
		repeat with rowNumber from 2 to 100 -- get row with blank cells (100 is an arbitray number)
			set theRange to "A" & rowNumber & ":D" & rowNumber
			set theValues to value of every cell in range theRange
			if (text of theValues) is {} then exit repeat
		end repeat
		repeat with i from 1 to 4 -- fill cells with values
			set value of cell ((item i of columnList) & rowNumber) to (item i of valuesList)
		end repeat
	end tell
end tell

My spreadsheet after running the script:

The forumla that calculates the time difference:

Normally the Date class is used to do date and time calculations, although that might be a bit cumbersome in this instance because the user input would have to be converted to a date object. Since, the script only deals with times on one day, some simple calculations might be used instead. The timing result for this script was 0.2 millisecond.

set {TID, text item delimiters} to {text item delimiters, ":"}

set startTime to "9:30"
set finishTime to "12:45"
--> "3 hours 15 minutes"

set startTime to "09:45"
set finishTime to "12:30"
--> "2 hours 45 minutes"

set startTime to "17:55"
set finishTime to "18:45"
--> "0 hours 50 minutes"

set theDuration to getDuration(startTime, finishTime)
set text item delimiters to TID
theDuration

on getDuration(startTime, finishTime)
	set startMinutes to ((text item 1 of startTime as integer) * 60) + (text item 2 of startTime as integer)
	set finishMinutes to ((text item 1 of finishTime as integer) * 60) + (text item 2 of finishTime as integer)
	set durationHours to (finishMinutes - startMinutes) div 60
	set durationMinutes to (finishMinutes - startMinutes) mod 60
	return (durationHours as text) & " hours " & durationMinutes & " minutes"
end getDuration

Hey peavine,

Firstly I want to thank you and mockman for all the help thus far, I’m still learning how it all works and your information has been an absolute goldmine, technically what you have spoken about is already in my pipe line, I’m looking to build out an app for a tracker that has many different spreadsheets within for different departments for tracking my time, I have split this off into its own individual project to work on so I can test and tinker with it without it causing other issues then I plan to integrate it into the app for use.

I am trying to think of all eventualities, hence why I was using the exist statement, because I am human and if it works well for me and I might use this with other people, I am trying to think of eventualities, such as accidentally deleting the table and then having the app rebuild it instead of having to create it manually, that is also why I am trying to include all calculations in the app so that if someone else uses it then they don’t have to know that you need to create a table that has x amount of columns, y amount of rows and has the following formatting of cells and is centered (personal preference there), I will also add that I did find a hitch, if there are more options than rows the table does error out, so I am curious if there would need to be something at the end like:

tell table 1 of sheet 1 of document 1
		repeat with rowNumber from 1 to 10 -- get row with blank cells (10 is an arbitray number)
			set theRange to "A" & rowNumber & ":D" & rowNumber
			set theValues to value of every cell in range theRange
			if (text of theValues) is {} then exit repeat
               else
                      add row below to theTable
             end repeat

repeat with i from 1 to 4 -- fill cells with values
			set value of cell ((item i of columnList) & rowNumber) to (item i of valuesList)
		end repeat
	end tell
end tell

Mark. I’m glad your project is progressing along well. I figured you had specific requirements, and that’s why I included several scripting suggestions.

The following sample code adds the required row if it doesn’t exist, and this works on my Sonoma computer. It uses a somewhat different (and hopefully more reliable) approach to determine if a row is empty.

set theDate to short date string of (current date) -- Read current date
set timeStarted to text returned of (display dialog "Enter the time started: (24 hr HH:MM)" default answer "")
set timeFinished to text returned of (display dialog "Enter the time finished: (24 hr HH:MM)" default answer "")
set taskList to {"Breakfast", "Lunch", "Dinner"} -- Attribute List
set selectedTask to item 1 of (choose from list taskList with prompt "Choose a task:" default items {item 1 of taskList})
set columnList to {"A", "B", "C", "D"}
set valuesList to {theDate, timeStarted, timeFinished, selectedTask}

tell application "Numbers"
	activate
	tell table 1 of sheet 1 of document 1
		repeat with rowNumber from 1 to 100 -- get row with blank cell in column A (100 is an arbitrary number)
			try
				set cellValue to value of cell ("A" & rowNumber)
				if cellValue is (missing value) then exit repeat
			on error -- the row doesn't exist
				add row below last row
				exit repeat
			end try
		end repeat
		repeat with i from 1 to (count columnList) -- fill cells with values
			set value of cell ((item i of columnList) & rowNumber) to (item i of valuesList)
		end repeat
	end tell
end tell

FWIW, a version of my above script that adds headers and calculates time duration. This worked on my Sonoma computer.

set theDate to short date string of (current date)
set timeStarted to text returned of (display dialog "Enter the time started: (24 hr HH:MM)" default answer "")
set timeFinished to text returned of (display dialog "Enter the time finished: (24 hr HH:MM)" default answer "")
set timeDuration to getDuration(timeStarted, timeFinished)
set taskList to {"Breakfast", "Lunch", "Dinner"}
set selectedTask to item 1 of (choose from list taskList with prompt "Choose a meal:" default items {item 1 of taskList})
set columnList to {"A", "B", "C", "D", "E"}
set headerList to {"Current Date", "Time Started", "Time Finished", "Time Duration", "Task"}
set valuesList to {theDate, timeStarted, timeFinished, timeDuration, selectedTask}

tell application "Numbers"
	activate
	tell table 1 of sheet 1 of document 1
		repeat with rowNumber from 1 to 100 -- get row with blank cell in column A (100 is an arbitrary number)
			try
				set cellValue to value of cell ("A" & rowNumber)
				if cellValue is (missing value) then exit repeat
			on error -- the row doesn't exist
				add row below last row
				exit repeat
			end try
		end repeat
		if rowNumber is 1 then -- add header
			repeat with i from 1 to (count columnList)
				set value of cell ((item i of columnList) & rowNumber) to (item i of headerList)
			end repeat
			set rowNumber to 2
			-- possibly insert handler here to format columns to desired vallues (e.g. alignment)
		end if
		repeat with i from 1 to (count columnList) -- fill cells with values
			set value of cell ((item i of columnList) & rowNumber) to (item i of valuesList)
		end repeat
	end tell
end tell

on getDuration(startTime, finishTime)
	set {TID, text item delimiters} to {text item delimiters, ":"}
	set startMinutes to ((text item 1 of startTime as integer) * 60) + (text item 2 of startTime as integer)
	set finishMinutes to ((text item 1 of finishTime as integer) * 60) + (text item 2 of finishTime as integer)
	set durationHours to (finishMinutes - startMinutes) div 60
	set durationMinutes to (finishMinutes - startMinutes) mod 60
	set text item delimiters to TID
	return (durationHours as text) & " hours " & durationMinutes & " minutes"
end getDuration

Well peavine, I must extend my courtesies your way, that has worked, I’m now working on integrating it in the applet, now the fun begins lol, I appreciate all the guidance and the support more than you can imagine

1 Like

Hey peavine,

Got an addendum to add, I have been tinkering with it for the last few days and its working wonderfully, now I’m realizing one mishap I may run into, that being the size of the table if I have to record myself for say 6 months then its gonna get huge, so my added question is gonna be around possibly moving the table from one sheet to another and then changing the title on the table (Default set to Log), although I may change this to the date if I can get it to move successfully, currently working with code:

tell application "Numbers"
     activate
     
    set today to current date

    tell table 1 of sheet 3 of document 1 -- This is where the Time Log resides with other needed sheets for logging etc
         set tableDate to value of cell "A2" -- Date of the table or as it currently stands the list of dates I have been building whilst testing
         if tableDate is not today then
            move table 1 to sheet 4
        end if
     end tell
  end tell

My issue with this is it cannot seem to locate the sheets, I’ve tried with putting the name in a variable "sheetHistory for Time Log History for example and tableName for “Log”, as well as trying to use table 1 of sheet 3 of document 1 (Time Log sheet) to sheet 4 of document 1, as ideally I’ll have it just move the entire table then create a new table to replace, hence why wondering if I can put the date into the Table name along the lines of Monday, June 10th 2024 as an example and then having another sheet that has all of the history tables available.

Mark. The current date command returns a date object that includes the current time accurate to one second. So, getting the current date and comparing that to a date contained in the spreadsheet is never going to match. There are several ways to resolve this, and one is to use the date string property of the date class. The following worked on my computer:

--cell A2 of  table 1 of sheet 3 of document 1 is formatted as a string and contains today's date string
set today to date string of (current date)
tell application "Numbers"
	activate
	tell table 1 of sheet 3 of document 1
		set tableDate to value of cell "A2"
		if tableDate is today then display dialog "The dates match"
	end tell
end tell

I looked at the Number’s dictionary, and I don’t believe it’s possible to move a table. If I’m wrong about this, hopefully another forum member will correct me.

Setting the title of a table to a date string can be done as follows. This worked on my computer.

set currentDate to date string of (current date)
tell application "Numbers"
	activate
	tell table 1 of sheet 3 of document 1
		set name to currentDate
	end tell
end tell
1 Like

Hey Peavine,

I tried that and it worked, thank you again for the response, I’ve been trawling through the library myself and that is how I found it myself, it is under the Standard suite of commands not under Numbers Directly but it attaches to Numbers as well, so technically it should work but it doesn’t want to play nice.

Slightly off topic here but you might like https://timingapp.com. An applescriptable task timer that tracks app usage, documents, sites, calls, practically everything! I love it.

Mark. This is an interesting question, and I’m not sure of the answer. The Numbers dictionary states that the move command moves an object to a location. This might lead one to believe that a table could be moved to a sheet. However, I tried that (as you have) and couldn’t get it to work:

tell application "Numbers"
	activate
	move table 1 of sheet 1 of document 1 to sheet 2 of document 1
end tell --> Numbers got an error: AppleEvent handler failed.

An alternative that I tried was to make a new table and to utilize the with data option. The old table could then be deleted. The table was created but it didn’t contain any data:

tell application "Numbers"
	activate
	tell sheet 1 of document 1
		set theTable to make new table with data "some data" with properties {column count:4, row count:4}
	end tell
end tell

A third option that does work is to select the all of the source table, copy it to the clipboard, and then paste it into a new sheet. This would be done with GUI scripting, which I try to avoid if possible.

You have workflow requirements that have to be met, but I would be inclined to create a new sheet each month and to set the name of the new sheet to the current month and year. A simple example:

set {year:theYear, month:theMonth} to (current date)
set currentMonth to (theMonth as text) & space & theYear

tell application "Numbers"
	activate
	tell document 1
		set sheetNames to name of every sheet
		if currentMonth is not in sheetNames then
			set theSheet to make new sheet with properties {name:currentMonth}
			delete every table of theSheet --this and following if desired
			tell theSheet to make new table with properties {column count:4, row count:4}
		end if
	end tell
end tell
1 Like

FWIW, when I try to duplicate the table, I get a slightly more informative error.

error “Numbers got an error: Tables can not be copied.” number -1717

Error -1717 indicates:

Unable to find a matching event or coercion handler.

If you look specifically at table in the dictionary, you should see that the only commands that it should respond to are sort and transpose.

As an aside, a table is also an element in an iwork item( and iwork container), the former of which can be an element of a sheet. I wonder if one could place the table inside one and then move the iwork item and have the table tag along. Not sure how that might be implemented though.

1 Like