Category Archives: Uncategorized

Nicely colour data in Google Spreadsheets

I love a feature in Excel (never thought I’d say that!) where I can choose a range of data and have Excel colour the backgrounds of the cells from red to green or green to red based on the cell’s value in the selected range. Excel calls this “Conditional formatting” and while Google Spreadsheets has a feature that has the same name, it’s really a completely different thing and it’s driving me nuts. But it has scripting, so I had to fix this lack of functionality.

Screen Shot 2013-07-04 at 16.54.59

To install this script, open your spreadsheet, open Script editor… in the Tools menu. Paste the source in and save. Then reload the actual spreadsheet. You should now have a new menu in your toolbar called ColourTool, with two options to colour cells. Choose the range you want to colour, choose an option and tada, your cells change background colour.

Note: This has not been tested extensively. There’s no code here to change the actual data, but, use at your own risk.

(Apologies for WordPress doing things to code formatting!)


function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.addMenu("ColourTool", [ {name: "High is Green, Low is Red", functionName: "hiToLow"},{name: "High is Red, Low is Green", functionName: "lowToHi"}]);
}

function hiToLow() { colourCells(1); }
function lowToHi() { colourCells(2); }

function colourCells(mode) {
var s = SpreadsheetApp.getActiveSheet();
var r = s.getActiveRange();
var rows = r.getNumRows();
var cols = r.getNumColumns();

var minV = 9007199254740992;
var maxV = -9007199254740992;

for(var i = 1; i < = cols; i++) { for (var j = 1; j <= rows; j++) { var value = r.getCell(j,i).getValue(); if(value !== "") { minV = Math.min(value, minV); maxV = Math.max(value, maxV); } } } for(var i = 1; i <= cols; i++) { for (var j = 1; j <= rows; j++) { var val = r.getCell(j,i).getValue(); var percent = (val - minV)/(maxV-minV); if (mode == 2) { percent = 1-percent; } var colour = getColor(percent); r.getCell(j,i).setBackgroundRGB(colour[0],colour[1],colour[2]); } } } function getColor(power) { var H = power * 0.4; var S = 0.9; var L = 0.6; return hslToRgb(H,S,L); } function hslToRgb(h, s, l){ var r, g, b; if(s == 0){ r = g = b = l; // achromatic }else{ function hue2rgb(p, q, t){ if(t < 0) t += 1; if(t > 1) t -= 1;
if(t < 1/6) return p + (q - p) * 6 * t; if(t < 1/2) return q; if(t < 2/3) return p + (q - p) * (2/3 - t) * 6; return p; } var q = l < 0.5 ? l * (1 + s) : l + s - l * s; var p = 2 * l - q; r = hue2rgb(p, q, h + 1/3); g = hue2rgb(p, q, h); b = hue2rgb(p, q, h - 1/3); } return [r * 255, g * 255, b * 255]; }

Teaching your kid to ride a bike, the right way

The summer has arrived and I’m seeing a lot of parents teach their children to ride a bike in my visits to Victoria Park. And it’s painful, as most of them are doing it utterly wrong. Learning to ride a bike is safe, fun and easy when done the right way, as opposed to the episodes of frustrated crying I’m having to witness.

Here’s how to do it the Right Way:

* You need a bike where you can adjust the saddle to go so low, your child can have both his feet on the ground simultaneously, knees bent.
* Do not purchase stabilizers, and if the bike already has one or more, take it off. Stabilizers make it harder for your child from learning actual balance.
* Remove the pedals from the bike, which turns it into a kick bike.
* STEP 1 for your child: learn to kick yourself forward using his/her feet without falling over. When he/she is good enough to glide forward for 5+ meters without touching ground, the balance is 90% there for biking. It’s probably a good idea to spend a couple days at this level, before progressing on.
* STEP 2: Put the pedals back on. Do not raise the saddle – you kid still needs to be able to put both feet on ground comfortably. Help the kid get started with the pedaling by pre-positioning the pedal of the stronger foot to be in the optimal position (just push down!), possibly gently pushing from the back to get up to speed (remember, he can balance on the kick bike). Your kid now needs to learn to get to speed and STOP on her own. Doing this bit on the lawn might be a good idea.

How long does this take? My daughter, age 3, learned the kickbiking to perfection within two days of practice (and didn’t need much encouragement as it was so much fun), and the pedalling step took about 10 minutes, after I told her she can’t ride on asphalt before she can start and stop by herself.

So what I’m advocating as a Bad Idea is:

* Using balancing wheels – they teach your child something other than balancing on the bike!
* Aiming to save money by getting a large bike, so you don’t have to get another one the next year.

So I’d have one more list here, here’s three additional tips:

* Remember to give a boatload of praise for your kid for trying to learn. Being praised for trying works much better than being praised for success.
* Get a good helmet. Of all biking helmets for kids, the best ones I’ve seen by far, are manufactured by Bell.
* If you can afford it, I recommend getting a learner bike and the real bike from Puky. The bikes are very high quality, and the saddle is adjustable to lowest height for each wheel size, that I’ve seen on bikes from any manufacturer.

Brief history of privacy, and a projection to the future

Privacy, as people currently think of it, is a modern invention.

If you go back 20 000 years, we lived in caves, where nobody had a second of peace. If you go back 200 years, practically everyone lived in small villages, where you had privacy in your home, but in practice everyone knew what everyone was up to.

The Village Privacy Model had a strong influence on people’s behaviour. If you behave in a manner that’s unacceptable to the community, you knew everyone around you go to know it, and you still had to face them. Mess up badly enough, and your life became very hard, regardless of whether there were “official” sanctions against you or not.

Enter urbanisation. People are moving into cities, where you’re effectively anonymous. It’s very easy to go and misbehave in a manner that wasn’t possible in smaller communities, and have no repercussions on it whatsoever. You won’t see those people again. You’re in the City Model of Privacy.

What this also means is the reverse – you can assume you’re in semi private mode at all times. Not only are you safe in your home, you’re also anonymous outside. I think this is having significant influence on how people perceive the expected level of privacy you have now – while at the village level there was almost no expectation of privacy, you can now expect privacy at all times.

Even further, enter Online use. You’re even more anonymous. Go to some random site to troll and there’ll be no repercussions. Nobody will know who you are. Nobody has the resources to track people.

Except you can’t assume this anymore. Technology caught up.

We’re entering a phase where it’s sensible to assume someone sees you at all times, and is probably making a recording of it. Both online and offline.

What inspired this post is this video. Some idiot tripped and severely hurt a young hockey player. It got recorded and went viral, and effectively ruined his life.

So for whatever it’s worth, we’re now getting back to the Village Model, from the brief experiment of the City Model. It’s Marshall McLuhan’s Global Village, with all of it’s privacy implications. He didn’t personally witness the computing technology explosion and so didn’t predict the implications to the extreme, but I’m sure he’d be excited if he was living today.

A lot of people are finding this new lack of privace extremely creepy and I don’t blame them. It’ll be interesting to see how the society adjusts. Going back to City Model is not going to happen.

De-moulding an espresso machine

Screen Shot 2012-12-25 at 16.16.22I’d been lazy and not dried my Rancilio Silvia espresso machine properly during a period of not using it for a while. To my horror, we found out some mould had developed inside the machine, which obviously made the coffee taste and smell pretty horrible. I did some frantic googling and found three suggestions to get rid of the mould:

  1. Descaler with mould remover
  2. Bleach
  3. Vinegar

Of these, I couldn’t find any of the recommended brands online or in local shops. I tried a regular descaler solution and it seemed to do the trick, but the smell came back in a week. I didn’t like the idea of using bleach as it’s properly poisonous, and it seems bleaches that are pure chlorine aren’t to be found in London. So vinegar it was.

Screen Shot 2012-12-25 at 12.36.02Ocado had the pictured strong (24%!!) pickling vinegar available. The process I used that seems to have worked was:

  1. Clean the water container thoroughly, until there’s no smell left
  2. Pump a full container through the machine
  3. Make a strong vinegar solution (I put half the bottle in half a container of water)
  4. Pump the vinegar solution through the machine until you can smell the vinegar is full through the pipes. Do this to both the coffee extruder (or whatever it’s called) and the steam pipe.
  5. Switch off the machine for an hour
  6. After an hour, pump the rest of the vinegar through
  7. Clean the water container thoroughly, again, until there’s no smell left
  8. Pump five full containers of fresh water through the machine, until there’s no smell left. Remember the steam pipe.
  9. Done!

Note this process is not recommended by the manufacturers as it probably corrodes some of the internals. Do this at your own risk!

Two weeks with Roomba

I’ve been eyeing robot vacuums ever since the first Roomba came out, and when the nearby supermarket had a Roomba 521 on clearance, I splurged. Here’s the experiences this far.

Some background – we have a dog and two small kids in a 74.5 square meter flat. What this means is, the flat goes from neat to disaster area in about a quarter of an hour, sometimes faster. And that happens every day. Enter Roomba.

The kids and the dog took a few days to adjust to their new friend. Kerttu immediately named the robot wally (or wall-e) and talked to it the first couple days but then mostly lost interest. I think the dog still sometimes thinks the vacuum’s out to get her.

The algorithm the robot uses to navigate floors is interesting and watching wally do his job is strangely hypnotic. I’m noticing I’m starting to learn to predict where the robot will go next, even though I don’t know how the robot works. Subconscious pattern processing is pretty amazing, I guess.

I’m positively surprised at the efficiency of the robot. It’s not perfect and if I vacuumed by hand, the rooms would definitely be cleaner. On the other hand, I’m getting a 95% vacuumed flat with the touch of a button, which is completely awesome. I don’t think our floors have been this clean in a long time. To be fair, the real test for wally will be when Luna the dog starts dropping hair in a significant manner during the summer heat. If the results are as good then and the vacuum doesn’t choke, I’ll be pretty darn impressed.

It’s pretty clear that in order to get real value, you need to establish a routine for clearing the floors from toys & random junk every morning and turn on the robot for daily maintenance vacuuming. This seemed a bit of an effort the first few days but I’m getting hang of the routine and I’m feeling good about the results.

Two complaints this far.

First, the unit we got feels noisy. I know it’s silly to assume a vacuum would be silent, but the unit looks somewhat graceful so I somehow expected it to be quieter based on the looks. I’d definitely prefer to be out of home when wally is working, but I work from home right now, so that’s unfortunately not an option every day. It does sound like half the noise is the motors though, so I’m sure the unit could be made quieter.

Second, the dock supplied with the unit is just silly. The dock is super light weight and the bottom of it is slippery, so I can’t see how it’s supposed to really work. After wally failed to dock the 50th time due to the dock slipping away, I just put some two sided tape onto the bottom of the dock so it doesn’t move anymore, and the problem was solved. I suspect rubberised bottom with more friction would fix the problem, but Roomba decided to save costs and give me a cheap piece of slippery plastic instead.

And a couple improvements.

Firstly, I suspect the navigation system of the robots hasn’t improved hugely despite the product having been on market for a fairly long time. The robot definitely doesn’t remember the flat’s layout. Given today’s tech, I’d be very surprised if it wasn’t possible to create significantly better system that was still cheap enough to manufacture, so from this perspective it looks like Roomba is resting on their laurels, waiting for someone to come to market with a superior product and eat their lunch.

Second, it feels like the product would be greatly improved by adding some more personality traits to it. The couple little quirks the robot has are great – when you activate it from the dock, the robot makes the same warning beep sound as if it was a truck, as it backs out from the dock. And apparently you get some quirky talking when you try to steer the robot with your foot a little too much. But I’d like to hear the robot tell me about how full it is. Or maybe occasionally speak out how many meters it’s traveled in the flat. A bit of Genuine People Personalities and I think I’d love the bugger a bit more. And a talking piece of kit would probably make the product more viral. Having a switch to turn off the personality might be a good idea though, with related snarky comments when you turn it back on.

Anyway, summary. Yes, after two weeks of use, I’d say the €250 was well spent. Had I paid €500, I’m not sure if I’d be as happy. If you’re contemplating getting a robot vacuum, I suggest you only get one if you get a good deal and are willing to keep your floors clean. With those caveats, it can be a great investment. :)

Update: I’ve been informed the current models are incredibly good compared to the Roombas that first came to market. Myself assuming they’ve not improved dramatically is a testament to how good a job Roomba has done on the robots – the current model Just Works, so the user gets an impression that creating the product must have been effortless. The truth seems to be a massive amount of iteration has happened, even if the product still looks similar to the first models that came out.

Smell of clean

When you think of what clean laundry smells like, what is the smell that comes to your mind first? I’m assuming more than half the people reading this will think of the smell of their favorite detergent, which means all those ads featuring happy women smelling laundry has been extremely successful at making people associate the smell of clean with a particular mix of chemicals.

Above thought came about as I was just hanging the laundry and smelled it. What clean laundry smells like is wet cloth, an almost nonexistent but perceptible smell. And clean here means the item of clothing doesn’t have anything on it including stains and chemicals. Just water.

Fixing Readability’s business model

John Gruber doesn’t like the way Readability is doing business. I have a simple proposal for fixing what they’re doing that I think would make almost everyone happy.

Readability should hire someone to work full time in creating a database of who owns each site they owe money to, going through domains in order of how much money they ought to pay the owner of that domain. Once you reach the person who owns a domain, just tell them how much you’ll give once they’ll sign up. If the content publisher refuses the money, it’d be a conscious choice, not oversight due to not knowing Readability exists.

There, most objections to Readability’s business gone.