Category: Productivity

A modest proposal for Slack Overload

Photo by Luis Villasmil on Unsplash

How do you keep up with these Slack communities that are out there?

For many of us, opening Slack creates an overwhelming feeling of “FOMO” (Fear of Missing Out). When all of the channels light up with notifications, you collapse the channel view, mute the channel, or just close Slack altogether. But wait! You’re missing out!

You could read everything. That’s not very effective, especially if you belong to multiple communities that post a lot.

What are some potential solutions to keep in touch with a community and avoid information overload? Closing Slack is one option and leaves you out of the loop. Setting up notifications to message you on certain topics is a blunt force instrument. Customizing the settings on each Slack community you join is too much work.

Why is Slack difficult to use? Here are a few reasons.

  • It all looks like a text “waterfall”. When you log in to a Slack community with lots of members, you often see many messages and need to choose whether to skip reading or “backscroll” and read them all.
  • What are people talking about? Because there’s no topic clustering, it’s hard to determine the larger themes in a group of messages. 
  • Channels have no inherent context. There may be a channel topic but people tend to talk about anything. The topic doesn’t limit the messaging or provide suggested guardrails.
  • Search is, at best, minimal. Good luck searching that message you remembered from a few days ago. If you limit your search to a particular channel, you will also limit your ability to search for it elsewhere.
  • All Slack instances look the same. The good news is that if you’ve used Slack before, you know how to use Slack. The bad news is that you might not remember which instance you’re using. The only clear differentiator by default is the logo, the url, and the channel names. This can get even more confusing if you know people in multiple slack communities and communicate with them in more than one place.

The cognitive overflow of using multiple channels and messaging services is real. So what could we do to make communities like Slack easier to use?

Solutions to this problem would increase the signal for interesting messages but boosting them somehow in your feed. It would also be great if Slack made it easier to find relevant information and related topics to your posts.

Slack has the API to make this happen. What could we address with a bot that aggregated information to help message overload?

Here are a few suggestions to make community involvement more effective in a noisy Slack community. These solutions could be addressed with an aggregator bot in Slack – I’m not as familiar with technology in other community services like WhatsApp or Microsoft Teams, but don’t think they are quite as open as Slack’s API.

I’d like to solve these problems that occur in busy communities:

  • What were the active conversations I missed? A feature might review conversations and Identify the “most active conversations” in a time period from the channels where you are a member, linking you the top 3 in the last 24 hours or a larger time frame if the community is quiet.
  • What did these conversations reference? Identifying the relevant topics from active conversations would be very useful. Also, it would neat to have a topic map of all of the public channel conversations in a community. Think of what Roam Research is doing to identify entities in individual documents and you’ll have an idea of what you could do to auto-tag conversations or channels with topics.
  • Is the conversation changing over time, and how are the relevant topics changing? If you tracked the most active topics in conversations and channels over time you’ll have a map of topic change in a channel and start to see the things that matter to a community
  • How do I find a complicated answer without using exact text, like “when was the last time we talked about pricing” – mapping topics will help search by adding context to the conversation
  • Is there a way to flag information for curation in real time? If you have a simple bot that is a member of of a public channel, you could use emoji signalling to mark “this post is great” or “this post is making me angry” or simply analyze the map of reactions that happen to a post
  • Can I make search work better? Because search is fragmented, use the user’s credentials to search multiple channels and return by active conversation or by topic map
  • What happened this week? Because all instances look the same, make it memorable. Send an opt-in daily or weekly digest with a way to the day’s or week’s news. If you gave admins the ability to add content to the summary before it’s sent, this would give you another place to curate news.

Slack isn’t the only place where this happens, and it is one of the most accessible to automate. 

Here are a few teams working on this problem today (August 2020)

  • Get Lowdown is building daily digests of Slack;
  • Obsidian is creating a local graph database for notetaking and building a plug-in architecture for other apps as well;
  • Roam Research is creating auto-linking discovery and entity matching based on your notetaking; 
  • Scorebot is a Slackbot that measures and tallys emoji reactions to posts using a point total 
0

Look up a list of items and return matches in Google Sheets

Anyone who’s spent time in spreadsheets dreads receiving that request to match a random list of things with another of list of things. Right? Except when you are used to using VLOOKUP. That tried-and-true method is very good at finding a match in a list against a another (usually longer) list.

But what happens when you need to do multiple VLOOKUP-like requests at the same time, say when you have a list of Student IDs and need to look up a list of corresponding names?

Wouldn’t it be great if you could do that in only a single step instead of multiple columns, tabs, and a long process?

Luckily, there’s a trick you can use in Google Sheets to take those multiple lookups and turn them into a single formula.

TL;dr – Matching each item in a list against another list

the answer in case you don’t want to keep reading – get the Google Sheets example here.

Why does this matter, anyway?

Looking up a list one item at a time is slow. Looking up all of the terms in a list is really useful, especially when you have a long list of IDs and a long list of values that might or might not match that list. Also, this method works for comma-delimited lists of different lengths in adjacent rows (e.g. cell 1 listing attendance for course 1 has two students, and cell 2 listing attendance for course 2 has five students, and only two of the students overlap both courses.)

WHATWHY
Look up a list of ID values in Google Sheets, returning a new comma-delimited string with the results of your lookupTransform a list in a single step

Recipe for building our formula

Given a comma delimited list of items you want to look up where the mapped value is not obvious, e.g. “ID123,ID456,ID567”, look each item up against a table of other information about that item where the lookup key is each item in the first list.

For example, you might have a list of Unique IDs and want to find out and return the corresponding mapped value, e.g. the name of people in a class. To do this you need to take the peopleID and return a name.

Each item in the list is the key you want to lookup in another list. One way to do this in multiple steps is to use VLOOKUP where you would run a vlookup to get a different value, e.g. to find that ID123 has the student name “Tony Schmidt”.

To do this lookup, you need to use the QUERY function in Google sheets to match multiple simultaneous conditions.

Using the matches keyword in QUERY, transform your comma-delimited list into a pipe-delimited list, then transpose and join the result to get a returned comma-delimited string from your QUERY function.

(fun fact: the QUERY function returns an Array which makes this possible.)

How does the formula work?

First, get the list items from our original list into a format that QUERY understands. The QUERY function in Google Sheets uses the term “matches” to look up one or more terms in the QUERY action. Using the the SPLIT function, we find each item in the comma-delimited list and then JOIN to replace commas with a “|” character, indicating item 1 OR item 2 so that the QUERY uses multiple terms to match.

After transforming our original list into a pipe-delimited list, we write a QUERY against the list we’re trying to match.

The goal is to select a term from column A (the one in the list with the keys matching our original list) with the corresponding value we want from column B (the ID value for each list item).

This table contains the steps to transform our original list into the comma-delimited result of looking up each term

What does it look like when you’re done?

When finished, we took the original list of ID123, ID456, ID457 and turned into the keys (names) that matched these ids in our lookup table:

  • ID123 matches “Tony Schmidt”
  • ID456 matches “Leroy Kuhn”
  • ID567 matches “Connie Wright”

Concatenating these and returning them yields the result “Tony Schmidt, Leroy Kuhn, Connie Wright.” If you wanted to make this fancier, you could use a string e.g. 

="STUDENT NAMES:" & CHAR(10) & join(", ",transpose(query(Sheet1!A2:B3,"Select B where A matches '"&join("|",split(A2,","))&"'",0)))

To format your string further:

STUDENT NAMES:
Tony Schmidt, Leroy Kuhn, Connie Wright

Using the “&” to concatenate a string and the CHAR function to add a line break.

Now that you’ve seen the steps, go ahead and copy this original sheet to try it yourself. Happy Matching!

0

Applying Regular Expressions to Sort Items in Google Sheets

Lists are more useful when grouped.

When you need to analyze a large list of items, you need to create areas of commonality to enable grouping and sorting that list. A simple example of grouping might be to think about a grocery list, where certain items (bananas, oranges) are fruits and other items (paper plates, napkins) are non-perishable items that need to be managed differently. Short lists of a few items are easy to categorize; longer items take an effort to sort and manage the data; and large lists (>200 items) might require a programmatic approach.

Fortunately, spreadsheets are excellent for a large, boring sort that might drive you a bit nuts if you tried to do it by hand. Spreadsheets require a logical framework to decide exactly how to organize your items into categories, especially if you have multiple criteria. To organize your items, you might look for an obvious sort (alphabetical) or count individual things, but you are more likely trying to create a grouping that’s not obvious.

RegEx can help (really)

Regular expressions are a computer-geeky way of writing a pattern for text that you want to match, and provide a TRUE or FALSE result to your expression. This code is amazing and flexible, and also a bit hard to master (check out https://regexr.com/ if you want a handy playground to try them out). You use Regular Expressions when you want to apply the same test to multiple items, e.g. finding the domain name in an email, or retrieving a portion of text from a URL.

Using a logical test and validating it in Regular Expressions allows you to take an abstract problem (find the senior leaders in a list of titles) that’s easily understood by humans but needs instructions for computers to execute. This all sounds logical, but searching for Google Sheets RegEx formulas might leave you feeling like adding more than one condition at a time is a bit … challenging. So I decided to simplify that process.

Here’s a concrete example of applying multiple true/false conditions to a single problem. If you’ve built prospect lists for outbound campaigns, there’s one thing that almost always happens: you have to “bucket” the titles by groups to decide what to do next. You might manually decide that titles starting with CEO or “Chief” are C-level, and that ones starting with VP are VP-level, but it’s kind of a drag to do this manually. Wouldn’t it be easier to define the filters, then apply the true/false results of each filter to all of your titles at once?

A quick tool to help sort lists

I decided to build a small tool in Google Sheets to model using multiple filters to test the ability to arrange job titles into families. To build this utility, I used Google Sheets to list the testName I wanted to establish in each row, followed by an example title, a Regular Expression condition I wanted to match, and the result of TRUE or FALSE for that test. I also created a column to help you understand which part of the string/title matched your condition.

Several rows of tests, displaying the regular expression used to evaluate the test

The initial list has four tests, where the simplest is:

(?i)(mgr|manager)

Or – in English – “match everything that has either the string “mgr” or the string “manager”, in the source string, and don’t worry about whether it’s upper or lower case.”

These tests each evaluate a job title, determining what seniority it lives in based on a keyword or words in the title. I intended this rule stack evaluate from the top to the bottom, so you would evaluate the first rule, then stop if you get TRUE, then move on to the next rule. If you prefer, you can keep going even when you have multiple TRUE conditions as it will help you understand the distribution of your list between buckets.

A few titles, displaying the results of the testing run and the item that matched

Here’s how this works:

  1. Taking the title text from the “Example Title” column,
  2. We use the header column to look up the row of the test we want to execute. For isManager level, we would use a formula to look at the column labeled Test Name in the other tab, do a vLookup to find the row matching isManager, then read the expression in the Condition/Regex column, and return a TRUE/FALSE result using the REGEXMATCH formula, e.g. =REGEXMATCH(“Sr Manager of Spreadsheets”,”(?i)(mgr|manager)”)
  3. use conditional formatting to alert a TRUE result;
  4. and reverse the formula using a REGEXEXTRACT to identify the portion of the string that matched first.

Ok, so what?

Yes, you could apply these formulas one at a time or use complicated sorting methods to find boolean conditions that are true or false in your data set. This method is helpful because it allows you to flexibly add conditions (add a row in the RegEx Tester sheet; and a column in the ProspectTitles sheet labeled with that row’s test name). It also allows you to change your RegEx conditions to match your data set by updating a single field for each change you want to make in your expression across as many items as you want.

This method works well and quickly for evaluating several fields against a set of 10k records or fewer. As you think about longer lists that involve updating more than 50k cells at a time, you might combine your tests or use small portions of lists at a time to increase the speed (that’s a lot of VLOOKUPs 😉

Here’s a link to the example – make a copy and give it a try.

0

Spreadsheets are not scary

xkcd-spreadsheet
https://xkcd.com/1906/

What was the last document you created? I’m guessing it was either an email, a Word document, or a Google Doc. Perhaps it was a Slack message but since when you use Slack you create several hundred messages a month (or day), so let’s not count that. Why wasn’t it a spreadsheet (Excel or Google Sheets)?

The most likely reason you didn’t create a spreadsheet is that you wanted the freedom of an open page (hint: View > gridlines turns them off in Google Sheets). Maybe you wanted to insert pictures (hint: use the Image function or insert > image). Or maybe you just like the formatting that seems to happen almost automatically in Cloud-based apps these days. But really, why didn’t you start with a spreadsheet?

Spreadsheets are structured. True, they end up looking that way when they are done. Why not use a tab in your spreadsheet to test an idea (growth in a metric over time, adjusted by factors that change) and produce a sensitivity table to help illustrate a business challenge you are facing. When you are thinking about taking a metric from x to y by when (improve yield by 40% in 30 days) it’s important to show the impact of meeting or missing your expectations.

A suggestion: start with a written statement of what you are trying to solve, e.g. “produce a month over month forecast for the sales team based on a certain number of sales per month and an average sale of $x.” Using the problem statement will frame how you set up your data – you need to have available data series for the problem that match your problem – and start you on the way to presenting the “dashboard” version of your solution. Keep it simple to start by labeling cells that affect others as assumptions, like “sales increase per month.”

Spreadsheets are structured like modular pieces to assemble into a larger whole. Start with the smallest item in your model or argument and test it, then use either a Named Range or a consistently placed cell (e.g. all of your calculations are in a single tab in the same column) to organize where you will find the results. Spreadsheets are more flexible than you think.

Those formulas are hard to copy from one tab to another. Anyone who has ever worked on a spreadsheet has had the experience of copying or changing the data and seeing the dreaded #N/A or #VALUE or #REF show up in place of the data or calculation that they wanted. (And if you have no idea what I am talking about, open a spreadsheet tab in sheets.google.com, enter a formula e.g.  =A1+A2 and then break the formula by putting in nonsense characters).

To test your ability to copy data from one tab to another, create a new tab, remove the gridlines for that tab, and title it “Dashboard”. If you can pull the data from the other tab (hint: just use a simple reference, e.g. “=YourOtherSheet!A2” to pull data from a tab called “YourOtherSheet” in cell A2) you can apply things like formatting and font choice to make your dashboard look like a document, not just a spreadsheet.

This method of data construction and display just plain feels weird. Building data-driven dashboards is a method to insert data conversations in your documents. The data needs to first exist in the format (or in a format that’s convertible to the format you need) and then you need to think about how to combine it. And you need to think of the visual display at the same time to make this a useful technique.

Spreadsheets aren’t scary if you think of the presentation layer as a sketch. You might even draw what you want to see on a Post-It note or find a dashboard that you like on the web and take a screen shot as a starting point. Then, consider what kind of data goes into the boxes and graphs to understand what you need to store in the data layer. Still confused, or want to learn more? Ben Collins is my go-to resource to explain Google Sheets to anyone. The best way to learn spreadsheets, however, is to start one.

 

 

0

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

google-list

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 ”):

join

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.

regex

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.

0

Blog at WordPress.com.

Up ↑