CodeSOD: Uniquely Enough Identifiers

Running and hosting a database is expensive. Not only do you need the server for it (even if you rent in the cloud), you also need the expertise to administer it. And that’s why Lucas ended up working on an application which used Google Sheets as its database.

Now, this was an application used by a marketing team to create new marketing campaigns, so Google Sheets wasn’t the worst choice made in the entire process. With only a handful of users and dozens of records, it was fine. You didn’t need to put a huge amount of effort or expertise into it- at least, that’s what management thought.

The application didn’t get the expertise it needed to work well, but “working badly” was sufficient for most cases.

Since each row needed a unique identifier, here is how they handled that:

function gerarIdComData() {
function gerarSequencia() {
return Math.random().toString(36).substring(2, 6);
}

var idAleatorio = gerarSequencia() + ‘-‘ + gerarSequencia() + ‘-‘ + gerarSequencia();
var agora = new Date();
var dataFormatada = agora.getFullYear() +
String(agora.getMonth() + 1).padStart(2, ‘0’) +
String(agora.getDate()).padStart(2, ‘0’) +
String(agora.getHours()).padStart(2, ‘0’) +
String(agora.getMinutes()).padStart(2, ‘0’) +
String(agora.getSeconds()).padStart(2, ‘0’);

return idAleatorio + dataFormatada;
}

So, we start with gerarSequencia, which generates a random number, converts it to a 36 character string, and then slices off a few characters. We call it a few times, throw a few dashes in the middle, and we’ve got an improvised UUID.

Then we do some mangling on date times (instead of using built-in date functions), to produce our ID: a combination of this mostly unique identifier and a timestamp.

Now, there are dozens of rows, so you might think this is probably safe enough (or maybe even think, “wait, couldn’t we just count and autoincrement if it’s truly that small?”), but we’re not done yet. Here’s how we use it:

var range = ws.getRange(1, 1, ws.getLastRow(), 1);
// var idsExistentes = range.getValues().flat();
var idsExistentes = range.getValues()
Logger.log(idsExistentes)
// var idsExistentes = range.flat();
Logger.log(idsExistentes)

var novoId;
do {
novoId = gerarIdComData();
} while (idsExistentes.includes(novoId));

We extract all the values in the ID column, log ’em twice, just for fun, and then start a loop: we keep generating IDs until we generate one that isn’t already in the spreadsheet.

This is an example of a problem that would have been better if it had just been a spreadsheet. Adding additional code made the whole thing more fragile and harder to maintain, confused the users, and gave us this… unique set of choices. It’s a unique inversion of Remy’s Law of Requirements Gathering (“No matter what your users say they want, what they really want is for you to give them Excel”), where… it probably should have just been a Google Sheet and just some instructions for how to use it correctly.

Source: Read More