Applescript Case Study

Inspyre recently finished an an Applescript development project.

Project Motivation:

We begin with a product catalog containing several thousand individual item entries, in the format of many Adobe InDesign documents. Most catalog entries consist of a small thumbnail picture of a book, followed by some product meta data including pricing information and a 10 digit ISBN number. A price update has been released and the catalog needs to be updated.

Project Objective:

Use the programming/scripting language Applescript to automate the process of updating all catalog entries with the appropriate 13 digit ISBN number, found in the Excel spreadsheet.

Project Details:

The price update is in the from of a Microsoft Excel spreadsheet with thousands of rows, each row representing an updated item in the catalog. Column C in the spreadsheet contains the 10 digit ISNB number for the row-record, another column potentially contains a footnote symbol to follow the updated price (a small cross symbol). If no symbol is present in the column then an invisible asterisk should be inserted for spacing purposes.

Deliverable

(****************************************
** ISBN Price Replacer v.1.3
**
** Copyright: 2007 inspyre LLC
** Date: 3 August 2007
** Updated: 28 August 2007 to look up 10 digit isnb numbers now
*****************************************)

set ISBN_GROSS_LENGTH to 13
set BEFORE_DOLLAR_LENGTH to 33
set ISBN_10_COLUMN to “C”
set DEBUG_MODE to 0 — 1 = no debugging, 1 = high level loops, 2 = low level loops
set USER_FRIENDLY to true
set TEXT_COLOR to “Black”
set SPACER_COLOR to “None”
–set SPACER_COLOR to “C=0 M=100 Y=100 K=0″
set LOG_FILE to true
set LOG_FILE_NAME to “Price Replacer log.txt”

set spacerSym to {}
set newStrings to {}
set oldStrings to {}
set founds to {}
set notFounds to {}

try
—————————————————————————————
— Make a list of all strings that need to be replaced with updated strings
—————————————————————————————

tell application “Adobe InDesign CS2″
— clear find/change preferences
set myOldFindAttributes to properties of find preferences
log myOldFindAttributes
set find preferences to nothing
set change preferences to nothing

set b to search for “^9-^9^9^9^9-^9^9^9^9-^9^t^9^9^9-^9-^9^9^9^9-^9^9^9^9-^9^t$^9.^9^9″ –less than 10
set c to search for “^9-^9^9^9^9-^9^9^9^9-^9^t^9^9^9-^9-^9^9^9^9-^9^9^9^9-^9^t$^9^9.^9^9″ — less than 100
set d to search for “^9-^9^9^9^9-^9^9^9^9-^9^t^9^9^9-^9-^9^9^9^9-^9^9^9^9-^9^t$^9^9^9.^9^9″ — less than 1000
set e to search for “^9-^9^9^9^9-^9^9^9^9-^9^t^9^9^9-^9-^9^9^9^9-^9^9^9^9-^9^t$^9^9^9^9.^9^9″ –less than 10,000

set a to b & c & d & e

if USER_FRIENDLY then my displayDialog(”Number of ISBN’s found in InDeisgn document: ” & length of a)

log my listToString(a, true)
end tell

———————————————————————-
— Construct a new string (with updated pricing) for each
— string that has an isbn in our excel spreadsheet.
———————————————————————-

tell application “Microsoft Excel”
repeat with oldString in a
if DEBUG_MODE > 1 then my displayDialog(”oldString is ” & oldString)
set isbn10Hyphens to characters 1 through ISBN_GROSS_LENGTH of oldString as text
set isbn10 to my stripHyphens(isbn10Hyphens) as text

try
set s to find range (ISBN_10_COLUMN & “:” & ISBN_10_COLUMN) of worksheet “Sheet1″ what isbn10

set colnum to (first column index) of s
set rownum to (first row index) of s

set symbolCol to colnum + 1
set priceCol to colnum - 3
set symbol to “”
set symbol to string value of cell rownum of column symbolCol as text
if symbol = “” then
set spacerSym to spacerSym & true
set symbol to “*”
else
set spacerSym to spacerSym & false
end if

set newPrice to string value of cell rownum of column priceCol as text

set newString to (characters 1 through BEFORE_DOLLAR_LENGTH of oldString as text) & (newPrice as text) & symbol
set newStrings to newStrings & newString
set oldStrings to oldStrings & oldString
copy “found isbn ” & isbn10Hyphens & ” (” & isbn10 & “)” & ¬
” in excel doc row: ” & rownum & “, col: ” & colnum & “, symbol: ” & symbol ¬
& “, spacerSym: ” & end of spacerSym & “, newPrice: ” & newPrice to the end of founds
if DEBUG_MODE > 1 then my displayDialog(”old strings is now: ” & my list2String(oldStrings, true))

if DEBUG_MODE > 1 then my displayDialog(”found isbn ” & isbn10 & ¬
” in excel doc row: ” & rownum & “, col: ” & colnum & “, symbol: ” & symbol ¬
& “, spacerSym: ” & end of spacerSym & “, newPrice: ” & newPrice)

on error errStr number errNum
if errNum = -1708 then –isbn not found in excel
copy isbn10Hyphens to the end of notFounds
set errStr to “We were unable to find ISBN number ” & isbn10Hyphens & ¬
” in the excel spreadsheet, skipping it”
else
error errStr number errNum
end if
if DEBUG_MODE > 1 then my displayDialog(errStr)
log errStr
end try
end repeat
end tell

——————————————–
— Do the replacement in InDesign
——————————————–

tell application “Adobe InDesign CS2″
if length of oldStrings is not equal to length of newStrings then ¬
error “Error, list of old strings to replace and new strings are different length”

repeat with j from 1 to length of newStrings
set old to item j of oldStrings as text
set new to item j of newStrings as text

if new does not contain old then
if DEBUG_MODE > 0 then my displayDialog(”Attempting to replace old string: ” & old & “, with the new string: ” & new)

————————————————————————
–if we have to add an invisible astrisk, lets add a
–whole invisible string, including the astrisk,
–then swap all of that (except the astrisk) out with non-invisible text.
————————————————————————

if item j of spacerSym is true then
— clear find/setup change preferences
set find preferences to nothing
set properties of change preferences to {fill color:SPACER_COLOR}
search for old replacing with new as text
else
— clear find/change preferences
set find preferences to nothing
set properties of change preferences to {fill color:TEXT_COLOR}
search for old replacing with new as text
end if

——————————————————
–now change the non spacer text back to the normal color
—————————————————–
set begNew to characters 1 through ((length of new) - 1) of new
if DEBUG_MODE > 0 then my displayDialog((”begNew: \”" & begNew as text) & “\”")

— clear find preferences and adjust change preferences to default text color
set find preferences to nothing
set properties of change preferences to {fill color:TEXT_COLOR}
–replace the non-astrisk text with itself using the new change preferences
set changedString to search for begNew as text replacing with begNew as text
if DEBUG_MODE > 0 then my displayDialog(”here is the string we just replaced: ” & my listToString(changedString, true))

else if DEBUG_MODE > 0 then
my displayDialog(”Skipping entry which was already up to date: ” & old)
end if
end repeat

if LOG_FILE then
try
set logFile to open for access (((path to desktop) as string) & LOG_FILE_NAME) with write permission
set eof of logFile to 0
write “ISBN NUMBERS FOUND IN EXCEL DOCUMENT & REPLACED IN INDESIGN OR ALREADY UP TO DATE:” & my listToString(founds, true) to logFile starting at eof
write ”

ISBN NUMBERS NOT FOUND IN EXCEL DOCUMENT:” & my listToString(notFounds, true) to logFile starting at eof
close access logFile

if USER_FRIENDLY then my displayDialog(”A log of the ISBN numbers which were found and updated has been written to ” & (((path to desktop) as string) & LOG_FILE_NAME))
on error errStr number errNum
error “there was an error with writing your log file, you can turn this functionality off to avoid this problem by opening the applescript and changing the variable at the top named set LOG_FILE from being \”set LOG_FILE to true\” to be \”false\”…” & errStr & errNum
end try
end if

if USER_FRIENDLY then my displayDialog(”Price replacement finished”)

set find preferences to nothing
set change preferences to nothing

end tell

——————————————————
— Catch any errors we didn’t handle already
——————————————————

on error errStr number errNum
if errNum is equal to -128 then
my displayDialog(”You have canceled the script”)
else
display dialog errStr & errNum
end if
end try

(********************************************
****************Subroutines *******************
********************************************)

–remove nasty hyphens out of an isbn number to get it down to just plain ol’ numbers
on stripHyphens(initISBN)
set ISBN to “” as text
repeat with i in initISBN
–isbn’s can have numbers or X’s in them
if ((ASCII number of i) ≥ 48 and (ASCII number of i) ≤ 57) or (i as text = “X”) then
set ISBN to ISBN & i
end if
end repeat
return ISBN
end stripHyphens

–Provides an easy way to get a string of comma separated values from a list
on listToString(initList, withNewLines)
set listString to “”
repeat with j from 1 to length of initList
if withNewLines then
set listString to listString & ”
” & item j in initList as text
else
set listString to listString & item j in initList as text
end if
end repeat
return listString
end listToString

–This wraps “display dialog” so that all of our dialog boxes show up at
–script editor so that we don’t have to switch windows to the read/click them
on displayDialog(message)
tell application “Script Editor”
display dialog (message)
end tell
end displayDialog