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.
