AppleScript or app development help

M

mattrstewart

Registered
i have a task that I need help with developing...as i know nothing of this...not sure if it should be an applescript or an app.

does someone know where i can go to find a person who would be willing to help me make this?
it should be pretty darned simple for someone who knows about scripting
 
Why not write a few lines on what you're trying to do. You'll most likely get suggestions on how to do it, where to look for help, and maybe someone's already done something similar we can point you at.
 
well....basically I have a excel document (.xls) thats used as a template for each product's schedule that our company makes.

the schedule has a bunch of different information, and the project name, and the dates each stage is ready on.

I want to make a script or an app or something....that will read the designated excel file and transfer certain information to iCal.

For example....
lets say i have a schedule "ProjectONE.xls"
"ProjectONE" would be the project name, which is also listed in one of the excel cells.
The schedule then has 15 stages (StageONE, StageTWO, etc), with the dates each stage is ready.
It also has the revision date (date schedule was updated).



I want to have the script read the excel file and make a new iCal calendar as follows:

Calendar Name - ProjectONE (ProjectTWO, ProjectTHREE, etc)

then make an entry for each stage in the schedule as follows:
Event Name - StageONE (StageTWO, StageTHREE, etc)
Location - ProjectONE (ProjectTWO, ProjectTHREE, etc)
Date - (the day each stage is ready)
Notes - Revision Date

*again...each of the above information is in different cells of the excel sheet, so it would just pull the information from the designated cells to auto fill in the calendar entires.

The other thing that would be nice, is if the script / app would first check to see if their is an existing calendar with same ProjectONE name, and if yes, delete calendar and replace with new updated schedule information.


I know this is alot, but it sounds simple to me...even though i have no ability to make this ;)

Also, if its easier...it can do this with Entourage calendar....but I prefer iCal.
 
Your project has two main pieces: get the data out of Excel, and create a calendar with this data.

A quick search on "AppleScript and Excel" returns some example we can draw from. Assume the project name is in cell A1, the stage is in A2, and the target date in A3:

Code: 
tell application "Microsoft Excel"
	set ProjectName to the value of cell "$A$1" as string
	set FirstTargetName to the value of cell "$B$1" as string
	set FirstTargetDate to the value of cell "$C$1" as date
end tell

Now we've got our info, we search Google again for "AppleScript and iCal" and lo and behold the very first link will take you to a page where you can download an example from Apple for doing just what you want! If we strip out the stuff related to the addressbook and so on, we come up with a simple "make an iCal entry" piece of Applescript:

Code: 
tell application "iCal"
	activate
	set theCal to make new calendar at end of calendars with properties {title:ProjectName}
	tell theCal
		set theEvent to make new event at end of events with properties {start date:FirstTargetDate, summary:FirstTargetName, allday event:true, status:confirmed}
	end tell
end tell

Not too complicated at all. Use this to get started. Obviously I'm not about to write the entire program for you, but with these two pieces you should be well on your way.

Good luck,
 
OK....so this is kind of fun...but im in a snag....
I created most of the script based on the information you provided and it looks like this:

Code: 
tell application "Microsoft Excel"
	set ProjectName to the value of cell "$F$4" as string
	set FirstTargetDate to the value of cell "$D$12" as date
	set FirstTargetName to "All Tooling Patterns in HK" as string
	set SecondTargetDate to the value of cell "$E$12" as date
	set SecondTargetName to "Rough Ceramics" as string
	set ThirdTargetDate to the value of cell "$F$12" as date
	set ThirdTargetName to "Tooling Quote Submitted" as string
	set FourthTargetDate to the value of cell "$G$12" as date
	set FourthTargetName to "Casting Ceramics" as string
	set FifthTargetDate to the value of cell "$H$12" as date
	set FifthTargetName to "Approval of Casting Ceramics" as string
	set SixthTargetDate to the value of cell "$I$12" as date
	set SixthTargetName to "Rough Ceramics to Tempe" as string
	set SeventhTargetDate to the value of cell "$J$12" as date
	set SeventhTargetName to "Tooling PO in HK" as string
	set EighthTargetDate to the value of cell "$L$12" as date
	set EighthTargetName to "Paintmaster in HK" as string
	set NinthTargetDate to the value of cell "$M$12" as date
	set NinthTargetName to "Production Quote Submitted" as string
	set TenthTargetDate to the value of cell "$N$12" as date
	set TenthTargetName to "Production Facility Confirmed" as string
	set EleventhTargetDate to the value of cell "$O$12" as date
	set EleventhTargetName to "1st Shot Samples" as string
	set TwelthTargetDate to the value of cell "$P$12" as date
	set TwelthTargetName to "Blister Layout in HK" as string
	set ThirteenthTargetDate to the value of cell "$Q$12" as date
	set ThirteenthTargetName to "1st Deco Samples" as string
	set FourteenthTargetDate to the value of cell "$R$12" as date
	set FourteenthTargetName to "Mock-up Packaging Samples" as string
	set FifteenthTargetDate to the value of cell "$S$12" as date
	set FifteenthTargetName to "EP Samples" as string
	set SixteenthTargetDate to the value of cell "$T$12" as date
	set SixteenthTargetName to "Packaging Artwork in HK" as string
	set SeventeenthTargetDate to the value of cell "$U$12" as date
	set SeventeenthTargetName to "Production PO in HK" as string
	set EighteenthTargetDate to the value of cell "$V$12" as date
	set EighteenthTargetName to "Release Printing" as string
	set NineteenthTargetDate to the value of cell "$W$12" as date
	set NineteenthTargetName to "Release Injection" as string
	set TwentythTargetDate to the value of cell "$X$12" as date
	set TwentythTargetName to "Release Decoration" as string
	set TwentyFirstTargetDate to the value of cell "$Y$12" as date
	set TwentyFirstTargetName to "PP Samples" as string
	set TwentySecondTargetDate to the value of cell "$Z$12" as date
	set TwentySecondTargetName to "1st Shipment" as string
	set TwentyThirdTargetDate to the value of cell "$K$12" as date
	set TwentyThirdTargetName to "Tooling Start" as string
end tell

tell application "iCal"
	activate
	set theCal to make new calendar at end of calendars with properties {title:ProjectName}
	tell theCal
		set theEvent to make new event at end of events with properties {start date:FirstTargetDate, summary:FirstTargetName, notes:"this is a test", allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:SecondTargetDate, summary:SecondTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:ThirdTargetDate, summary:ThirdTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:FourthTargetDate, summary:FourthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:FifthTargetDate, summary:FifthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:SixthTargetDate, summary:SixthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:SeventhTargetDate, summary:SeventhTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:EighthTargetDate, summary:EighthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:NinthTargetDate, summary:NinthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:TenthTargetDate, summary:TenthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:EleventhTargetDate, summary:EleventhTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:TwelthTargetDate, summary:TwelthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:ThirteenthTargetDate, summary:ThirteenthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:FourteenthTargetDate, summary:FourteenthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:FifteenthTargetDate, summary:FifteenthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:SixteenthTargetDate, summary:SixteenthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:SeventeenthTargetDate, summary:SeventeenthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:EighteenthTargetDate, summary:EighteenthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:NineteenthTargetDate, summary:NineteenthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:TwentythTargetDate, summary:TwentythTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:TwentyFirstTargetDate, summary:TwentyFirstTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:TwentySecondTargetDate, summary:TwentySecondTargetName, allday event:true, status:confirmed}
	end tell
	quit
end tell

tell application "iCal"
	launch
end tell


Heres the problem:
I realized a snag....some of the cells that should have dates...will have value as "N/A" or "" (empty cell)......the script stops here and says error" cant create date with entry "N/A"

my thinking is there is 2 routes we can go:
#1 - have a logical statement so that IF value of cell = "" or "N/A", THEN skip entry
OR
#2 - have a script run in beginning that searches all of row 12 from column D to Z, and it should find and replace all values of "" or "N/A" with "0/0/00" (in this option...the entry is still there...but it is made in a far away spot that will pose no harm for me)
 
How about this:

Code: 
if (theDate is not "N/A" and theDate is not "") then
    make new event...... etc. etc.
end if
 
im lost...

i changed my code for the iCal part to be as follows:

Code: 
tell application "iCal"
	activate
	set theCal to make new calendar at end of calendars with properties {title:ProjectName}
	tell theCal
		if (FirstTargetDate is not "N/A" and FirstTargetDate is not "") then
			make new event
		end if
		set theEvent to make new event at end of events with properties {start date:FirstTargetDate, summary:FirstTargetName, allday event:true, status:confirmed}

but it still says "cant make N/A into date
 
What I gave you was not meant to be cut and paste as is, it is meant to show you how to check whether the date was valid, and only if it is to use it to create an event:

Code: 
tell application "iCal"
	activate
	set theCal to make new calendar at end of calendars with properties {title:ProjectName}
	tell theCal
		if (FirstTargetDate is not "N/A" and FirstTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:FirstTargetDate, summary:FirstTargetName, allday event:true, status:confirmed}
                end if
		if (SecondTargetDate is not "N/A" and SecondTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:SecondTargetDate, summary:SecondTargetName, allday event:true, status:confirmed}
                end if
....etc. etc.
 
hehe...sorry...i have no clue what im doing here...really im an idiot when it comes to this stuff


thank you for helping me btw...BUT....your second code does not work either.
it still gives me the same error, and i tried in 2 styles:

your code pasted inbetween my code:
Code: 
tell application "iCal"
	activate
	set theCal to make new calendar at end of calendars with properties {title:ProjectName}
	tell theCal
		if (FirstTargetDate is not "N/A" and FirstTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:FirstTargetDate, summary:FirstTargetName, allday event:true, status:confirmed}
		end if
		set theEvent to make new event at end of events with properties {start date:FirstTargetDate, summary:FirstTargetName, location:ProjectName, allday event:true, status:confirmed}
		if (SecondTargetDate is not "N/A" and SecondTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:SecondTargetDate, summary:SecondTargetName, allday event:true, status:confirmed}
		end if


and:

your code copy and pasted to replace my code:
Code: 
tell application "iCal"
	activate
	set theCal to make new calendar at end of calendars with properties {title:ProjectName}
	tell theCal
		if (FirstTargetDate is not "N/A" and FirstTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:FirstTargetDate, summary:FirstTargetName, allday event:true, status:confirmed}
		end if
		if (SecondTargetDate is not "N/A" and SecondTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:SecondTargetDate, summary:SecondTargetName, allday event:true, status:confirmed}
		end if
		set theEvent to make new event at end of events with properties {start date:ThirdTargetDate, summary:ThirdTargetName, allday event:true, status:confirmed}
 
Does it specifically point you to any particular line of code when you get the error? For example if it's in the "read Excel" section, try remove the "as date" specifier from the line that reads in the date, so that it now looks like:

Code: 
set FirstTargetDate to the value of cell "$D$12"
 
you are a GENIUS!!!!!!!!

it works...i am extremely happy and grateful

now if i can only fine tune the other cosmetic things like:

1. After I the calendar is successfully created, I can only see the new calendar, but no events. After I restart iCal, then the events appear. Is there any way to refresh the calendars or restart iCal?

2. Is there a way to have the script check iCal and see if there is a Calendar already there with the same ProjectName. If yes, then it would delete that calendar and make a new one, if no, then it would continue to create a new calendar.

3. How do I specify the calendar color?

4. How do I specify the information for "Notes" for the event?
 
I think you really should try some Google searches of your own, you'll learn a lot more by reading code examples and just trying things out than if I just keep passing you pieces of code. However, in brief:

(1) I don't see that problem with small numbers of events, but why not end your script by telling iCal to quit, and then telling it to activate again?

(2) Before creating your calendar you could do:

Code: 
delete (the first calendar whose title is ProjectName)

(3) Add a color property when creating your calendar. Search Google on "AppleScript and Colors" to see how it works. Hint: "color:{0,65535,0}" is green. Extra hint: all colors are a combination of Red, Green, Blue.

(4) Add a "description" to your list of properties when you create an event, for example: description:"These are some notes"
 
1 - ive tried ...but it says "iCal got an error: Connection is invalid."

my code is:

Code: 
	     end if
	end tell
	quit
end tell

tell application "iCal"
	activate
end tell

wierd...sometimes it works...and most times it gives me that error
 
I guess the app needs "time to quit" before we can ask it to restart. Try putting a 1-second pause in between the "quit" and "activate":

Code: 
delay 1
If 1 second isn't enough, try 2, 3 and so on...
 
1 is good...its all set now

now i need to figure out 2 things:
1 - print the month view of ALL calendars
2 - have the month view of all calendars to be set as desktop picture
 
You must log in or register to reply here.
Back
Top