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

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
Subscribe: RSS · Newsletter · Twitter