Thursday, October 6, 2022

#1 2022-06-07 12:48:19 pm

seth.seth.bo.beth
Member
Registered: 2022-06-03
Posts: 3

add variable number of rows to a Numbers sheet

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


Filed under: numbers, add rows

Offline

 

#2 2022-06-07 02:33:01 pm

estockly
Member
Registered: 2009-01-03
Posts: 132

Re: add variable number of rows to a Numbers sheet

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

Applescript:


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:

Applescript:


   --    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.

Last edited by estockly (2022-06-07 02:38:30 pm)

Offline

 

#3 2022-06-08 04:36:57 pm

seth.seth.bo.beth
Member
Registered: 2022-06-03
Posts: 3

Re: add variable number of rows to a Numbers sheet

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/1H5Z0bB … sp=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

Offline

 

#4 2022-06-08 05:07:36 pm

estockly
Member
Registered: 2009-01-03
Posts: 132

Re: add variable number of rows to a Numbers sheet

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.

Offline

 

#5 2022-06-08 05:21:33 pm

estockly
Member
Registered: 2009-01-03
Posts: 132

Re: add variable number of rows to a Numbers sheet

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.



Applescript:


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

Last edited by estockly (2022-06-08 06:06:11 pm)

Offline

 

#6 2022-06-08 09:07:11 pm

seth.seth.bo.beth
Member
Registered: 2022-06-03
Posts: 3

Re: add variable number of rows to a Numbers sheet

OMG, it works! Thank you so very much!

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)