11 Replies Latest reply: Jan 25, 2013 8:51 PM by claude.B
viapicante Level 1 Level 1 (0 points)

Well I am lost.  Automator has data detection but does not support currency detection.

 

I have quite a bit of receipt emails in my Mail app all containing a price I paid at some point.  They are all organized alone in a mail box as well.  Using Applescript (since automator has no currency detection), I am looking to find/select and extract/copy this price in each email and paste each in it's own cell in a Numbers spreadsheet.  I have too many to do by hand so that is not an option.  I understand the basics of applescript... but I've been looking for hours now for any script that will locate currency or select text on a certain line in a mail message (esp in bulk - numerous emails at once) and I have just about given up.

 

Is this something that can be done or am I just wasting time?


Mac Pro, Mac OS X (10.7.3), 2.66 GHz Dual 12 GB Memory 1.25 TB
  • 1. Re: Automator/AppleScript needed for currency detection
    Pierre L. Level 4 Level 4 (3,860 points)

    The following script should detect any amount of money beginning with the dollar sign. The script is not blazing fast, but it worked flawlessly with my iTunes Store receipts, taking about 6 seconds for 30 receipts.

     

    tell application "Mail"

        set theSelection to the selection -- the selected messages

        set list1 to {} -- all the lists of prices

        repeat with thisMessage in theSelection

            set list2 to {} -- the prices found in thisMessage

            set theContent to content of thisMessage

            tell me to set P to offset of "$" in theContent

            repeat while P is not 0

                set theContent to rich text (P + 1) through -1 of theContent

                set thePrice to word 1 of theContent

                copy thePrice to the end of list2

                tell me to set P to offset of "$" in theContent

            end repeat

            copy list2 to the end of list1

        end repeat

    end tell

    list1

     

    Message was edited by: Pierre L.

  • 2. Re: Automator/AppleScript needed for currency detection
    Pierre L. Level 4 Level 4 (3,860 points)

    You might want to try the script on a small number of emails to begin with.

     

    Message was edited by: Pierre L.

  • 3. Re: Automator/AppleScript needed for currency detection
    viapicante Level 1 Level 1 (0 points)

    Thanks for this it was a big help.  Now I have another small issue.  I am attempting to copy my AppleScript result into Numbers.  I figured out by selecting each number individually lets me successfully paste each number into it's own cell in Numbers.  However there is still too many to select each by hand.  Is their a code where just my prices are selected for copying without the {{""}} that surrounds it?  When I attempt to copy everything into Numbers including the commas, quotes and {}, it pastes everything into only one cell.

  • 4. Re: Automator/AppleScript needed for currency detection
    Pierre L. Level 4 Level 4 (3,860 points)

    If you could precise exactly in which columns and which rows you want the numbers to be pasted, the script could easily be completed in order to automate the process.

  • 5. Re: Automator/AppleScript needed for currency detection
    viapicante Level 1 Level 1 (0 points)

    All prices to be sorted in column 2, row 2 through row 1032.

  • 6. Re: Automator/AppleScript needed for currency detection
    Pierre L. Level 4 Level 4 (3,860 points)

    I've made a few minor changes to the first part of the script. Try this:

     

    tell application "Mail"

        set theSelection to the selection -- the selected messages

        set theList to {} -- all the lists of prices

        repeat with thisMessage in theSelection

            set thisList to {} -- the prices found in thisMessage

            set theContent to content of thisMessage

            tell me to set P to offset of "$" in theContent

            repeat while P is not 0

                set theContent to rich text (P + 1) through -1 of theContent

                copy (word 1 of theContent) as number to the end of thisList

                tell me to set P to offset of "$" in theContent

            end repeat

            copy thisList to the end of theList

        end repeat

    end tell

     

    tell application "Numbers"

        activate

        set theTable to table 1 of sheet 1 of front document

        repeat with j from 1 to count theList -- number of rows

            set thisList to item j of theList

            repeat with k from 1 to count thisList

                set value of cell (k + 1) of row (j + 1) of theTable to item k of thisList

            end repeat

        end repeat

    end tell

  • 7. Re: Automator/AppleScript needed for currency detection
    viapicante Level 1 Level 1 (0 points)

    Worked like a charm.  Thanks again!

  • 8. Re: Automator/AppleScript needed for currency detection
    Pierre L. Level 4 Level 4 (3,860 points)

    My pleasure.

  • 9. Re: Automator/AppleScript needed for currency detection
    claude.B Level 1 Level 1 (0 points)

    Hello Pierre,

     

    I am new to applescripts and have found that you script deos exactly the opposite of what I would like to do I have tried to reverse it but cannot make it work.

     

    Actually I would like to collect diferent cell information from a numbers spreadsheet and paste them in different parts of my email!

     

    for example cell B33 to the subject line and cell A18 to the mail content.

     

    Hope you can help thanks

    Claude

  • 10. Re: Automator/AppleScript needed for currency detection
    Pierre L. Level 4 Level 4 (3,860 points)

    For example:

     

    tell application "Numbers"

        tell table 1 of sheet 1 of document 1

            set B33 to (value of cell "B33") as text

            set A18 to (value of cell "A18") as text

        end tell

    end tell

     

    tell application "Mail"

        set newMessage to make new outgoing message with properties {subject:B33, content:A18 & return & return, visible:true}

    end tell

     

    See “Create New Message.scpt” in the “/Library/Scripts/Mail Scripts” folder

  • 11. Re: Automator/AppleScript needed for currency detection
    claude.B Level 1 Level 1 (0 points)

    thanks Pierre it looks so easy while I was going in circles. Anyway thanks