Build a snippet to fill in spreadsheet headers using a single in-sheet formula

TextExpander Talk
1

If like me you’ve had to do a fair bit of data wrangling in your lifetime, chances are you get given csv files that have the column headers missing. Now, if this happens on a regular basis for similarly structured data, scrabbling around to find the appropriate headers in another Excel file or Google Sheet can become a real chore.

As Textexpander can “tab across,” the cells, entering the headers as it goes, it makes sense to get Textexpander to do this. It makes even more sense to use a formula to magically create the text we need to drop into a snippet, especially if there are many headers. Here is that formula…

Google Sheets

=JOIN("%key:tab%", FILTER(INDIRECT("A1:" & ADDRESS(1, COLUMN()-1)), INDIRECT("A1:" & ADDRESS(1, COLUMN()-1))<>""))

Microsoft Excel

=TEXTJOIN("%key:tab%", TRUE, FILTER(A1:INDEX(1:1, COLUMN()-1), A1:INDEX(1:1, COLUMN()-1)<>""))

Now, if we have say, “Beef”, “Chicken”, “Pork”, in cells A1, B1 and C1 respectively, dropping the appropriate formula into D1, will output…

Beef%key:tab%Chicken%key:tab%Pork

In that output, you will see we have the text %key:tab% which, back in the day, you could manually type into Textexpander to denote a press of tab key was required.

To get our Textexpander snippet to work as required, we have to jump through a couple of hoops…

  1. Paste the string,Beef%key:tab%Chicken%key:tab%Pork into a snippet with Content Type = Plain Text
  2. Next, switch the Content Type to JavaScript
  3. Expand the snippet via your abbreviation, and you’ll get JavaScript Error: SyntaxError - Unexpected token ':'
  4. Switch the Content Type of your snippet back to Plain Text and you’ll now see our column headers are delimited by the Tab lozenges

The snippet can now be used and will expand across our headings as required. It would obviously be great if we didn’t have to go through the merry dance to coerce the Tabs. But of course, if you only have a few columns for the snippet, you can manually build the snippet using Keyboard macros > Tab.

4 Likes
2

I agree. It would be great if TE had a “source” view that allowed us to type snippet strings or edit the code for them.

1 Like
3

I agree. I understand the “dumbing down,” approach to a degree, especially when the product has moved out of “nerds only,” territory, but it would make things a darn sight easier if there was an officially-sanctioned way to text string the keyboard commands as you could once do. Progress, hey?

1 Like
4

I have a workaround. I still use the discontinued iOS app, which doesn’t have the fancy UI and allows me to write the snippets as code. The snippets sync to the Mac and show up with the nice UI. (I have yet to try tab to see if syncs well to the Mac.)

5

I don’t know if this helps, but you can copy and paste the Tab bubbles.

copy-paste-tab

1 Like
6

Not really. You can’t do that in the spreadsheet formula.

7

The closest thing we have to editing a Snippet’s source is working with the JavaScript Content Type. I wonder if we could find a workaround to keep all of the functions in JavaScript? Something like:

var headers = ["Header1", "Header2", "Header3"];  // Replace with your headers
var output = headers.join("%key:tab%");
output;
1 Like