You'd need to work out a one-to-one correspondence between the columns in your spreadsheet and the scriptable fields in a calendar event, unless you were going to add some of the information manually.
Perhaps something like this would work:
Event | Spreadsheet |
Summary | Client |
Location | Project |
Start date | Project Date |
Notes | Agreement Date Cost |
The Cost and Agreement date would be input as two paragraphs in the event’s Notes. You would have to type these carefully as the spreadsheet and script depend on them being in the right place.
All the events would be saved in a calendar called Jobs. Week to view in Calendar would look like this:
An individual event would look like this:
Where would you put the keyword? The way I envisage it working, you wouldn't need one. The script would check the calendar for events that don't contain the word "Logged" in the Notes, log them to the spreadsheet and then add the word "Logged" to the description so they don't get logged again.
The spreadsheet would start off like this
The script would start to look something like this:
tell application "Calendar"
activate
set x to 1 -- to allow for a header row in the spreadsheet
set the_events to (every event of calendar "Jobs" whose description does not contain "Logged")
repeat with each_event in the_events
tell each_event
set {client, project, pdate, adate, cost} to {summary, location, start date, (paragraph 1 of (description as string)), (paragraph 2 of (description as string))}
set description to description & return & "Logged"
end tell
tell application "Numbers"
activate
tell document 1 to tell sheet 1 to tell table 1
tell column 2 to set value of cell (x + 1) to client
tell column 3 to set value of cell (x + 1) to project
tell column 4 to set value of cell (x + 1) to adate
tell column 5 to set value of cell (x + 1) to pdate
tell column 6 to set value of cell (x + 1) to cost
end tell
end tell
set x to x + 1
end repeat
end tell
DO NOT run this script on a live spreadsheet - at the moment it fills from the top and overwrites whatever is there already, there is no error checking, or testing for the existence of the spreadsheet, or whether it’s open or not, or what to do if you mistype the notes...
But as a proof of concept, the spreadsheet ends up like this:
The logged events end up like this:
The next time you run the script, this event won't get added to the spreadsheet because it has the word "Logged" in the notes.
One other thought - if it was me, I wouldn’t run the finalised script from a schedule. What would happen if your desktop was logged out, or the script needed user input and your kids were on it, or it errored halfway through…
I would personally keep human control of when the script is run. Wrap it in an Automator application, open the spreadsheet manually and run the Automator app whenever you wanted to.
Job numbering and detecting the need for extra rows at the bottom of the sheet add an extra layer of complexity which I haven't quite got my head round yet.
