Reorganizing software licenses

As part of the slow process of moving myself away from 1Password to iCloud Keychain, last week I exported the list of software licenses and put them in a database. This took more time than I expected, as I tried out different ways to organize the list.

I have, by the way, nothing against 1Password. It’s a great program and has been a big help to me for many years. But as I try to simplify my computing setup, it seemed best to avoid having a separate program that, as I use it, largely replicates a function of the operating system. Especially when that separate program has moved to a subscription service—I’d rather use that money to pay for software that’s substantially different from what Apple provides.

One of 1Password’s functions that isn’t in iCloud Keychain is the list of software licenses.

1Password software licenses

There are a couple of ways to get the license data out of 1Password:

I started out doing it the iOS way and quickly decided the Mac way would take less time for the several dozen licenses I needed to export. Whichever method you choose, you won’t be pleased with how much fiddling you have to do.

With the data in a Numbers spreadsheet, I figured there were three options:

  1. Just keep it in a spreadsheet. That’s not a bad format for this kind of data, and I could make it available on all my devices by saving it to iCloud Drive.1
  2. Export the spreadsheet out to a CSV file and import that into a database. Airtable would be my choice, as it looks good and works across platforms. I’m not entirely sold on its security2, but software licenses don’t strike me as especially damaging if they get hacked.
  3. Export the spreadsheet to CSV and then turn it into formatted text that can be kept in Notes. This is also available on all my devices and notes have the additional advantage of being lockable. This little Python script reads the CSV and formats it for a note:

    python:
     1:  #!/usr/bin/env python
     2:  
     3:  import csv
     4:  
     5:  print("Software licenses\n")
     6:  with open('1p.csv') as csvfile:
     7:    reader = csv.DictReader(csvfile)
     8:    for row in reader:
     9:      print(row['app'])
    10:      for f in reader.fieldnames[1:]:
    11:        if row[f]:
    12:          print("{}: {}".format(f, row[f]))
    13:      print()
    14:         
    

    It opens the CSV file named 1p.csv, and writes out every record in its own paragraph. The first line is the name of the app, and the subsequent lines are all the other fields in

    key: value
    

    format. After importing to Notes, it looks like this:

    Software licenses in Notes

    The disadvantage of using Notes is that I have to type everything in myself when adding a new entry. Also, I can’t just add it to the bottom if I want the list to stay alphabetized.

Although I’ve done all three of these to see how they work, I think I’ll stick with the Airtable solution. It’s easier to scroll through than Notes and has a single-entry view that Numbers doesn’t provide, which is especially convenient when using an iPhone.

Single entry in Airtable


  1. And password protect it, too. Thanks to Teddy Svoronos for reminding me. 

  2. No particular reason for this; I’m just generally skeptical of cloud security, especially with things I’m not paying for. 


Airtable

I first heard of the Airtable database about a week ago from this Twitter thread involving Christina Warren, Ed Cormany, T.J. Luoma, and Rosemary Orchard. Already it’s my primary way of accessing my main projects database from work.

Airtable is a cloud-hosted database1 that can be accessed through a web interface, an iOS app, or programmatically through an API. I haven’t used the web interface much, but the iOS app is very well designed and the API is a wonder of clarity.

The database I moved into Airtable is a table of projects at work that I’ve been using in one form or another for over 20 years. It’s basically a list of jobs, storing the following information for each job:

For many years this was just a plain text file, one record per line and the fields separated by pipe (|) characters. A small set of Perl scripts were used to add and extract information. Then I imported it into a SQLite database and rewrote the scripts in Python using the sqlite3 library.

As I found myself wanting to search the database on my phone and iPad, I moved the database file to iCloud Drive and wrote some scripts in Pythonista to do the searching. This worked, but it was a little clumsy and the results, while correct, were ugly. I didn’t really want to put in the effort to make the output prettier, but I was resigning myself to get down to it when Airtable appeared in my Twitter feed.

It took basically no time to export the data from SQLite and import it into Airtable. Now I have a nice looking view of the project information (well, it’s nice looking when I haven’t scrambled the fields for anonymity).

Airtable database on iPhone

It’s easy to search by tapping the magnifying glass, but I often find the information I want (who’s the client on Project X? what project did I just open for Client Y?) sitting right in front of me when I open the app. The other fields can be seen by scrolling to the right.

When I first imported the data, all the fields were treated as either text or numbers. Airtable has a variety of other field types that can specialize these two generic types:

Airtable field types

Within the Number type, you can choose between integer and floating point, which can be quite handy.

To reduce the clutter, I have a filter that shows only the latest projects.

Airtable filter

And I sort them by number in inverse order because the projects I’m most likely to be looking for are the most recent.

Airtable sort

The API is easy to understand and has possibly the best documentation I’ve ever seen. The docs are formed with examples that are specific to your database; they include your fields and your records, not some made-up example that means nothing to you.

Airtable API example

Airtable doesn’t provide a Python library, but it’s easy to use the API through the Requests and JSON libraries:

python:
 1:  # Add record to Airtable database.
 2:  airURL = 'https://api.airtable.com/v0/ABC987654321/projects'
 3:  airHeaders = {
 4:    'Authorization': 'Bearer 12345ABCDE',
 5:    'Content-Type': 'application/json'}
 6:  payload = {'fields': {
 7:    'name': 'New Project,
 8:    'number': 9999,
 9:    'client': 'Jane Q. Client',
10:    'clientref': 'JQC1234',
11:    'directory': 'new.project',
12:    'box': 0,
13:    'email': 'jane@company.com'}}
14:  r = requests.post(airURL, headers=airHeaders, data=json.dumps(payload))

The identification code in the URL is specific to the database, and the authorization code that goes in the header is specific to the user. The JSON library’s dumps function takes a Python structure—in this case a nested dictionary—and turns it into a JSON string for POSTing. The response is stored in the r variable, from which we can extract the JSON data that tells us whether the request was a success.

For the small data sets I typically work with, the free version of Airtable, which limits you to 1200 records per database, is sufficient. I’m a little leery of using free services for job-critical data—I don’t want something I rely no to end up with the dreaded sunsetting post on Medium—but Airtable seems to be well funded for now. Also, I’m still keeping and updating the old SQLite database for safety.

With my first Airtable database under my belt, I’m eager to add more. Airtable markets itself for teams (because everything markets itself for teams nowadays), but don’t let that stop you from using it for personal databases. I have a few inventories for both work and home that could really use the structure of a database behind them.


  1. They say it’s “part spreadsheet, part database,” but aren’t all databases part spreadsheet (and all spreadsheets part database)? 


Tweeting App Store links via Shortcuts

The extra long framed screenshots in yesterday’s post drew some puzzled tweets. They were created with PicSew, which stitches together overlapping screenshots into a single long image and can, if you want, frame the image with the outline of an iPhone. I don’t know if I’ll continue to add the frame, but it’s kind of fun.

After answering the questions about PicSew, I thought it would be nice to have a shortcut that would automatically assemble a tweet with an app’s name, icon, and App Store link. So I combined the general outline of this shortcut for tweeting Kindle book links with the parts of yesterday’s shortcut that handle app names and icons and ended up with this:

Tweet App shortcut

You can build it from scratch or just download it.

To use the shortcut, you start on the App Store page of the app you want to tweet. Bring up the share sheet, choose Shortcuts, and select the Tweet App shortcut. You’ll then be presented with a tweet ready for sending.

App tweet ready for editing or sending

By default, the text portion of the tweet contains the name of the app and the developer, followed by the App Store link. You can edit the tweet before sending.

With these last two shortcuts ready to go, I guess I should start blogging more about apps.


Extended app icon shortcut

I was scrolling through Federico Viticci’s shortcuts library the other day, looking for something to steal, when I came across this shortcut for finding and saving icons from the App Store. It’s the kind of thing that can be very helpful when writing a blog post about an app, so I installed it. I soon decided that it would work better for me with a little tweaking.

Here’s how Federico’s shortcut works: It asks you for a search term and returns the top five hits from the App Store. You select the one you want (or cancel and try again if the search didn’t work) and a 512×512 PNG of the app’s icon appears in your Photos app. In a nice touch, the corners of the icon are rounded off to appear as they would on your home screen.

You can imagine how often this shortcut gets used by the industrial-strength app reviewing team at MacStories, but even a small-timer like me can make use of it. But to automate the process further, I wanted the shortcut to upload the image to my server and delete the local copy from my iOS device. Also, since I’m usually in the App Store at some point when I’m researching or writing a post about an app, it seemed more useful to my work habits to have the shortcut start from the share sheet instead of a search.

Here’s how my Upload App Icon shortcut works:

  1. Go to the App Store entry for the app of interest.
  2. Tap the three-dots button near the upper left corner of the page and choose Share App… from the popup menu. This will bring up the standard share sheet.

    Sharing from the App Store

  3. Choose Shortcuts from the share sheet and select the Upload App Icon shortcut.
  4. Enter a description for the image. The default description is “App icon for app name,” which is what I want most of the time.

    Default description of uploaded icon

  5. Decide whether to delete the icon image from Photos.

    Ask to delete local icon image

The image is now on my blog’s server, in a folder (for the above example) named “images2018” with the name “20181121-App icon for Annotable.png.” The year in the folder name and the date prefix in the file name are handled by the shortcut.

Here’s the shortcut itself, with the server information anonymized.

Upload App Icon shortcut

I think I’ve reached (or maybe exceeded) the limit of shortcut length that can be comfortably displayed here. If you want to adapt the shortcut for your own use, download it and edit away.

The first five steps (not counting comments) are taken straight from Federico’s shortcut. They get the icon’s URL, download the image, mask it to round off the corners, and save it to Photos.

The next three steps handle the file extension of the saved image, making it all lower case and changing “jpeg” to “jpg.” The latter is probably unnecessary, as icons are (I think) always PNGs, but better safe than sorry. We’ll use the extension later to set the name of the uploaded image and to decide whether it’s necessary to run an image compression command.

The following four steps get the date strings that will be used to set the folder and the prefix of the image name. They’re taken from this shortcut I wrote a couple of months ago.

The next five steps set the name for the uploaded file. This starts by getting the app’s name and using regular expressions to extract just the part before the first colon or hyphen. Many apps, like Annotable, include descriptive bits in their name to make them easier to find. The Match and Replace regexes get rid of that stuff. The user is then asked to provide the description, with the default as described above. Finally, the full name of the uploaded file is assembled from the date, the description, and the extension.

The next eight steps upload the image and, if necessary, run the OptiPNG image compression command. These steps are also mainly lifted from my earlier screenshot uploading shortcut.

Finally, the last two steps delete the icon from Photos. The Delete Photos step automatically gives the user the option to cancel the deletion, so there was no need to write any of that code.

Sharp-eyed readers will notice that this shortcut includes no Set Variable steps. It’s possible that I’ve finally figured out how to use Shortcuts’s magic variables. One feature that makes them easier to use, and the resulting code easier to understand, is renaming. After choosing a magic variable, you can tap on it to reveal several options for the information you can extract. This is like a dictionary in Python, a hash in Perl, or a record in AppleScript.

Magic variable info

As important as the ability to pull out different information is the little Rename button at the top left. This allows you to give the magic variables that appear in your shortcut a more descriptive name than the default—especially helpful when you have more than one magic variable called “Text” or “Match Results.”

Rename magic variable

Update Nov 22, 2018 6:41 AM
If you download the shortcut now, you’ll get a version that’s been considerably simplified, partially through suggestions from Federico and partially through my own exploration and testing.

The simplifications are as follows:

  • It gets the icon directly as an app property instead of getting the icon’s URL and then downloading it.
  • It no longer saves the icon to Photos and therefore doesn’t have to delete the image at the end. When it’s time to upload, it accesses the image via the Masked Image magic variable.
  • It no longer includes provisions for JPEG images. I’m pretty sure icon artwork is always in PNG format.

Here are the steps of the new streamlined version.

Upload App Icon shortcut