Obfuscate text with Apps Script

Sometimes you want to hide data in a sheet that’s being used for demo purposes. In a previous post I covered encrypting selected data so that only people with access to its public key plus their own private key could access it (Merging sheets from multiple sources and encrypting selected columns), but that was about data you wanted to share securely. In this article, I’ll cover the much more simple requirement to obfuscate data so you can share it without having to revealing the original content, and of course provide a library to do it for you.

Options

There are various approaches to this – all of which have their pluses and minuses – here’s a few things you could replace the data you want obfuscated with

• A positional ID – not reproducable as it’ll depend on the order of the data.
• A uuid – these are guaranteed to be unique, but they are not based on the input data so not repeatable.
• A digest – these will almost certainly be unique, and are based on the input data – so are repeatable. However the format of long base64 or hex string make them difficult to reference or evenlook at.
• A random string – will probably be unique, especially if based on the time – but they are not derived from the input data so won’t be repeatable. Also likely to be fairly unpleasant to deal with unless you overlay an algorithm to transform them.
• An encrypted value – this would be based on the input data, and probably will be unique but like the the digest, will be fairly unpalatable to deal with. You’ll also have to start managing pass keys to be able to repeat the same encryption, and of course they are 2 way so there is a chance of leakage if you are careless with the pass key.
• A readable version of the digest – this idea is to take the digest and use it as the basis to generate some readable string. It’s repeatable, but may lose some of the uniqueness of the digest.

Getting started

All of these functions (other than encryption which I won’t cover here but you can see more at Add-on for decrypting columns in Google Sheets) are in my bmUtils library.

bmUtils key: 1BSHC_YfPl6qs6HBvTteKIMyrL-FiuyEpKNGjS_szDb2PXEYUebddkgVR

The examples we’ll use also use my bmFiddler library for ease of playing around with sheets.

bmPreFiddler key: 13JUFGY18RHfjjuKmIRRfvmGlCYrEkEtN6uUm-iLUcxOUFRJD-WBX-tkR

Here’s a sheet populated with the options mentioned above

The Code

Include the libraries mentioned earlier, make a copy of the spreadsheet mentioned below and substitute in the id of your copy.

const all = () => {
  
  // shortcut
  const u = bmUtils.Exports.Utils

  // get the sheet, do all the transforms & write it out again
  // take a copy of this ss(it's public) and replace with your copy id below
  
  bmPreFiddler.PreFiddler().getFiddler({
    id: '1fpKROA7kitklhGEqSLiTZsy-oUD-U6DzIAl2r55ejRE',
    sheetName: 'obfuscate'
  }).mapRows((row, { rowOffset }) => {
    row.id = rowOffset   1
    row.uuid = u.uuid()
    row.digest = u.md5FromText(row.original)
    row.random = u.flummer({ size: 6, numberOfWords: 2 })
    row.repeatable = u.flubber({ text: row.original, size: 6, numberOfWords: 2 })
    return row
  }).dumpValues()
}

Library Methods

All from bmUtils.Exports.Utils

uuid()

Creates a unique uuid each time it is called.

md5FromText (text)

Creates a hex md5 digest from a given text. WIll produce the same digest for the same given text.

flummer ({ size = 4, numberOfWords = 3, sep = ‘-‘ } = {})

Creates a random series of pronouncable wordlike strings.

Where

• size – the size of each word
• numberOfWords – how many words to create
• sep – what to seperate the words with

const flubber = ({ text, size = 4, numberOfWords = 3, sep = ‘-‘ })

Creates a series of pronouncable wordlike strings based on a digest of the original text. Will produce the same result given the same input.

Where

• text – the original text
• size – the size of each word
• numberOfWords – how many words to create
• sep – what to seperate the words with

Links

Test sheet – make a copy

bmUtils key: 1BSHC_YfPl6qs6HBvTteKIMyrL-FiuyEpKNGjS_szDb2PXEYUebddkgVR (or github)

bmPreFiddler key: 13JUFGY18RHfjjuKmIRRfvmGlCYrEkEtN6uUm-iLUcxOUFRJD-WBX-tkR (or github)