I would like to create an Applescript for the following:
My boss sends to all of us a calendar with our duties for the month. It is a simple Excel page with the dates from 1 to 30-31 on the first column and on the first row what duty we have to do. Then our name shows up on a cell for a given date for a given duty. This is a general calendar for all of us.
Up till now I have been manually inserting these in my iCal/Calendar app which is kind of tedious. I was wondering if there would be a script in which it would find my name in the cell, and create an iCal event with the duty I have to perform for that given date (no need for minutes and hours, just the duty)
Thank you
Model: Macbook 13"
AppleScript: 2.6.1 (152.1)
Browser: Safari 537.36
Operating System: Mac OS X (10.8)
To be clear, the Excel spreadsheet you receive has in column 1 the dates for the month (say 1 to 31) going down in rows. Column 2 has the duty to be performed for each day going down in rows and column 3 has the names of those responsible for the duty going down in rows.
Is this accurate? Any given row contains the information for that date?
If this is the case, here is a strategy.
User selects all the rows for any of the three columns or all the rows for all the columns–doesn’t matter. This handles the problem of months with varying numbers of days.
Return the range selected, extract the number of rows (days) from the range.
Get from user the month and user name.
Repeat for each row:
a. Check for user name in the whose assigned cell. If name is found then:
(1) Get date from first cell in row and task from second cell in row.
(2) Create all day event for the date with task as the event in iCal.
1 Joe Blow Mark Smart Sarah Sweet Ray Ban
2 Sarah Sweet Ray Ban Joe Blow Mark Smart
3 Ray Ban Joe Blow Mark Smart Sarah Sweet
4 Mark Smart Sarah Sweet Ray Ban Joe Blow
While your table had the same people in the same columns, this data allows for the possibility of people being in different columns or not even being there at all.
To use the routine have iCal running:
(1) Open the Excel workbook to the worksheet with the jobs table.
(2) Select the cell with the month in it.
(3) While holding down the command key, select the entire range of the jobs table from the blank upper left cell to the last lower right cell. [You have just selected two ranges: the one with the date and the one with the table.]
(4) Run the routine.
You will be asked to enter the name of the person for whom to create the iCal events.
Note that you must change the first line of the Applescript by replacing “Home” with the name of your calendar in quotes.
Here’s the Applescript. Copy into your Applescript Editor.
property theCalendar : "Home" as text --change "Home" to the calendar desired for events
-- Get name
set theName to my getName()
-- Get ranges from Excel
set theRanges to getRanges()
set monthRange to item 1 of theRanges
set tableRange to item 2 of theRanges
-- Extract month
tell application "Microsoft Excel" to set theMonth to the value of cell 1 of monthRange
-- Get year
set theYear to getYear(theMonth)
-- Process data table
tell application "Microsoft Excel"
set rowN to count of rows of tableRange
set colN to count of columns of tableRange
set theJobs to value of (row 1 of tableRange)
set theJob to item 2 of item 1 of theJobs
repeat with i from 2 to rowN --process each date row in table
set theRowValues to value of (row i of tableRange)
set theDay to item 1 of item 1 of theRowValues as integer
repeat with j from 2 to colN --process each person in row
set thePerson to item j of item 1 of theRowValues
if thePerson = theName then
set theJob to item j of item 1 of theJobs
my createEvent(theMonth, theDay, theYear, theJob)
end if
end repeat
end repeat
end tell
-- Subroutines
on createEvent(theMonth, theDay, theYear, theJob)
set theDate to date (theMonth & " " & theDay & ", " & theYear)
tell application "Calendar"
tell calendar theCalendar
make new event at end with properties {summary:theJob, start date:theDate, allday event:true}
end tell
end tell
end createEvent
on getRanges()
tell application "Microsoft Excel"
set multRange to (get areas of selection)
return multRange
end tell
end getRanges
on getYear(theMonth)
set theYear to year of (current date)
if theMonth = "January" then
set theResult to display dialog "For the month of January, of which year?" buttons {"Cancel", theYear, (theYear + 1)} with title "Year Query" default button 3
set theYear to button returned of theResult as text
end if
return theYear
end getYear
on getName()
set theResult to display dialog "What name to create events in iCal for? Make certain that the name matches exactly that used in the data table." default answer "" with title "Name Query"
set theName to text returned of theResult
return theName
end getName
It has been a while since I’ve used office, but isn’t there a way to get the used cells? I think it used to return the last used cell. That way, you wouldn’t need to get selected cells assuming that the first cell is always the same. Maybe they changed things?
I don’t know if I am doing this right but I am getting a syntax error here: (at the number “1”)
tell application “Microsoft Excel” to set theMonth to the value of cell 1 of monthRange
I’m at a different computer today so I copied the data and pasted into Excel by using Word as a go between to change multiple spaces to a tab. I then copied the code into this computer’s Applescript Editor. Ran perfectly.
My guess that what is wrong is that you are not holding down the command key when you select the data table range. Follow this procedure:
Click on the cell with the month.
Press down on the command key and continue to hold it down.
Select the range containing the entire data table starting with the top-left most blank cell and dragging down to the bottom-right most cell. In this case, the bottom right cell has “Joe Blow” in it.
Release the command key.
Run the routine.
Make certain that you have a calendar named “Home” (no double quotes) in your iCal.
This hold the command key strategy is how Excel can select different ranges. The routine requires two to be selected: (1) the month range and (2) the jobs/people table.
I have been trying to work out an error that it gives me when I run this: the date format. I use day/month/year as my date format. How can I change that in the sricpt.
Hate to be such a pain but I have tried to work this out myself to no avail.
Well, that is indeed the problem! I thought that there was only the day of the month in the cells of the date column as that was the way it was presented in the first sample table that was provided. The code can be revised to use a real date in each row of the first column. Is that what’s needed?
property theCalendar : "Home" as text --change "Home" to the calendar desired for events
-- Get name
set theName to my getName()
-- Get ranges from Excel
set theRanges to getRanges()
set tableRange to item 1 of theRanges
-- Process data table
tell application "Microsoft Excel"
set rowN to count of rows of tableRange
set colN to count of columns of tableRange
set theJobs to value of (row 1 of tableRange)
set theJob to item 2 of item 1 of theJobs
repeat with i from 2 to rowN --process each date row in table
set theRowValues to value of (row i of tableRange)
set theDate to item 1 of item 1 of theRowValues
repeat with j from 2 to colN --process each person in row
set thePerson to item j of item 1 of theRowValues
if thePerson = theName then
set theJob to item j of item 1 of theJobs
my createEvent(theDate, theJob)
end if
end repeat
end repeat
end tell
-- Subroutines
on createEvent(theDate, theJob)
tell application "Calendar"
tell calendar theCalendar
make new event at end with properties {summary:theJob, start date:theDate, allday event:true}
end tell
end tell
end createEvent
on getRanges()
tell application "Microsoft Excel"
set multRange to (get areas of selection)
return multRange
end tell
end getRanges
on getName()
set theResult to display dialog "What name to create events in iCal for? Make certain that the name matches exactly that used in the data table." default answer "" with title "Name Query"
set theName to text returned of theResult
return theName
end getName
Call with only one range, the complete data table in the form of
Walk Plank Order Lunch Dig Ditch Look Good
9/1/14 Joe Blow Mark Smart Sarah Sweet Ray Ban
9/2/14 Sarah Sweet Ray Ban Joe Blow Mark Smart
9/3/14 Ray Ban Joe Blow Mark Smart Sarah Sweet
9/4/14 Mark Smart Sarah Sweet Ray Ban Joe Blow
The day of the month is the way it was in the origianl table sample. The problem is that when I run the script I get the following error: “Fecha y hora no válidas date OCTUBRE 1, 2014 of «script».”
I assumed that the problem was the date format since I have my computer set for: “day/month/year”.
On the new script you sent me I get an error in this line: " set theJob to item 2 of item 1 of theJobs". It says: “Cannot get item 2 of “O”.”
The problem appears to be what type of data are the values in the column of days of the month. Could you look at the cell format for one of the days and see just what it really is?
Could the problem be here in this line: “my createEvent(theMonth, theDay, theYear, theJob)” or this one: "set theDate to date (theMonth & " " & theDay & “, " & theYear)”. Since the date in my macbook is setup to: DD/MM/YY could this be the bug (glitch?)?
I have tried switching theDay and theMonth but still get an error.
