Safety Emporium - Lab and Safety Supplies

AppleScripting QuickBooks 2009 Mac

Script Examples and Techniques: Page 4

On the previous page we examined how to use AppleScript to export data and to error trap our scripts. On this page, we look at how to import data from Apple's Mail program, parse it, and bring it into QuickBooks 2009 Mac.

Using Apple Mail to Import Data into QuickBooks 2009 Mac

In this example, we are going to assume that you have an email message in your Inbox from Authorize.net. This same script can be used to import just about any Mail message into any program. Notice that this script does not require Mail be the frontmost application so we don't need to send Mail an activate message.

-- This script retrieves the contents of an email message from Authorize.net in Apple Mail 3.6

-- We will assume that the user has either highlighted the message they want to process in their Inbox or has the message open. You could get fancier and use a loop to import multiple messages.

tell application "Mail"

set theSelectedMessages to selection

if the number of items in theSelectedMessages is 0 then

display dialog "Select or open an Authorize.net receipt." buttons {"Cancel"} with icon 0 with title "Oopsie" default button "Cancel"

return

end if

if the number of items in theSelectedMessages > 1 then

display dialog "Whoa, dude, only 1 message at a time, please." buttons {"Cancel"} with icon 0 with title "Oopsie" default button "Cancel"

return

end if

set messageSelected to item 1 of theSelectedMessages

set emailContent to the content of messageSelected

set messageSubject to the subject of messageSelected

-- Mark the message as read.

set read status of messageSelected to true

end tell

And that's it - boom. We have all our data about that message contained in AppleScript variables. At this point, it might be a good idea to do an error check. We could look at the subject line, the sender, or both. We'll just use the subject line:

if messageSubject does not contain "Merchant Email Receipt" then

display dialog "Please select a Merchant Email Receipt." with icon note buttons {"OK"} with title "Oopsie"

return

end if

Now that we are sure we have the merchant email receipt stored in emailContent, we are ready to parse the data.

Parsing the Data We Read In

It's helpful at this point to see what the data contained in emailContent looks like:

**Please DO NOT REPLY to this message. E-mail support@authorize.net if you have any questions.

========= SECURITY STATEMENT ==========
It is not recommended that you ship product(s) or otherwise grant services relying solely upon this e-mail receipt.

========= GENERAL INFORMATION =========

Merchant : YOUR NAME HERE (123456)
Date/Time : 10-Dec-2010 05:43:39 PM

========= ORDER INFORMATION =========
Invoice : 121212
Description : Widget 10-pack
Amount : 230.00 (USD)
Payment Method : MasterCard
Type : Authorization and Capture

============== RESULTS ==============
Response : This transaction has been approved.
Authorization Code : 99666
Transaction ID : 9123123123
Address Verification : Street Address: Match -- First 5 Digits of Zip: Match

==== CUSTOMER BILLING INFORMATION ===
Customer ID : 
First Name : Support
Last Name : Team
Company : ILPI
Address : PO Box 1003
City : Blackwood
State/Province : NJ
Zip/Postal Code : 08080
Country : United States
Phone : 856-555-1212
Fax : 732-555-1212
E-Mail : ThisWillBounce@ilpi.com

==== CUSTOMER SHIPPING INFORMATION ===
First Name : Support
Last Name : Team
Company : ILPI
Address : PO Box 1003
City : Blackwood
State/Province : NJ
Zip/Postal Code : 08080
Country : United States

======= ADDITIONAL INFORMATION ======
Tax : 
Duty : 
Freight : 
Tax Exempt : 
PO Number : 

========== MERCHANT DEFINED =========
CardType : MasterCard
CustomerSource : Web site
CouponCode : BigDEALZ
PhaseOfMoon : Waxing gibbous

Certain data will always appear on certain line numbers, so that makes things easy for the first few items. However, even simple lines like Last Name might contain more than one word. In the examples below we'll look at a few of the different methods you might use to parse data that appears in constant positions.

-- This script parses the data that is currently residing in the emailContent variable we used in the previous script example

-- These two lines are key. We are converting the paragraphs of our data into a list of items because it is much easier to manipulate list items than it is to manipulate paragraphs. However, we will need a copy of the original paragraphs for later, so we'll copy that into a variable first:

set originalContent to emailContent

set emailContent to the the paragraphs of emailContent as list

-- In this example, we know that the Transaction date will always appear on the 9th line and always have the same format.

set transactionDate to item 9 of emailContent

-- transactionDate now has the value "Date/Time : 10-Dec-2010 05:43:39 PM". Now we need to chop that down. Because Authorize.net zero-pads dates, we can reliably do it with the same character range each time.

set transactionDate to characters 13 through 23 of transactionDate as text

-- transactionDate now has the value "10-Dec-2010" which QB will accept as a valid input in its date fields.

-- Here, we can grab the Transaction number. This will always be in the same spot so we can simply say

set transactionNumber to word 3 of item 21 of emailContent

-- Now let's look at grabbing the Last Name. If it was two words, we could not rely on asking for the last word of item/paragraph 27. Instead, we must look all the way to the end of the line:

set lastName to item 27 of emailContent

set lastName to characters 13 thru (length of lastName) of lastName as text

Once we get to the billing address, we can no longer rely on data being in a given paragraph/item number (because any address line could take up more than one line). From this point on in the parsing, we need to dynamically find the beginning and end locations of our data chunks. As we will do this over and over, we will create a subroutine list_position:

set startHere to list_position("Address : ", emailContent)

set endHere to list_position("City : ", emailContent)

set billingStreet to item startHere of emailContent

set billingStreet to characters 11 thru (length of billingStreet) of billingStreet as text

-- We only need to do this extra bit of work for multi-line data such as Addresses

repeat with x from (startHere + 1) to (endHere - 1)

set billingStreet to billingStreet & return & return & item x of emailContent

end repeat

Here's the list_position subroutine we'll need for that to work. Like all subroutines, it goes after the main script so we can call it over and over:

-- This subroutine returns the position of an item in a list

-- From http://www.apple.com/applescript/sbrt/sbrt-07.html, changing "is" to "contains"

on list_position( this_item, this_list)

repeat with i from 1 to the count of this_list

if item i of this_list contains this_item then return i

end repeat

return 0

end list_position

Billing City, which will always be one line, would be found this way:

set startHere to list_position("City : ", emailContent)

set endHere to list_position("State/Province : ", emailContent)

set billingCity to item startHere of emailContent

set billingCity to characters 8 thru (length of billingCity) of billingCity as text

One final fly in the ointment is that the Authorize.net email does not use unique markers for the billing and shipping information. When we look for "Address : ", we will always find that for Billing, and not for Shipping. This is why we made a copy of our original data - we can isolate the Shipping data and then do our extraction on that. This script will use something called AppleScript text item delimiters to make that easy:

set AppleScript's text item delimiters to "==== CUSTOMER SHIPPING INFORMATION ==="

set shippingInfo to text item 2 of originalContent

set AppleScript's text item delimiters to ""

set shippingInfo to the the paragraphs of shippingInfo as list

Notice that we immediately set the text item delimiters to "" as soon as we were done with them. That's because they will stay at the value we set even if a script has ended (naturally or because of error). You'll find it good script hygiene to reset the AppleScript text item delimiters to "" at the beginnning of every script.

Now that we have the shipping information in its own variable list, you can extract those pieces of data in the same way we did above, substituting shippingInfo for emailContent.

Putting Our Parsed Data Into QuickBooks 2009 Mac

In the example we've been using, we have imported and parsed data from an Authorize.net payment. Let's now put this parsed data into QuickBooks. Specifically, we will Receive Payments in QuickBooks.

You might store the customer information by company name or perhaps using a "Last Name, First Name" construction. We'll assume that you are using the latter in this case. We have already seen how to activate menus and navigate in QB so this script should be easy.

tell application "QuickBooks 2009"

activate

tell application "System Events"

-- Open the Receive Payments window by simulating a menu selection.

click menu item "Receive Payments" of menuof menu bar item "Customers" of menu bar 1 of process "QuickBooks 2009"

end tell

set theCoordinates to the bounds of window "Receive Payments"

tell application "System Events"

-- Click the Deposit To button in the Receive Payments pane.

click radio button "Deposit To" of window "Receive Payments" of process "QuickBooks 2009"

-- And activate the Deposit To pulldown menu

move mouse {(item 1 of theCoordinates) + 300, (item 2 of theCoordinates) + 207}

click mouse

delay 0.1

keystroke "Merchant Account" -- Use the name of whatever account you deposit to

delay 0.5

keystroke tab

delay 0.1

keystroke lastName & "," & space & firstName -- or companyName, depending on how you enter your data.

keystroke tab

delay 0.1

keystroke transactionDate

delay 0.1

keystroke tab

keystroke paymentAmount as text -- We left parsing of this up to you

delay 0.1

keystroke tab

keystroke paymentMethod -- We left parsing of this up to you

keystroke tab

delay 0.1

keystroke tab

keystroke transactionNumber

delay 0.1

keystroke tab

end tell

end tell

All your data is now imported into the Receive Payments window. You will probably want to review it rather than letting the script hit the OK button until you are certain that the script works as you expect. This example includes various delays in it, but all of those may not be necessary, so you can delete them and then test to see if things still work correctly. Also remember that for the mouse commands to work that you need to install the XTools OSAX we dicussed on page one of this tutorial. You might also need to tweak the click location through trial and error.

On the next page we will examine how to export documents and data from QuickBooks 2009 to Apple Mail using AppleScript.

Table of Contents


Safety Emporium - Lab and Safety Supplies

Copyright 2008-2016 by Interactive Learning Paradigms Incorporated. All rights reserved. Reposting of this material is not permitted and constitutes a violation of US and International copyright law.