I say “waking” because it’s not really asleep, but the display is.
Anywho, I’ve an awesome budget workbook in Excel (functioning as my place to implement new VBA ideas with a degree of practical return on time spent), and the next enhancement was to be able to send myself an email when out and about and have mail run an applescript to open the workbook and place the new line item into it.
It works, and I overcame the problem not being able to get the display to come back on by using a tool called “SleepDisplay”. However, the script will only wake the display, enter the password, and execute if the screen’s been asleep for a short time, less than 30 minutes or so (though I’ve yet to exhaustively pinpoint exactly how long).
If its within this time frame, then everything works perfectly, but if not, then everything works except the script itself, meaning that Mail receives the message the other actions of the rule are executed, but the applescript fails to run.
Upon waking the display and logging back in manually, I can see that excel has been activated (icon appears on the dock), but it doesn’t do anything until I click it’s icon. After clicking excel’s icon, it immediately opens the budget workbook, inserts the line item, and does everything else it was supposed to do.
So what’s my problem? Is there some deeper level of sleeping the display without actually putting the computer to sleep? I know the computer is still awake because I can send it emails and it will beep at me and execute any other rules that are completely within Mail (ie no applescript), including auto-responding with a message. Given the hanging excel instance when I log back into the computer, is the problem to due with calling excel? The display never comes on when the ~30 minutes have been passed, so is there something wrong with the SleepDisplay tool?
Any help is appreciated.
Thanks,
Tim
The full script follows, including subroutines, so if you’d like to test this, just change the pointer to the budget file. Directly below is a link to a blank copy of the glorious budget sheet itself:
Edit This workbook is macro-enabled, but it’s completely safe. Scan it before opening if you’d like to be (more) safe.
https://www.dropbox.com/s/17elck604t3nyvz/budget%20template.xlsm
Use the following formatting in the trigger email to conform with the script:
Budget item:
(day, ## for day of current month, leave blank if for same day;
w, “who” the line item is for, enter 1,2 or 3, points towards names specified in the budget workbook;
t, type, enter i or e, stands for income and expense;
d1, primary description/category, must match category specified in budget workbook;
d2, secondary description, any value or string;
$, number only for amount)
AVOID HYPHENS WHEN ENTERING INFO, AS HYPHENS ARE USED AS THE DELIMITER
day-
w-
t-
d1-
d2-
$-
EXAMPLE:
day-15
w-1
t-e
d1-shopping
d2-test
$-100
some notes on the budget workbook:
- used for two people sharing a single account or monetary supply
- data validation used so only income/expense categories specified in the “year” sheet can be entered
- conditional formatting color codes each line item based on income/expense of person 1, person 2, or a shared income/expense
- dynamic charts on “year” sheet get data from the hidden “charts” sheet
- VBA userforms for entering in all line items, common income/expenses, and sorting line items based on several criteria
Finally, here’s the script itself. I’ve only commented it until the end of the part that wakes the display and logs in.
(*
Tim Wilson
3/19/2013
Script to add line items to budget worksheet from incoming message in Mail
*)
-- first two lines are blocks to react to the rule in Mail
using terms from application "Mail"
on perform mail action with messages theSelectedMessages
-- checks if system sounds are muted and mutes them if not, unmuting when finished
set isMuted to output muted of (get volume settings)
set changeMute to false
if isMuted is false then
set changeMute to true
set volume with output muted
end if
-- check to see if display is asleep, wakes display if asleep and resleeps it when finished
set isSleeping to false
set sleeping to 1
set awake to 4
-- shell script that returns sleep status of display
set display_sleep_state to parseLine(do shell script "ioreg -n IODisplayWrangler |grep -i IOPowerManagement", "CurrentPowerState")
if item 2 of display_sleep_state contains sleeping then
set isSleeping to true
set passKey to "my_super_secret_password" -- not really my password
-- shell script calling the "SleepDisplay" tool
do shell script "/usr/local/bin/SleepDisplay --wake"
tell application "System Events"
delay 5
-- entering my password into the login window
repeat with i from 1 to count of passKey
keystroke item i of passKey
delay 0.01
end repeat
key code 36 --return key
delay 1
end tell
else if item 2 of display_sleep_state contains awake then -- unused, but I keep it here anyways :)
end if
delay 0.5
set dateTime to current date
set currentMonth to month of dateTime as number
set currentYear to year of dateTime as number
set currentDay to day of dateTime as number
set budgetFile to "HDD:Users:Haiiro:Dropbox:" & currentYear & " Budget.xlsm" as alias
if currentMonth is 1 then
set sheetName to "Jan"
set numOfDays to 31
else if currentMonth is 2 then
set sheetName to "Feb"
if currentYear mod 4 = 0 then
set numOfDays to 29
else
set numOfDays to 28
end if
else if currentMonth is 3 then
set sheetName to "Mar"
set numOfDays to 31
else if currentMonth is 4 then
set sheetName to "Apr"
set numOfDays to 30
else if currentMonth is 5 then
set sheetName to "May"
set numOfDays to 31
else if currentMonth is 6 then
set sheetName to "Jun"
set numOfDays to 30
else if currentMonth is 7 then
set sheetName to "Jul"
set numOfDays to 31
else if currentMonth is 8 then
set sheetName to "Aug"
set numOfDays to 31
else if currentMonth is 9 then
set sheetName to "Sep"
set numOfDays to 30
else if currentMonth is 10 then
set sheetName to "Oct"
set numOfDays to 31
else if currentMonth is 11 then
set sheetName to "Nov"
set numOfDays to 30
else if currentMonth is 12 then
set sheetName to "Dec"
set numOfDays to 31
end if
repeat with currentMessage in theSelectedMessages
set fileText to content of currentMessage
set itemList to {}
set i to 0
set fullItem to 5
set tempItem to {itemDay:"", itemWho:"", itemType:"", itemDesc:"", itemDesc2:"", itemAmount:""}
set err to false
repeat with currentLine in paragraphs of fileText
set lineInfo to parseLine(currentLine, "-")
if (count of lineInfo) is not less than 2 then
if item 1 of lineInfo contains "Day" then
set itemDay of tempItem to item 2 of lineInfo
set i to i + 1
set fullItem to 6
else if item 1 of lineInfo contains "w" then
set itemWho of tempItem to item 2 of lineInfo as number
set i to i + 1
else if item 1 of lineInfo contains "t" then
set itemType of tempItem to item 2 of lineInfo
set i to i + 1
else if item 1 of lineInfo contains "d1" then
set itemDesc of tempItem to titleCase(item 2 of lineInfo)
set i to i + 1
else if item 1 of lineInfo contains "d2" then
set itemDesc2 of tempItem to titleCase(item 2 of lineInfo)
set i to i + 1
else if item 1 of lineInfo contains "$" then
set itemAmount of tempItem to item 2 of lineInfo
set i to i + 1
end if
end if
if i ≥ fullItem then
if itemType of tempItem is "i" then
set itemType of tempItem to "Income"
else if itemType of tempItem is "e" then
set itemType of tempItem to "Expense"
end if
if itemDay of tempItem is not "" and itemDay of tempItem is greater than numOfDays then
set errorMsg to "The specified day of an item is incorrect. Must be no greater than " & numOfDays & ".
No items were added."
set err to true
exit repeat
end if
if itemDay of tempItem is not "" then
set itemDay of tempItem to (currentMonth & "/" & itemDay of tempItem & "/" & currentYear) as string
end if
set end of itemList to tempItem
set tempItem to {itemDay:"", itemWho:"", itemType:"", itemDesc:"", itemDesc2:"", itemAmount:""}
set i to 0
set fullItem to 5
end if
end repeat
if err is false then
set returnValue to insertItems(budgetFile, sheetName, itemList)
if item 1 of returnValue is 1 then
set errorMsg to "The current month is full, so no more items can be entered. Only the first " & item 2 of returnValue & " item(s) were added."
set err to true
else if item 1 of returnValue is 2 then
set errorMsg to "The description of item " & (item 2 of returnValue) + 1 & " didn't match with set descriptions.
The first " & item 2 of returnValue & " item(s) were added.
Expenses:
"
repeat with desc in (item 3 of returnValue)
set errorMsg to errorMsg & " " & desc & "
"
end repeat
set errorMsg to errorMsg & "
Income:
"
repeat with desc in (item 4 of returnValue)
set errorMsg to errorMsg & " " & desc & "
"
end repeat
set err to true
else if item 1 of returnValue is 3 then
set errorMsg to "The person of item " & (item 2 of returnValue) + 1 & " is incorrect. Must be 1, 2, or 3.
The first " & item 2 of returnValue & " item(s) were added."
set err to true
end if
end if
if err is true then
errorMessage(errorMsg, fileText)
else
successMessage(item 2 of returnValue, fileText)
end if
delete currentMessage
end repeat
if isSleeping is true then do shell script "/usr/local/bin/SleepDisplay"
delay 4
if changeMute is true then set volume without output muted
end perform mail action with messages
end using terms from
on errorMessage(errorMsg, fileText)
set errorMsg to errorMsg & "
Original data:
" & fileText
if my appisrunning("Mail") then
set dontQuit to true
else
set dontQuit to false
end if
tell application "Mail"
set theMessage to make new outgoing message with properties {visible:true, subject:"Budget - Add Item Error", content:errorMsg}
tell theMessage
make new recipient at end of to recipients with properties {name:"Tim Wilson", address:"twilsonco@gmail.com"}
end tell
set sent to send theMessage
if dontQuit is false then
if sent is true then quit
end if
end tell
end errorMessage
on successMessage(itemList, fileText)
if my appisrunning("Mail") then
set dontQuit to true
else
set dontQuit to false
end if
set dateTime to current date
set currentMonth to month of dateTime as number
set currentYear to year of dateTime as number
set currentDay to day of dateTime as number
set messageText to "The following item(s) have been added to the budget workbook:
"
set i to 1
repeat with currentItem in itemList
if itemDay of currentItem is "" then
set itemDate to (currentMonth & "/" & currentDay & "/" & currentYear) as string
else
set itemDate to itemDay of currentItem
end if
set messageText to messageText & "Date: " & itemDate & "
Person: " & itemWho of currentItem & "
Type: " & itemType of currentItem & "
Description: " & itemDesc of currentItem & "
Secondary Description: " & itemDesc2 of currentItem & "
Amount: $" & itemAmount of currentItem & "
"
end repeat
set messageText to messageText & "
Original data:
" & fileText
tell application "Mail"
set theMessage to make new outgoing message with properties {visible:true, subject:"Budget - Add Item Success", content:messageText}
tell theMessage
make new recipient at end of to recipients with properties {name:"Tim Wilson", address:"twilsonco@gmail.com"}
end tell
set sent to send theMessage
if dontQuit is false then
if sent is true then quit
end if
end tell
end successMessage
on insertItems(excelFile, sheetName, itemList)
set itemNum to count of itemList
set insertNum to 0
set descMatch to false
set userNumbers to {1, 2, 3}
tell application "Microsoft Excel"
open excelFile
repeat with currentItem in itemList
tell worksheet "Year"
if itemWho of currentItem is not in userNumbers then
quit saving no
return {3, insertNum}
end if
set itemWho of currentItem to value of cell ("O" & (itemWho of currentItem) + 32)
if itemType of currentItem is "Expense" then
repeat with i from 32 to 44
if itemDesc of currentItem contains value of cell ("B" & i) then
set descMatch to true
exit repeat
end if
end repeat
else if itemType of currentItem is "Income" then
repeat with i from 32 to 39
if itemDesc of currentItem contains value of cell ("G" & i) then
set descMatch to true
exit repeat
end if
end repeat
end if
end tell
if descMatch is false then
set expList to {}
set inList to {}
tell worksheet "Year"
repeat with i from 32 to 44
if value of cell ("B" & i) is not "" then set end of expList to value of cell ("B" & i)
end repeat
repeat with i from 32 to 39
if value of cell ("G" & i) is not "" then set end of inList to value of cell ("G" & i)
end repeat
end tell
quit saving no
return {2, insertNum, expList, inList}
end if
tell worksheet sheetName
repeat with i from 5 to 104
if value of cell ("D" & i) is "" then
if itemDay of currentItem is not "" then set value of cell ("C" & i) to itemDay of currentItem
set value of cell ("D" & i) to itemWho of currentItem
set value of cell ("E" & i) to itemType of currentItem
set value of cell ("F" & i) to itemDesc of currentItem
set value of cell ("G" & i) to itemDesc2 of currentItem
set value of cell ("H" & i) to itemAmount of currentItem
set insertNum to insertNum + 1
exit repeat
end if
end repeat
end tell
end repeat
if i = 104 and insertNum < itemNum then
quit saving no
return {1, insertNum}
else
run VB macro "dateSort"
tell active workbook
save workbook as filename (excelFile as text) file format macro enabled XML file format with overwrite
end tell
quit
end if
end tell
return {0, itemList}
end insertItems
on parseLine(theLine, delimiter)
set astid to AppleScript's text item delimiters
set AppleScript's text item delimiters to {delimiter}
set theTextItems to theLine's text items
set AppleScript's text item delimiters to astid
repeat with i from 1 to (count theTextItems)
if (item i of theTextItems is "") then set item i of theTextItems to missing value
end repeat
return theTextItems's every text
end parseLine
on titleCase(_string)
set _code to "import sys; print sys.argv[1].title()"
set _script to "/usr/bin/python -c " & _code's quoted form & " " & _string's quoted form
return do shell script _script
end titleCase
on appisrunning(appName)
tell application "System Events" to (name of processes) contains appName
-- returns true if excel is running
end appisrunning
Model: MBP-r
AppleScript: 2.2.4
Browser: Safari 536.25
Operating System: Mac OS X (10.8)
