I’ve not been scripting long, well not in AppleScript anyway. I have been trying to think of a way to get my work rota onto my iPhone without having to go through and create events each time in iCal manually. We do our rota in an excel spreadsheet, with the date on row 1 and my shifts on row 2.
So far, I have nailed the code for creating new events, I know how to set the correct duration of time and I have the IF statements created to make events at different times for different shifts. I have also got the code for OPENING the Excel spreadsheet sorted, and as I’m sure all of you will tell me, these were the easy bits.
So, I’m looking for someone with a good bit of experience that could help me along on this little project. It’s not for financial gain, just simply for myself and my boss who also has an iPhone.
To re-iterate the intention:
Create a script that reads through the row of an excel spreadsheet one by one, correlates row 1 (day (i.e. 24)) and row 3 (my shift for that day(these are in the format of L, E. M, IN)) and creates iCal events from the spreadsheet. There is room for adjustment in the spreadsheet to make things easier if needs be, for example adding a hidden year and/or month row.
I might be back in with some better help here in a while, but have you done any searches here in the mean time? I know there quite a lot of topics about Excel and pulling data from it.
I don’t have an iPhone and I haven’t scripted iCal, but as far as the Excel part, here is some code I use for a script that copies all the data out of a row in a spreadsheet into a list. You can then use the data from that list by referencing item 1 of the list (the first or “A” cell), item 2 (the second or “B” cell), etc.
You will need to modify it for what you are doing but this should give you a good start:
tell application "Microsoft Excel"
activate
open PathToYourSpreadsheetHere
delay 1
--activate object worksheet "The Tab You Want" --Use this if you have multiple tabs in the spreadsheet
delay 1
tell application "System Events"
tell process "Excel"
try
--This will turn off any autofiltering that may be on
pick menu item "Show All" of menu 1 of menu item "Filter" of menu 1 of menu bar item "Data" of menu bar 1
end try
end tell
end tell
delay 1
set ThisOne to (find (range "AG:AG") what PartSelectValue look in values) --I was doing a find for a certain row. You might want to replace this with a repeat loop through all the rows
select contents of ThisOne
set ThisRow to first row index of active cell --This gets the row # after the find. If you are specifying it directly with a repeat loop you won't need this
set AllRulesRow to {}
repeat with z from 1 to 33 --I had 33 colums - change this to however many you have
set TheFinalDataList to TheFinalDataList & (string value of cell z in range ("A" & ThisRow & ":AG" & ThisRow)) --The AG is the letter name of the 33rd column
end if
end repeat
close active workbook without saving
end tell
Model: G5
Browser: Safari 419.3
Operating System: Mac OS X (10.4)