Month: January 2013

★ Adding Items to the Grocery List

So last week I mentioned my shopping list spreadsheet.

However, there’s one flaw. My Mac isn’t in the kitchen, and adding things to the spreadsheet via Numbers on my phone is… awkward at best.

But I can send a text with my phone. And I have Messages running on my Mac. And I read this. And it all clicked.

So now I text “gr milk” and milk gets added to my grocery list, it gets sorted by aisle and saved to iCloud. And it only works if I do it from my phone. Don’t need anyone adding things to my list.

While I was at it, I added commands to quit Chrome and to fire up iTunes.

There’s one more thing I want to do to this – I want to make a small script that calls this one, instead of having Messages call it directly. the reason is that when you edit the script, you need to re-add it to Messages and honestly, it was becoming a bit of a pain during debugging.

Here’s my script:

on nextEmptyCellInColumn(sheetName, tableName, colNumber, compValue)
— Copied from
— Finds next empty row in the spreadsheet
local sheetName, tableName, colNumber, res, i, len
tell application “Numbers”
tell document “Groceries”
tell sheet sheetName
tell table tableName
set len to row count
set res to 0
repeat with i from 1 to len
if (value of cell colNumber of row i) = compValue then
set res to i
exit repeat
end if
end repeat
if res = 0 then
error “No empty row in table ” & tableName & “!” number 111
end if
end tell
end tell
end tell
end tell
return res
on error eMsg number eNum
error “Can’t nextEmptyCellInColumn: ” & eMsg number eNum
end try
end nextEmptyCellInColumn

using terms from application “Messages”
on message receivedtheMessagefromtheBuddyfortheChat
set theName to full name of theBuddy
if theName contains “Neil Kelly” then
if theMessage starts with “COMMANDS:” then
— This is here to prevent the reply for commands from triggering all the commands
set a to 1
if theMessage contains “itunes” then
tell application “iTunes”
end tell
end if
if theMessage contains “chrome” then
tell application “Google Chrome”
end tell
end if

if theMessage starts with “gr ” then
set numCell to nextEmptyCellInColumn(1, 1, 1, 0)
set groceryItem to text 4 thru (length of theMessage) of theMessage
tell application “Numbers”
tell document “Groceries”
tell sheet 1
tell table 1
set value of cell 1 of row numCell to groceryItem
sortbycolumn 2 directionascending

end tell
end tell
end tell

end tell
end if
if theMessage contains “commands” or theMessage contains “help” then
— reply with list of commands
— this doesn’t autogenerate
— so if you add a command you should document it here too
— backslash-n adds a newline
gr to add to grocery list
chrome to quit chrome
itunes to start itunes” to theChat

end if
end if

end if

end message received

end using terms from

★ Grocery Lists & Data Collection

I tend to be the one who does most of the grocery shopping for our family. I’ve always been curious about some of the data that could be collected – specifically, pricing data and how often we purchase certain items. So I built a spreadsheet. (You know how they say “To a child with a hammer, everything is a nail”? Yeah, well, spreadsheets are my hammer.)

Of course, just a list of all the stuff we buy with prices paid would be annoying to keep up – since there’s a lot of stuff we don’t buy every week, there would be a mountain of empty space to deal with. Okay, so two spreadsheets – one with a list of everything, and another with the current week’s list. And since my preferred store offers a list of items and aisles, I can start with that. Fire up the scanner, fix some minor OCR issues and I have a list of lots of what they sell and where in the store it is. This is important because…

I like my grocery list to be in the order I’m going through the store. So do a VLOOKUP on the item name and get the aisle number. Works for lots of stuff, manually add a few items to the store directory and away we go.

I used a Numbers spreadsheet for this task – sure, Excel has lots of geekery going for it, but Numbers has iCloud which means I can edit on my desktop with amenities like mouse and keyboard, and update at the store on my phone.

Next up… geeking out on adding items to the list.