November 21, 2019

Installing applications on a new Mac using Brewfiles

My first Mac — the 2013 MacBook Pro — is still alive. Putting aside the occasional service battery” warning, it’s actually going pretty strong. This machine has far exceeded my expectations in terms of durability and longevity.

Soon enough, though, I will purchase a new machine; either because this one dies, or because Apple finally releases a promising replacement (read: no keyboard issues).

Naturally, I had been looking for a way to automate what I consider the most dreaded part of migrations: reinstalling macOS applications, packages, and command line utilities.

I wrote about macOS migrations with Homebrew and Brewfiles on Open Folder. I estimate it shaves off 95% of the time (and effort) it takes to install applications on a new Mac.

Yes, it involves using the Terminal, but I’ve seen iOS shortcuts more complicated than that.

Share: Facebook · Twitter · Email

Email This Page

June 16, 2018

Let them drag & drop

A common annoyance with Google Sheets is that drag & drop can potentially mess up a lot of things. This is because changing the location of cells by dragging them somewhere else also changes the reference to those cells in formulas.

A simple, easily solvable example: If cell D1 is supposed to hold the formula =SUM(B3:B4), and we then select and drag those two cells down, the formula in cell D1 will now say =SUM(B5:B6). Sometimes this is the desired result, other times it isn’t, and if it’s the latter case, it’s easy enough to fix by using INDIRECT: =SUM(INDIRECT('B3:B4')).

But here’s a more complicated scenario: you are one of many collaborators on a protected sheet, of which you are only permitted to edit a certain range: B2:C20. In other words, this is the only unprotected range in the sheet.

Everything is cool. Unless you decide to drag the first cell in the range. Here’s a short video I’ve created to demonstrate this:

Notice how when you try to drag the two rows selected from row 3 back to row 2, Sheets throws an error. This is because you are now no longer allowed to edit row 2 — the new unprotected range is B3:C20. You are effectively locked out of a range you are supposed and were originally permitted by the owner to edit. Since you are only a collaborator, you can’t simply go to the protected ranges” menu and reconfigure them.

This is how all spreadsheets work, it isn’t specific to Sheets. And still, it seems like the wrong behavior from a user-experience standpoint: all cells that were manipulated in the example were within the unprotected range. It’s not like we dragged a protected cell into the range. I also believe that with protected sheets, only the owner should dictate which cells remain unprotected. But Sheets not only allows collaborators to lock themselves out of cells, it allows them to lock out other collaborators too.

So as the maintainer and owner of more than a dozen spreadsheets used in my company for mission-critical tasks, I set out to find a solution.

Telling tens people to use copy-paste instead of dragging wasn’t an option. You don’t count on humans to not do things they’re used to doing. Using INDIRECT as is possible with formulas was also out of question because Google doesn’t support it when dealing with ranges.

Since we can’t really prevent collaborators from locking themselves out of their own ranges, we need a way to verify, and if needed, change the sheet’s (un)protected ranges to what we intend them to be.

As in many cases, what isn’t possible in the interface can be achieved quite easily with JavaScript in the script editor:

// protect the sheet, add a description
var protection = sheet.protect().setDescription("Protection Validator");
// select the range we want to un-protect  
var unprotected = sheet.getRange('B2:C20');
// remove the protection from the range
protection.setUnprotectedRanges([unprotected]);

This code is set to run whenever a change is made to the spreadsheet. No matter how people misuse” it, protected ranges are always re-set to the desired result. They get to use drag and drop, and I get less phone calls.

Share: Facebook · Twitter · Email

Email This Page

February 3, 2018

On apps that use fake progress bars

A thought-provoking discussion ensues on Hacker News regarding the practice of displaying fake progress bars in web services and apps. Opinions vary on whether these are indeed benevolent deceptions”, as the linked-article suggests, or not. Still haven’t made up my mind.

Share: Facebook · Twitter · Email

Email This Page

December 10, 2017

Running a local script “from” Google Sheets

It’s been two years since I started learning Python. Incidentally, or not, it has also been two years since I last posted something on here. It’s been a fun ride and I’ve now got several projects going on: web-scrapers and APIs for personal use, automation scripts, and one nice but practically useless predictive model utilizing machine learning.

Challenging subjects like ML are fun to practice, but nothing beats automation in my world.

For a recent project, the need was to run some local script whenever a button is clicked in a Google Sheets spreadsheet. Each script automates a specific After Effects composition, based on data in its respective spreadsheet. So we have sheet-script pairs, one button per sheet, and many users interacting with different sheets.

Google makes it fairly easy to manipulate Drive files based on certain events. Apps Script runs on their own cloud platform and can do pretty much anything a human can with Google’s own services. Invoking a locally hosted script isn’t one of them though.

Generalizing the problem helped pave the way: what we needed was to notify the machine, in realtime, of an event taking place on the web. Once that’s done, executing the appropriate script is straightforward.

There are two ways to make a computer aware of remote events: we either have it pull them periodically from the sender, or we have the sender push each event to the machine. The pull approach is simpler, but wouldn’t have worked here: it’s possible to write a script that pings Google’s servers every second, but that’s also a surefire way to go over the APIs quota.

So we need the sender to push the notification, something Google’s platform doesn’t provide out of the box. But actually, the only thing a Google script can push out of the box is even better: a filesystem event. That is, if Google Drive is synced as a local folder, changes in this folder are normal file events as far as the operating system is concerned, and these are among the easiest to monitor. Now the question becomes how it is best to do that.1

For our use-case, I decided to create a timestamped JSON file each time there’s a button event. It sure would’ve been nice to do the tracking in one file, but with multiple users and sheets, such design would create a substantial risk of race-conditions.2

Here’s where I complicate things a bit: Since I already had Dropbox installed, I decided to save the JSON files there, and not in Google Drive. This changes the implementation, but not the underlying concept.

So, each time one of the users clicks a button, a Google script is invoked, creating a new file in the Dropbox subfolder json_jobs”:

### filename example: rhino-12-8-2017 10/32/30.json
{
  "processed": false,
  "script": "C:\\ae_folder\\ae_script_1.ps1",
  "generated_at": "12-8-2017 10:34:02AM",
  "delegator_spreadsheet": "spreadsheet_id",
}

Whenever a file like this is created, it’s grabbed and inspected by a Python script running in the background: this script then invokes the PowerShell script at the specified path, moves the resulting files to their final destination, and if all went good, changes the processed flag to True. This way retries won’t reprocess completed jobs.3

But that’s the end result. The very first step was to write the script that creates the JSON file and saves it to Dropbox. Here’s a slightly modified version of the main function, the one connected to the button-click event:

function main(spreadsheet_id) {
  var json_dict = fillDict(spreadsheet_id)
  var json_obj = convertDictToJSON(json_dict)
  sendJSONToDropbox(json_obj)
};

Connected” is a little misleading here: the file holding this code isn’t actually bound to any one spreadsheet. Instead, all spreadsheets involved import and utilize it as a library, passing their IDs whenever they call it. From there, we pass spreadsheet_id to a function that constructs a dictionary with the appropriate values, convert the dictionary to a JSON object, and send it to sendJSONToDropbox():

function sendJSONToDropbox(json_object) {
  var timestamp = timeStamp()
  var parameters = {
    "path": "/json_jobs/rhino" + "-" + timestamp + ".json",
    "mode": "add", // do not overwrite
    "autorename": true,
    "mute": false // notify Dropbox client
  };

var headers = { Content-Type”: application/octet-stream”, Authorization’: Bearer’ + dropbox_access_token’, Dropbox-API-Arg”: JSON.stringify(parameters) };

var options = { method”: POST, headers”: headers, payload”: json_object };

var API_url = https://content.dropboxapi.com/2/files/upload; var response = JSON.parse(UrlFetchApp.fetch(API_url, options).getContentText()); Logger.log(response)

};

And that’s one way to run a local script from Google Sheets.


  • Yes, we do end up listening to something, but polling the filesystem is overwhelmingly cheaper than polling Google’s servers. ↩︎

  • I haven’t found a way to directly append data to a Dropbox file using the API. ↩︎

  • This is one of several reasons to call the scripts indirectly via Python. The main reason: I didn’t write these scripts myself, and have no intentions whatsoever to learn PowerShell. ↩︎

Share: Facebook · Twitter · Email

Email This Page

April 19, 2017

London Police Spying on Journalists

From WSWS via Hacker News:

The existence of a secretive unit within London’s Metropolitan Police that uses hacking to illegally access the emails of hundreds of political campaigners and journalists has been revealed. At least two of the journalists work for the Guardian . Green Party representative in the British House of Lords, Jenny Jones, exposed the unit’s existence in an opinion piece in the Guardian. The facts she revealed are based on a letter written to her by a whistleblower.

In May 2015 it was unveiled that the UK government quietly rewrote its Computer Misuse Act to exclude police and intelligence agencies. I wrote about it here. Today we see the bastard child.

Share: Facebook · Twitter · Email

Email This Page

January 8, 2016

Paypal Freezes Startup’s Funds, Offers Loan

Hacker Paradise founder Casey Rosengren, on Medium:

On one hand, Paypal was saying that our account was too risky for them to release the $20,000 they’d taken as collateral. On the other hand, they were saying our account was credible enough to offer us a loan.

We chose to approach this in the spirit of Hanlon’s Razor: when in doubt, assume ignorance, not malice. So, we emailed a screenshot of the ad above to the startup evangelist, assuming it was some kind of clerical error.

Unfortunately, for customers outside the US there is no practical alternative to Paypal. I still use them for most of my recurring payments, with the occasional online purchase.

Share: Facebook · Twitter · Email

Email This Page