Inserting data from Applescript into Excel = headache!

viper1953 · January 21, 2013, 5:08pm

Hi guys,

As this is my first post, I’d first like to thank the members of this forum for saving me hours of head scratching during my [somewhat steep] learning curve…

However, I’ve hit a brick wall. I have witten the following script to extract event details from ical and strip out some not needed characters. My next task is to populate an excel spreadsheet - herein lies the difficulty.

It would seem that currently it is inserting the last line only of the result as a complete chunk of text in every cell in the range A2:F (7 in this case).

and would like each line of the result populating as such.

Here is the script:

set {year:y, month:m, day:d} to current date
set str to (d as string) & " " & (m as string) & " " & (y as string)
set today to date str
set tomorrow to today + 60 * 60 * 24

set startOfPeriod to today - (today's day) * days + days
tell startOfPeriod + 32 * days to set endOfPeriod to it - ((its day) - 1) * days - 1

tell application "iCal"
	tell calendar "Sales"
		
		set results to {}
		set theEvents to {}
		
		set AllEvents to every event whose (start date is greater than startOfPeriod) and (start date is less than endOfPeriod)
		
		set eventCount to number of AllEvents
		repeat with anEvent in AllEvents
			
			
			set eventString to {}
			set eventDate to start date of anEvent
			set eventDescription to description of anEvent
			set eventDesc to paragraphs of eventDescription
			set unitPrice to {}
			set Qty to {}
			set Postage to {}
			set totalPaid to {}
			
			repeat with i from 1 to count of eventDesc
				
				set thisPara to item i of eventDesc
				
				if thisPara contains "Item Desc: " then -- gets the item description
					set itemDescription to ((characters 12 thru -1 of thisPara) as string) -- strips the first 13 characters (Item Desc: )
				end if
				
				if thisPara contains "Unit Price: " then -- gets the unit price
					set unitPrice to ((characters 14 thru -1 of thisPara) as string) -- strips the first 15 characters (Unit Price: )
				end if
				
				if thisPara contains "Qty: " then -- gets the quantity
					set Qty to ((characters 7 thru -1 of thisPara) as string) -- strips the first 8 characters (Qty: )
				end if
				
				if thisPara contains "P&P: " then -- gets the P&P
					set Postage to ((characters 7 thru -1 of thisPara) as string) -- strips the first 8 characters (P&P: )
				end if
				
				if thisPara contains "Total Paid: " then -- gets the total paid
					set totalPaid to ((characters 14 thru -1 of thisPara) as string) -- strips the first 15 characters (Total Paid: )
				end if
				
				
				
				set eventString to eventDate & "," & itemDescription & "," & unitPrice & "," & Qty & "," & Postage & "," & totalPaid & linefeed as string
				
				
				
			end repeat
			set results to results & eventString
			
		end repeat
		
	end tell
end tell



tell application "Microsoft Excel"
	
	set {month:currentMonth} to current date
	set currentMonth to currentMonth as string
	
	set sheetName to "Sales - " & currentMonth as string
	
	tell active workbook
		activate object sheet sheetName
	end tell
	
	tell sheet sheetName of active workbook
		activate object cell "A2"
		
		set value of range ("A2:F" & (eventCount + 1)) to eventString
		
	end tell
end tell
return results

Here is the set of results that the script produces:

{"Sunday, 6 January 2013 23:46:32,2.Shotgun Cartridge Fridge Magnets (1 pair) novelty, clay,3.99,1,1.00,4.99
", "Tuesday, 8 January 2013 23:46:32,3.Shotgun Cartridge Fridge Magnets (1 pair) novelty, clay,3.99,1,1.00,4.99
", "Friday, 18 January 2013 23:46:32,4.Shotgun Cartridge Fridge Magnets (1 pair) novelty, clay,3.99,1,1.00,74.99
", "Tuesday, 22 January 2013 23:46:32,5.Shotgun Cartridge Fridge Magnets (1 pair) novelty, clay,3.99,1,1.00,4.99
", "Wednesday, 2 January 2013 23:46:32,1.Shotgun Cartridge Fridge Magnets (1 pair) novelty, clay,53.99,1,1.00,4.99
", "Wednesday, 30 January 2013 23:46:32,6.Shotgun Cartridge Fridge Magnets (1 pair) novelty, clay,3.99,1,1.00,4.99
"}

I know I’m doing something fundamentally wrong - I just dont know what! Please be gentle though - I’ve only been scripting in Applescript for a few days!

edit: as a side note, to reproduce the error, create an event in a calendar of your choice (preferably an empty one) and insert the following into the notes section:

Item Desc: this is the product name

Unit Price: £5.99
Qty: 4
P&P: £1.00
Total Paid: £24.96

Thanks in advance guys!

Mark

Model: iMac
AppleScript: 2.3
Browser: Chrome
Operating System: Mac OS X (10.6)

blend3 · January 22, 2013, 8:20am

Hi Mark,

Well done for getting as far as you did.
I have attached an amended version of your script that hopefully will do what you want. I have tried to stick to your original script as much as possible so that you can understand where I’ve changed things. I’ve written the script so that it processes each row of the spreadsheet 1 at a time. So the data that I’ve extracted from iCal is basically a master list that consists of individual lists, that represent an event.

myRecordList is the master list that consists of the events as a list.

set {year:y, month:m, day:d} to current date
set str to (d as string) & " " & (m as string) & " " & (y as string)
set today to date str
set tomorrow to today + 60 * 60 * 24

set startOfPeriod to today - (today's day) * days + days
tell startOfPeriod + 32 * days to set endOfPeriod to it - ((its day) - 1) * days - 1

tell application "iCal"
	tell calendar "Sales"
		
		set AllEvents to every event whose (start date is greater than startOfPeriod) and (start date is less than endOfPeriod)
		
		set eventCount to number of AllEvents
		set myRecordList to {}
		
		repeat with anEvent in AllEvents
			
			set eventDate to start date of anEvent as string
			set eventDescription to description of anEvent
			
			set itemDescription to ""
			set unitPrice to ""
			set Qty to ""
			set Postage to ""
			set totalPaid to ""
			
			repeat with i from 1 to count of paragraphs of eventDescription
				
				set thisPara to paragraph i of eventDescription
				
				if thisPara contains "Item Desc: " then -- gets the item description
					set itemDescription to ((characters 12 thru -1 of thisPara) as string) -- strips the first 13 characters (Item Desc: )
				end if
				
				if thisPara contains "Unit Price: " then -- gets the unit price
					set unitPrice to ((characters 14 thru -1 of thisPara) as string) -- strips the first 15 characters (Unit Price: )
				end if
				
				if thisPara contains "Qty: " then -- gets the quantity
					set Qty to ((characters 7 thru -1 of thisPara) as string) -- strips the first 8 characters (Qty: )
				end if
				
				if thisPara contains "P&P: " then -- gets the P&P
					set Postage to ((characters 7 thru -1 of thisPara) as string) -- strips the first 8 characters (P&P: )
				end if
				
				if thisPara contains "Total Paid: " then -- gets the total paid
					set totalPaid to ((characters 14 thru -1 of thisPara) as string) -- strips the first 15 characters (Total Paid: )
				end if
				
			end repeat
			
			copy {eventDate, itemDescription, unitPrice, Qty, Postage, totalPaid} to end of myRecordList
			
		end repeat
		
	end tell
end tell

tell application "Microsoft Excel"
	
	set currentMonth to month of (current date) as string
	set sheetName to "Sales - " & currentMonth as string
	
	tell active workbook
		activate object sheet sheetName
	end tell
	
	tell sheet sheetName of active workbook
		
		repeat with i from 1 to count of myRecordList
			set thisRecord to item i of myRecordList
			set lastColumn to count of items of thisRecord
			set firstCell to get address of cell 1 of row i
			set lastCell to get address of cell lastColumn of row i
			set myrange to range (firstCell & ":" & lastCell)
			set value of myrange to thisRecord
		end repeat
		
	end tell
end tell

Hope this helps,

Nik

viper1953 · January 22, 2013, 8:56am

Morning Nik,

That is brilliant! I’ve just compared the scripts and can see where I went wrong.

Just one thing - how would I go about inserting the data into row 2 instead of row 1 ? (row 1 is going to contain column headings).

This was the last major hurdle in a bid to automate my monthly sales etc.

So far I have a rule in Outlook that runs a script when an ‘payment received’ Paypal email comes in. This script extracts specific info, and pops it into the specified calendar in iCal.

This current script will be set to run at the end of every month automagically and thus I can analyse the sales accordingly…

Many thanks for your help though - much appreciated.

Mark

blend3 · January 22, 2013, 9:06am

Hi Mark,

you could do this one of two ways:

1). use the myRecordList to insert the headers for you i.e.:

		set myRecordList to {{"Date", "Description", "Price", "Quantity", "Postage", "Total"}}

2.) Increment the row number by 1 so that the current row is always 1 more than the list item i.e.

		repeat with i from 1 to count of myRecordList
			set thisRecord to item i of myRecordList
			set lastColumn to count of items of thisRecord
			set firstCell to get address of cell 1 of row (i + 1)
			set lastCell to get address of cell lastColumn of row (i + 1)
			set myrange to range (firstCell & ":" & lastCell)
			set value of myrange to thisRecord
		end repeat

I would personally go for the first option is it’s simpler but what is easier for you.

Thanks,
Nik

viper1953 · January 22, 2013, 9:55am

Hi Nik,

Cheers for that. I was experimenting with the i+1 thing but couldn’t get the syntax correct.

For the time being I’m going with that option as I’ll have a workbook already created with sheets ready formatted etc, so its just a case of the script picking the correct sheet and inserting the data.

The idea is so I can generate nicely formatted reports, and run analysis (charts etc) from the same data in the same sheet…

One more thing though - not directly related to the opening post:

how would I put the paragraph directly after the one searched for into a variable ?


set theText to paragraphs		
set searchTerm to "test"
		
		repeat with i from 1 to count of theText
		set thisPara to item i of theText
                       
                        if thisPara contains searchTerm then

			-- need to select the paragraph directly after	

			end if

                end repeat

Apologies for going a little off topic here…

blend3 · January 22, 2013, 10:14am

Will something like this help?

set theText to "Here is seme text
with the search term of test in it.
I am the next paragraph of text.
This is another para."

set searchTerm to "test"

repeat with i from 1 to count of paragraphs of theText
	set thisPara to paragraph i of theText
	
	if thisPara contains searchTerm then
		set nextPara to paragraph (i + 1) of theText
		display dialog nextPara
	end if
	
end repeat

Thanks,
Nik

viper1953 · January 22, 2013, 10:16am

haha, thats it!!

Now I’ve seen that, its such a simple solution…

This scripting malarky is starting to fall into place now!

Once again, many thanks for your help mate. It is much appreciated!

Mark