Innovation, Life Hacks

Make a Boolean Expression From A List of Rows in Google Sheets


We all use search forms every day. There’s usually an advanced search form of some sort that allows you to enter a boolean expression. You know Boolean expressions as “This OR That” – an inclusive search that increases results – or “This AND That” – an exclusive search that limits results.

When you need to filter your results, a powerful method is to make a list of companies, individuals, skills – whatever you are using to get more precise answers – and create a Boolean expression to get a better initial set of results from a search engine. Yet when someone gives you a list of 10, 20, 50, or 100 items the idea of making a delimited list of “Item1” OR “Item2” OR “Item3” is more difficult than you’d like to be.

Never fear: Google Sheets to the rescue! This is a perfect example of a task for a Lazy Programmer™ to solve, as it’s much easier to have a spreadsheet count values and rearrange text for you than it is for you to do the work by hand. Let’s take a look.

As an example, I created a utility spreadsheet that takes a range of values, counts those values, and outputs a result string that delivers a boolean expression to paste into your favorite advanced search page. Whether you’d like to create a list of criteria to find your next job, search Twitter more effectively, or simply create a customized search for Google, this expression builder can help.

This spreadsheet has two formulas that do the work for you. The first one joins a range of values into a concatenated string (turns list of Orange and Apple into ‘Orange’ OR ‘Apple’ OR ”):


There are five functions in this expression:

  1. JOIN combines an expression of values into a string connecting each item with the string ‘ OR ‘;
  2. INDIRECT tells the spreadsheet to wait until the range expression in the formula from A2 to the last unblank value in column A is completed and we know the number of the last row to select;
  3. TEXT converts a complex expression into a value – in this case a calculation of the number of rows in the column of this spreadsheet minus the number of blank rows in the column of this spreadsheet – so that INDIRECT will take result of this expression and substitute “5”, the last row above that has a value. This results in a range from A2:A5 to feed back to JOIN;
  4. ROWS counts the number of rows in a range – the standard number in a Google Spreadsheet is 1000 – and returns an integer to feed back to the expression inside of TEXT;
  5. COUNTBLANK returns the number of blank rows in a range, so that our calculation of rows minus the number of blank rows leaves us with the number of rows containing values (4) or the total number of rows including the header (5).

Now that we have our formula, it creates the string:

'Orange' OR 'Apple' OR 'Banana' OR 'Grapefruit' OR ''

From the rows of Orange, Apple, Banana, Grapefruit above. And there is one problem here: that we need to remove the trailing “OR ”” that resulted from the expression.

Our second formula, REGEXREPLACE, removes the last “OR ”” from the string. RegEx is sort of like Black Magic, so you will probably need to use a helper site like Rubular. This string looks for a specific string right before the end of the line (represented by $)

OR ''

and replaces it with nothing.


This leaves you with the strong you want to paste into a search form:

'Orange' OR 'Apple' OR 'Banana' OR 'Grapefruit'

You’re all set! Now, use this utility with any number of rows in your list of items to create a Boolean expression made for you automagically.


What will you let drop today?

austin-chan-275638Photo by Austin Chan on Unsplash

When you work in a startup, you are guaranteed to make a decision that will disappoint someone today. Every day is a series of stack-ranked decisions. You decide (in near real time) whether Item A is more important than Item B, and make the best decision you can at the time. Many of those decisions are right. Some of them turn out to be wrong. It’s really hard to know before you make the decision which ones are going to be the wrong ones.

If you start with the premise that you will need to let items drop (there are too many tasks to do, not enough time, inability to delegate, not enough resources), it’s easier to focus on the inverse problem. Which one task do you need to get done today to move things forward the most? You might move two or three or ten things in a day, but what’s the one task when you look back will be the one you say “yes, I needed to do that or bad things would happen?”

Great – easy advice, you say – now how do you do this in practice? There are many ways to sort your list in the order that will give you the top stack rank. You might start with effort. What are the highest effort tasks? Give everything a 1 (easy), 2 (more than easy) and 3 (sounds big). And now think about the value of those things: 1 (small), 2 (bigger), and 3 (definitely high). If you’re spending time on easy small things they are not going to be the ones that create value. In the effort scenario, you need to be working on the smallest big thing that creates value.

But you need to allocate Time as well. Tasks that need to be done in a week are probably not more important than those that need to get done in two days, or tomorrow. In a perfect world you would have addressed the “tomorrow” tasks days ago but … sometimes life doesn’t work that way. You need to be aware enough of the deadlines for items (especially those that are contingent on the work of other people). Time (especially when there are near term tasks that are bigger and more than easy) can disappear quickly. One way to handle this is to under-schedule your expected tasks so that you have reserved capacity for just-in-time triage.

And there is importance. Is this a task for a customer? Is it a task that moves a critical product feature forward? Is it a commitment that was made and is potentially late if you don’t take action? Perhaps the most difficult choice to make is managing the conflict between two tasks of competing importance. The solution? Pick your best. Make Your Choice. Keep moving. The speed of making decisions and moving the task forward is almost as important as picking the right one. Until you make a mistake. That’s the time to stop and figure out what you should do the next time you make a decision.

There is no right answer. But there is a mostly good answer most of the time. Know enough about the decisions you need to make to get it right a lot. When you don’t know, ask leaders in the organization which direction they prefer. And keep learning.

Life Hacks, Product Thoughts, Startup

Thoughts on the Bus Problem


(photo by

What’s the most important thing you do at work?

Most of us, when asked “how do you create job security”, default to explaining a way of interacting with others that only we can do. If you have unique skills, of course you would want to create a solution where you can solve the problem. It’s romantic to think that you – the cowboy or cowgirl – can race into the important situation and solve the problem where no one else can, or do it faster than anyone else.

Described differently, “I am the only one who can get it done on time and under budget” also looks a lot like “I am a bottleneck”, or “my company is now vulnerable to the ‘Bus Problem’, where if I get hit by a bus my company will have absolutely no way to do the things I know how to do. These statements now look a bit different.

A Corollary To What You Do Today

But what if creating personal job security looked completely different and had more to do with creating systems everywhere you go that help everyone else in the company raise their game? In this version of the bus problem, maybe the solution is to make bus schedules (so that all buses run on time), and develop contingency plans (like snow routes) for what happens when there is inclement weather or other unexpected behavior like traffic?

The best way to solve the problem of institutional knowledge sharing is to share that knowledge. Duh. But it means more than simply barfing out that information in whatever messaging suite happens to be the flavor of the month. True knowledge sharing means that you can isolate the facts and share the strategy implications of changing course, that you can write a procedure anyone in your company can follow, and that if you are not in the office the process works without you there.

What does this look like in practice?

Let’s say for the moment that you are responsible for updating the team on a new feature in your product. As a consumer of that information inside of the company, each person in each role needs something different. Sales might need to know if the price of that product changes or if specific customers had been waiting for it. Marketing might need to know if there are marketable features that could be shared with a wide audience. Engineers might want to know if there are new things to test and build. And Customer Support needs to know the typical things customers will ask and how to solve their problems.

Compare your original goal of becoming the only one who can solve a critical problem with the goal of sharing information with everyone in the company at the right time to ensure a productive product release. If you don’t create systems that ensure people on your team know what they need to know before you can tell it to them, you will fail. Your participation in the process should be the reinforcement of the knowledge, rather than the only way they know that information.

Start today by writing down an important thing that no one knows into instructions that person can follow, and then take the day off. Train a trusted resource, take the day off, and see how things went. If you get to “One in a Row” on this problem, you’re ready to tackle the next critical business process you own until the whole business can run without you telling them which buses run next on the schedule.

Life Hacks, Photography

A Practical Guide to Uploading An External Hard Drive of Photos to iCloud

photo by


In The Old Days, We Only Printed the Good Ones

Photos used to be easy to manage in the pre-digital era. You bought some film, took some shots that you hoped would turn out well, and then printed all of the pictures. Or if you were more picky you decided to print only the good ones. It was expensive to print, time-consuming, and difficult to store.

Fast forward to 2017 and things are very different (duh.) Storage is almost free, it’s trivial to store tens of thousands of photos so that you can print them on demand, and relatively easy to store if you have an access device like a phone or a computer. Scrolling through a photo album looks much different than it used to, but also opens up many more opportunities for creativity and organization than ever.

What Happens if You Have A Lot Of Photos?

If you have a lot of photos (let’s call this 10,000+) that you want to load and maintain accessibility using a cloud service of your choice, managing these photos can be challenging. Here’s the Job To Be Done: determine how to synchronize 10+ years of photos made pre-cloud services so that they update with all of the handy-dandy cloud services without breaking the bank and while maintaining the discoverability we like from cloud services.

I’m choosing to optimize in this example for a consumer or pro-sumer set up where the main goal is viewing the image or movie on lots of different devices. I’m also viewing this from an Apple-biased point of view, so a spoiler here is that the recommendation will not end with the option to use Google Photos and Just Forget About it. (Though this is a perfectly fine answer).

What and How Do You Back Up?

There are a few obvious options when considering a backup procedure for your photos (or for your whole computer).

Here they are, in order of “solves everything” to “solves a point solution like photos”:

  • back up the whole computer to a shared location (Carbonite, Crashplan, or similar)
  • create a local backup NAS using RAID and AWS
  • continue with lame local back up to a single hard drive
  • upload photos to a cloud service like Google Photos or iCloud

Option 1: Whole Computer Backup

Backing up the whole computer to a shared location in some cloud somewhere seems like a great option if you have an unlimited symmetrical internet connection. For people with a fiber connection, this one would work great. For those of us with a typical cable modem connection for internet, have you thought about how long it takes to upload data?

If you haven’t thought about it, here’s a handy table from that article:


Most of us are looking at the 10Mbps link speed and somewhere between 100GB and 1000GB to upload. Once you start talking about hundreds of gigabytes or more of data this might take days (or almost a week) to complete unless you want to take the radical step of using an Amazon Snowball and putting your data in deep storage.

More practical services like Carbonite or Crashplan will still take days to run a full backup.

So what’s a way that you could back up everything yet still keep a copy locally?

Option 2: Network Attached Storage

Being geeky as I am, I love the idea of purchasing new hardware to back up files on the computer seamlessly to a local storage server on the network and then seamlessly upload the files as needed to a cloud service that maximized the savings.

This is a fun idea, but it’s not cheap either. If you were going to set up a home NAS you might investigate a Synology NAS (2 or 4 or 8 bay) which will set you back a minimum of $600-800 including the right number of hard drives for the storage you need. There are other, cheaper solutions available from Western Digital but frankly I’d be worried about uploading to someone else’s cloud not named Apple, Amazon, or Google.

The goal here was not to optimize for local storage but to find a place to upload about 175GB of photos and videos that have accumulated over a decade.

Maybe there is a simple, practical solution – a local USB drive is fast, easy, and cheap (I have a 2TB Western Digital Passport, and that’s good for the Sneakernet at my place).

Option 3: Local Back Up to a Single Drive

This is the least bad option (given that I’m using it today) but it doesn’t protect against the inevitable hard drive failure that will happen at some point. Local back up also doesn’t protect against loss from theft or fire and fails to solve the basic problem of “how can I use these images more effectively instead of looking at them in a file tree once every 6 months?”

As a backup (not the only backup), I think this solution actually works quite well. The primary benefit of being able to put a 2TB drive in your pocket is that you can easily move a large number of files between computers even when you have a relatively fast wireless internet.

So we’ve got a solution for local backup, and haven’t yet landed on the right solution for cloud backup.

Option 4: Upload photos to a cloud service

There are lots of cloud services you might choose to solve this problem, though I lean toward the paid version to gain a little bit of leverage around getting the data out should one of the services go away in the future.

So which one should you choose? The free one (Google Photos), the paid one (Dropbox), or the more expensive and integrated one (Apple iCloud)? Perhaps you like to solve your own problems and would like to buy raw storage using AWS. For this solution I’m optimizing for ease of use and a turnkey system.

Google Photos gives you instant upload and permanent storage, and a decent photo editing and management service. It is also optimized for the Android ecosystem – it works for iOS, but doesn’t show up natively within Apple Products unless you are super clever about how you set things up.

Dropbox offers plenty of storage (1TB for $99/year) but is more of a file synching service than a photo synching service. It is extremely handy for sharing large files and less easy to find that photo you were looking for (the Carousel photo app notwithstanding).

So, Apple has trapped me again into making a choice based on the discoverability of files and lock-in to the ecosystem I use most.

Getting the files to the service takes a little work

You’re not quite done. If you pick iCloud like I did there is some work you need to do first to ensure that you don’t fill up your hard drive.

  1. Open Photos while holding down the option key – this will give you the option to create a new System Photo Library in the place of your choosing.
  2. Make a new System Photo Library on an External Hard Drive
    This gives you the ability to let OSX’s magic “Optimize space setting” expand to whatever amount it needs to without taking up space on your primary hard drive. If you don’t care or have a gigantic hard drive, have at it. But until Apple changes this setting this is the only way to separate your iCloudified photos and videos from your hard drive space.
  3. Next, enable this new Photo Library to use iCloud
  4. If you don’t want to create an ever-growing photo library, deselect photo > settings > general > copy items to the Photos library
  5. Next, select photo > settings > iCloud > optimize mac storage or set to download originals if you’d like to make a backup at the same time. You’ll need to have enough iCloud storage to store your originals (but you knew that anyway).

Now, you’re ready to import photos from your external hard drive.

Use the Import option in Photos (file > import) to import the photos you’d like to add to your iCloud library.

When this is done select the Import New Items option to add these to your Photos Library.

There’s one more thing you’ll need to do – create a smart folder of “referenced” photos – before you can add these external photos to iCloud. Now, use the File > Consolidate… option to add the photos to iCloud.

Great! You are on your way to seeing all of your photos and videos more often. There’s one final thing to consider, which is that iCloud imposes a daily, weekly, and monthly limit on uploading.

Learning, Life Hacks, On Writing

Pattern Minded

My happy place is an art studio where all of the items have their own section.

I love to draw. Ever since I can remember I’ve created doodles, pictures, paintings, and other kinds of art. And it generally comes naturally to me – the kind of skill that other people call “artistic” and that I call “just drawing” – until it doesn’t.

I’m not sure what this gap feels like to people who don’t draw, so I’ll try to describe it in terms most people find easy to understand: imposter syndrome. When I don’t “feel” like drawing, I come up with every excuse to avoid that practice. I stay away from art materials and all of those wonderful colors. I stop drawing because there’s no chance of messing up.

That’s really not fun. Sometimes it has lasted for years. I am not sure of the first time I had this feeling but I would guess it happened when I enrolled in a Ph.D program in History instead of renting an Art Studio and drawing for a living. Maybe not drawing was a good thing, though.

If I hadn’t taken a break from drawing I would have spent much less time with computers. I might have missed out on learning to program. I might also have not engaged with new technologies like mobile and social and local commerce.

I am drawing again.

 It doesn’t take much to get started again on drawing. Just a little bit of time.
The hack that got me going again? Repetition. Small pictures. Doodling. Pretending “this drawing doesn’t matter.” Because the real benefit to creating and writing about it is a pattern itself – the self-reinforcing loop that happens when you make stuff, and look back later to see whether it’s good – and its absence is an anti-pattern.

So if you see me stop drawing, ask me to draw you something. Give me a commission. It doesn’t need to be paid, and it can be just enough to give me an idea. Making art pays off for me in many more areas of my life than the artwork I create. That process of making is a pattern that leads me to a place where I build amazing things. 

Media Mind, Productivity, Uncategorized

Escaping from Messaging Hell

One Way to Escape from Message Hell

Admit it — it’s really great to get the message you want, when you want it, and in the time and place that you want it. And that vision is usually hard to match.

photo by

Most Messaging is not Like This

And it’s really horrible to get most unwanted messages. It should be simple (and of course it’s not) to find the right balance of messaging across various clients — be they email, iMessage/SMS, or social — so that you get more signal than noise. The reality is that everyone sends you all of their messages all of the time. Unless you filter communication aggressively, split your contact lists into “family”, “friends”, “acquaintances”, and “block that”, you’re going to have a hard time finding the zen of messaging.

photo by

The Unrestricted Inbox is No Fun

The irony of messaging as a category is that as it gets more popular it gets more awful (thanks Nir Eyal for this visual of Message Hell). Yet almost every app and remote communication method needs messaging, because messaging solves the problem of communicating 1:1 (or 1:many) when we are all not physically in the same place and need to respond to each other. We all want the (algorithmically-delivered or not) perfect signal of “need to know” and “just in time” messages while also wanting desperately to avoid the inverse: “crying wolf while seemingly urgent and important”, “informational but not urgent”, or just plain spam.

photo by

But Blocking Email is Not A Solution

What will we do to keep the best parts of messaging across clients and channels and remake the part we don’t like that causes inefficiency, anger, and frustration?

Clay Shirky, in the well-known talk above (watch it if you’ve never seen it before), talks of “filter failure” and poses that as an antidote to information overload. However, that talk was several years ago. Things have gottne a lot worse with the volume and speed of information since then.

A Modest Proposal

Here’s the problem as I see it — we have information overload and filter failure. Some of this is bacn — “email you want but not right now”, and we have spam (we all know what that looks like). We have communication from different groups: home, family, work, social, and commercial communications. And we have the very real problem of multiple identity disorder, because there is no universal namespace for messaging someone that would create a “phone number” for all communications.

Photo by

Most people would say, “I’m not sure I like this but this is sort of fine, because the idea of a universal mailing address sounds even worse.” The whole purpose of messaging, they might say, is “to have varying degrees of anonymity and intimacy based on the level of familiarity and trust you have with the individual who’s contacting you.”

The Typical Answer: Don’t Cross The Streams

This “trust” issue is the crux of the problem we face when we want more signal and less noise in our messaging and in our communication in general. We all have internal business rules we use to govern how we respond to different types of messages.

Whether we have enumerated these “rules” or not, they might look like:

  • “Answer the phone call on the second or third ring when my spouse or partner calls”
  • “Text my friend in an hour if I’m busy, or immediately if we are in the process of meeting for coffee or a meal”
  • “Ignore that spammy message from someone or some business I don’t know.”
  • “Never look at LinkedIn connection requests (ok, I kid — but this might be a special category for a segment of the population).”

Get More Quiet, Based on Our Actions

Our messaging apps and messaging platforms in general do a poor job of interpreting our own behavior and in translating that behavior (and future, intended behavior) into human-readable business rules that govern apps and give us more signal than noise.

We don’t live in a utopian (or dystopian, depending on your worldview) future when we have universal messaging or aggregate delivery of messages to a single client or brain box and a system to rules to respond automatically or manually to those messages. But given the overall desire to reduce noise and increase signal in the messaging conversations we do have, I propose the following suggestions:

  1. Turn off notifications on your phone or tablet. This seems like a no-brainer but the struggle to fight “notification creep” is real. It only takes a few app-created nudges to generate a storm of messages you don’t need or want, generated by app developers and not by your own actions.
  2. Unsubscribe from information you don’t need or want. Try Unroll and Sanebox to clean up your email — future you will thank you.
  3. Aggressively filter the information you get. Your mileage may vary depending upon your style, so this might mean uninstalling apps, unfriending certain people, using email filtering rules, or just not looking at your devices so often.
  4. Use text messages and iMessages to maintain ongoing, single-threaded conversations to the people who matter to you. What’s better than email? Having only one conversation to respond to, stacked in chronological order. If that person is on your list (let’s say … in your top 25 people), they should either leave that list by falling below a threshold or you will have a clear signal that you need to reach out to them because they’re not at the top of your list.
  5. Think about simple rules and habits that make your life better. When you encounter product managers and other people who work on products and services, be sure to tell them what’s working and what’s not working in the products you use. (Hint: they would like to know what regular people feel.)

What could product managers and developers do to help with the message problem? A great start would be more levers and dials to adjust how we receive messaging. Don’t worry — I’m not suggesting that we create Advanced Settings Panels everywhere — but rather that the products themselves observe and respond to a series of behaviors derived from passive activity and active activity. Passive in this case might mean the messages I don’t respond to, and active could mean the messages I do respond to or arrange into folders or lists. The goal should be to develop a personalized set of rules that will automatically deliver message Air Traffic Control to the average user, not the power user.

What about Ads?

Building a personalized set of messaging rules will make easier to present promoted content in a clear and consistent manner, penalize spam, and highlight the important messages I’d like form the people that matter most. It could be an elusive goal, but I believe that improving messaging incrementally has amazing potential to increase happiness and productivity.The popularity of messaging need not cause its antithesis by creating messages we hate. We should be building new and clearer ways to ensure the right information gets to the right people at the right time, on the right communication channel.

(this post also appeared on Medium)

Standing Desk

Balance is a tricky skill.

Balance is a tricky skill.

If you’re like many people you balance easily. Riding a bicycle was an early thing for you. Likewise trying a skateboard or balance beam. Maybe you even are clever enough to use a unicycle or a slackline.

Not me — I’ve always been a little off kilter. I didn’t really notice it when it took me until I was 10 to ride a bike. I have trouble reading when cars are in motion — instant motion sickness. I hate being the passenger in a car unless I have something else to distract me (music). Continue reading