add variable number of rows to a Numbers sheet

Scripting Forums AppleScript | Mac OS X
#1

Hi,

I’m just getting into applescript, and I could use your help. Most of what is below is something I found online, and it’s very close to doing what I want!

I would like to run an applescript that will extract parts of selected emails, and paste those parts to a Numbers sheet. The below code works to do that, but if I run it again, it overwrites my rows.

<<Please help me add the number of rows equal to the length of “dataLst” so that it will add rows.>>

I know this should be simple, but I can’t get it to do it no matter what I try!

Thanks in advance,
Seth

set dataLst to {{“SenderAddress”, “SenderName”, “Subject”, “Date”, “Time”, “Content”}}

tell application “Mail”
repeat with aMsg in items of (get selection)
tell aMsg
set senderNameAddr to my splitEmail(sender)
set senderAddr to item 2 of senderNameAddr
set senderName to item 1 of senderNameAddr
set msgSubj to subject
set msgDate to date received
set msgTime to time string of msgDate
set msgDate to my dateFormat(date string of msgDate)
set msgContent to content
set msgLst to {senderAddr, senderName, msgSubj, msgDate, msgTime, msgContent}
copy msgLst to dataLst’s end
end tell
end repeat
end tell

tell application “Numbers”
open “Macintosh HD:Users:xxxxxxxxx:desktop:Email_log.numbers”
tell table 1 of active sheet of newDoc
delete column “A” – remove default Header Column
set column count to length of item 1 of dataLst
set row count to (length of dataLst)
set foundBlank to true
if foundBlank then
repeat with i from 1 to length of dataLst
repeat with j from 1 to length of item 1 of dataLst
set value of cell j of row i to item j of item i of dataLst
end repeat
end repeat
end if
end tell
end tell

to dateFormat(aDateString) → yyyy-mm-dd
set {year:y, month:m, day:d} to date aDateString
tell (y * 10000 + m * 100 + d) as string to text 1 thru 4 & “-” & text 5 thru 6 & “-” & text 7 thru 8
end dateFormat

to splitEmail(nameAddress)
set text item delimiters to “<”
tell nameAddress
set theName to text item 1
set theAddress to text 1 thru -2 of text item 2
end tell
return {theName, theAddress}
end splitEmail

#2

Try something like this for the numbers part of your script:


tell application "Numbers"
	activate
	set newDoc to document 1
	--set newDoc to make new document at beginning
	tell table 1 of active sheet of newDoc
		--	delete column "A" -- remove default Header Column
	--	set column count to length of item 1 of dataLst
	--	set row count to (length of dataLst)
		set foundBlank to true
		if foundBlank then
			repeat with i from 1 to length of dataLst
				repeat with j from 1 to length of item 1 of dataLst
					set newRow to make new row at end
					set value of cell j of newRow to item j of item i of dataLst
				end repeat
			end repeat
		end if
	end tell
end tell

I removed the delete header column, because that would keep on deleting that column every time the script is run. You may want to do a separate command to do that the first time it’s run.

Also this version works on the open document. You’ll need to restore your command to open a saved document.

Lemme know if it works. (It works for me)

(Also, wasn’t sure the function of “foundblank” but as far as I can see it’s not needed, but I left it in just in case)

And these two lines are problematic:


	--	set column count to length of item 1 of dataLst
	--	set row count to (length of dataLst)

I’m surprised they didn’t error out.

#3

Hi, I’m back,

Unfortunately this isn’t working for me yet. I tried what you suggested (included below), and have tried it with modifications. For me, it creates one value-filled cell per row making a diagonal cascade of email infos. Here’s a link to a pdf of what it looks like: https://drive.google.com/file/d/1H5Z0bBSf_QZuxxJ4vl8xDcAdoe7oZVeH/view?usp=sharing I also tried starting a new document from scratch, etc., but it always comes with the cascade.

The sheet that I’m reopening is a Numbers doc, without any header rows, and 6 columns.

Thanks for weighing in again if you have the opportunity.

tell application “Numbers”
open “Macintosh HD:Users:xxxxxx:desktop:Email_log.numbers”
tell document 1 of application “Numbers”
set active sheet to sheet 1
end tell
tell table 1 of active sheet of document 1
set foundBlank to true
if foundBlank then
repeat with i from 1 to length of dataLst
repeat with j from 1 to length of item 1 of dataLst
set newRow to make new row at end
set value of cell j of newRow to item j of item i of dataLst
end repeat
end repeat
end if
end tell
end tell

#4

I’m Having a look. A sample of your actual data, just a few lines, might help. I tried before with some dummy data.

Also, when you post copies of your script, try wrapping them in the “AppleScript” tags. There should be an Applescript button in your browser above the message box.

#5

I did see a couple issues.

I had put the “make new row” command in the wrong spot.
The script was looking at item 1 of datalst rather than item i

Try replacing the relevant part of your script with this segment and see if that works.


    tell table 1 of active sheet of newDoc
 		set foundBlank to true
		repeat with thisRecord in dataLst
			set newRow to make new row at end
			repeat with j from 1 to length of thisRecord
				set thisField to item j of thisRecord
				set value of cell j of newRow to thisField
			end repeat
		end repeat
	end tell
#6

OMG, it works! Thank you so very much!