Me: Are you guys dressed yet?

The Girl: No.

The Boy: No… I’m not even wearing pants.

No drivers to install. No fuss. Ubuntu booted without missing a beat. It just works.

My spreadsheet has three columns that track the date I biked, how far I went, and how long it took me: Date, Miles, Time. That’s all I care about, for now. But from that I can derive data like my average speed (miles/time) and do a chart of my progress. But what I really want to know is how far I’ve gone in total, how far I’ve gone this week, and how far I’ve gone this month. And that’s when I discovered the magic that is the Google docs QUERY function. It is very cool. It basically lets you do something akin to a SQL select statement on your spreadsheet data. So here’s what I came up with to automatically calculate my total miles this week and this month (overall total is easily done with the built-in sum() function). The hairy stuff below are the contents of cells and you can copy and paste into your spreadsheet.

This week:

=query(A:B, "select sum(B) where A >= date '" & text(date(year(today()-weekday(today())+1),month(today()-weekday(today())+1), day(today()-weekday(today())+1)), "YYYY-MM-DD") &  "' and A <= date '" & text(date(year(today()-weekday(today())+7),month(today()-weekday(today())+7), day(today()-weekday(today())+7)), "YYYY-MM-DD") & "' label sum(B) 'THIS WEEK " & text(date(year(today()-weekday(today())+1),month(today()-weekday(today())+1), day(today()-weekday(today())+1)), "M/D") & "'")

This month:

=query(A:B, "select sum(B) where A >= date '" & text(date(year(today()),month(today()), 1), "YYYY-MM-DD") & "' and A <= date '" & text(date(year(today()),month(today())+1, 1)-1, "YYYY-MM-DD") & "' label sum(B) 'THIS MONTH " & upper(text(date(year(today()),month(today()), 1), "MMM")) & "'")

So let's break down the "This week" query. The cell starts with =query() which tells the spreadsheet to calculate that function and put the result in this cell. A:B is the range for the query. In this case, every cell in columns A and B (where A has my dates and B has my miles). Then the query itself is structured like this:

select sum(B) where A >= date 'XXX' and A <= date 'YYY' label sum(B) 'ZZZ'

I took out all of the date calculations so you can see it more clearly. It just says "get the sum of column B where column A is between two dates". Pretty simple. The main trick is calculating the date ranges for the start and end of the current week and the start and end of the current month. And here's how you do that. Hopefully you can see where the calculations below plug in above and follow along.

The current week starts on today()-weekday(today())+1. That is, "today - the day of the week + 1". So if today is Monday Oct 24, then this code would calculate: Monday Oct 24 - 2 + 1. Which ends up being Sunday Oct 23. And of course the last day of the week is just 6 days after that: today()-weekday(today())+7. (This is just the first expression +6 where the +1 and +6 have been summed.)

"This month" works exactly the same way but with different dates. The current month starts on date(year(today()),month(today()), 1). That is, "a new date based on the current year, the current month, and use 1 for the day". And the current month ends on date(year(today()),month(today())+1, 1)-1. That is, "a new date based on the 1st of the month after this one minus 1 day". You can optimize this a little by using the eomonth() function (which calculates end of month dates)---but I like my way because I can see exactly what's going on.

Son: Dad, I need help.
Me: With what?
Son: I’m trying to make a peanut butter and jelly but I can’t open the jelly. It’s too hard.
Me: OK, I can help you with that.
Son: And the second problem is I can’t find the bread!
Me: We’ll find that, too. Come on.
Son: Thanks, Dad.

The app uses OpenCV to do some processing on live output from my video camera. Here are some screengrabs from the prototype so far:

I look younger without a nose.

Me: You’re the bee’s knees.
Daughter: …
Me: It means ”you’re awesome.”
Daughter: I know what it means, Dad. I speak “old people.”

The only problem I’ve had is this: it wouldn’t remember the window size and position of the desktop app on my Ubuntu machine. I installed CrashPlan as root as suggested by the installer (so that it can backup anything on the system). This put CrashPlan in /usr/local/crashplan, owned by root. Turns out that CrashPlan desktop wants to save window geometry in /usr/local/crashplan/conf. But it can’t because I run the desktop app as me, not as root.

So, the fix would be for CrashPlan to store user settings in the user’s home directory (for example ~/.crashplan/ui.properties). But the workaround until that happens is to manually create a properties file in the /usr/local/crashplan/conf folder that CrashPlan desktop has access to. Open a terminal and type:

cd /usr/local/crashplan/conf
USERNAME=`whoami`
sudo touch ui_$USERNAME.properties
sudo chown $USERNAME ui_$USERNAME.properties

That creates an empty properties file in the crashplan directory with your username that you have access to. You should be able to just copy that into a terminal. Double check the directory where CrashPlan is installed and note the backticks. My username is john so it creates a file named ui_john.properties. The next time you close CrashPlan, it will be able to write to that file so it will remember its window position and size.

My son’s activity is mostly games. My daughter’s is mostly Netflix.

Our goal is to establish long-term relationships with developers,
and we encourage you to contact us before terminating your agreement
to see if we can improve your Amazon Appstore Developer Program
experience.

If you would like to terminate your agreement, please send an email
with your developer name, contact information, and a list of your
applications to apps-notices@amazon.com with a CC via email to
contracts-legal@amazon.com. We will notify you of receipt of your
email and respond within 48 hours with confirmation of your request
to terminate your agreement.

So I did that. Felt a lot like filling out one of those stupid mail-in rebate forms. Two days later I received a notice that my agreement was terminated and that they’d “appreciate feedback on why you are terminating your agreement.” The email was signed: Winkie C., Amazon Appstore Account Team.