Hello everyone :)

I was wondering if you could spend a couple minutes to help me here please. I use iCalendar to organize my job bookings. What, I am trying to do is: combine Calendar information into Numbers spreadsheet to facilitate my tedious invoice process and then send some information from Numbers back to Calendar.

So far, I got a major help from Mr. Ben Waldie, which helped me with script that import my Calendar events into the Numbers Spreadsheet. Here is the script.

set theCalendarName to "Test"
set theStartDate to date (date string of (current date))
set theEndDate to theStartDate - 20 * days

tell application "Calendar"
	set theEvents to every event of calendar theCalendarName where its start date is less than or equal to theStartDate and end date is greater than or equal to theEndDate
end tell

repeat with a from 1 to length of theEvents
	set theEvent to item a of theEvents
	
	tell application "Calendar"
		tell theEvent
			set theTitle to summary
			set theDesc to description
			set theStartDate to start date
			set theEndDate to end date
		end tell
	end tell
	
	set therow to a + 1
	tell application "Numbers"
		tell table 4 of active sheet of front document
			set value of cell 6 of row therow to theTitle
			set value of cell 7 of row therow to theDesc
			set value of cell 8 of row therow to theStartDate
			set value of cell 9 of row therow to theEndDate
			sort by column 8 direction descending
			sort by column 3 direction descending
			add row above second row
		end tell
	end tell
end repeat

The script is great and its doing almost everything that I need, it just need a few tricks to make it perfect.
I managed to do a few minors change to adapted to my needs, but its not perfect.

-To make things easy, here is link to a screenshot of my Spreadsheet table:

https://dl.dropboxusercontent.com/u/8661303/Screenshot%202015-02-18%2017.50.13.png

----Alright here is where I would like your help please:

1st - Previous the original script was importing all event from current date + 60 days in advance, I managed to fix it and its now importing the past 20 days until the current date. But the problem is that, the script re-import all the same events every time it run. Could the script only import new events into the spreadsheet that has not yet been imported without override what is already in the spreadsheet?

2nd - I notice that the script does not import the events in sorting Date order. I managed to add a line “sort by column 8 direction descending” which works until I sort the column 3 “Invoice Number” and then everything goes out place. So, could the the script sort the imported events into “Star Date” column into a descending order like 5 march, 4 march, 3 march…?

----- From Numbers back to Calendar

I would need a new script, this time to send the information from Numbers back to a new Calendar.

I pretty much would like to send back all the same information now imported apart of the EVENT TITLE column, which I would then like to replace it with the EVENT WITH STATUS into a NEW CALENDAR.

Btw, I am using all the latest software version, Yosemite, Numbers 3.5

I appreciate everyone thoughts and help.
Thanks
Ed

Here is a quick and dirty canvas.

 # eventsToInsert is supposed to be a list of the components of the envents to insert
tell application "Numbers" to tell front document
tell active sheet to tell table 4
repeat with i from 1 to count of eventsToInsert
add new row above row 2
--> insert value of events i in row 2
end repeat
end tell
end tell

Yvan KOENIG (VALLAURIS, France) mercredi 18 février 2015 21:14:28

Thanks Yvan,

I much appreciate.
But I am getting an error:

Result:
error “The variable eventsToInsert is not defined.” number -2753 from “eventsToInsert”

Did you really read what I wrote ?
I wrote :

eventsToInsert is supposed to be a list of the components of the envents to insert

It means that it was your duty to put the datas from your Calendar in the list which I named eventsToInsert.

I’m in VALLAURIS, smal French town. I don’t know what is stored in your calendar.

I did your job and edited your script.
Try to run it.

set theCalendarName to "Test"
set theStartDate to date (date string of (current date))
set theEndDate to theStartDate - 20 * days

tell application "Calendar"
	set theEvents to every event of calendar theCalendarName where its start date is less than or equal to theStartDate and end date is greater than or equal to theEndDate
end tell

repeat with a from 1 to length of theEvents
	set theEvent to item a of theEvents
	
	tell application "Calendar"
		tell theEvent
			set theTitle to summary
			set theDesc to description
			set theStartDate to start date
			set theEndDate to end date
		end tell
	end tell
	
	tell application "Numbers"
		tell table 4 of active sheet of front document
			add row above row 2
			tell row 2
				set value of cell 6 to theTitle
				set value of cell 7 to theDesc
				set value of cell 8 to theStartDate
				set value of cell 9 to theEndDate
			end tell
		end tell
	end tell
end repeat
# I moved the sort commands out of the loop so they are executed only once.
tell application "Numbers"
	tell table 4 of active sheet of front document
		sort by column 8 direction descending
		sort by column 3 direction descending
	end tell
end tell

Yvan KOENIG (VALLAURIS, France) jeudi 19 février 2015 22:09:23