Sometimes you have a forest of spreadsheets each with multiple sheets, but you only want to share a selection of those sheets. You could cut and paste, but that’s error prone and difficult to repeat. You could probably record some kind of macro to do something or other, but that’s fragile. You could write a script, but that’s a lot of work.

Another thing you might want to do is to only make certain columns visible to people with the correct credentials, but not go to the trouble of creating different versions of the summary sheet for different groups. For example, you might want to create a list of employees, vacancies, managers, promotion recommendations etc, but only allow the HR department to see the salaries, and the managers see the promotion recommendations.

This post introduces a library that can summarize selected columns from multiple sheets across multiple spreadsheets into 1 spreadsheet, as well as to optionally apply a public/private key data encryption scheme to selected columns, all via a simple JSON definition file.

Selecting destination spreadsheet

This is all about combining multiple spreadsheets into 1, so we’ll start by definition the final summary sheet, or ‘clone’. Once you’ve created the spreadsheet to clone into, you can define it like this. You’ll notice I’ve added a function to create a public key for encryption. I’ll get back to that later when we start looking at encrypting columns. The createIfMissing property is whether or not to create a sheet if it doesn’t already exist. That’ll usually be set to true.

const clone = {
  // create a new spreadsheet, cloning each of the masters, and encrypting the given columns

  id: '1lNLIpJwvz_GllYnloVBX02vOomZ68cP4Y2KZoPyi7Gg',
  // will create any missing sheets (but the spreadsheet must exist)
  createIfMissing: true,
  // this method generates a new public key for the spreadsheet
  // this example will make a random one, but yu can make anything you want here
  generatePublicKey() {
    return bmCrypter.randomString()
  }

}

Copying

Selecting sheets to copy

This defintion describes all the input spreadsheets, sheets and columns to clone. These are called ‘masters’.

[{
    id: '1cTRN6mGvH_vnWO2ehEFJkWi7Dpw9HcPNvg8zjrWKh4g',
    sheets: [{
      name: 'Billionaires',
      // wildcards are accepted so this just copies all columns
      copy: ['*'],
      // if null rename the same as source
      get renameAs() {
        return 'plain-'   this.name
      }
    }]
  },
  {
    id: '1vnwo8esaek0NPit1UgtERSobiYR5jCIP6dke5vsiPb8',
    sheets: [{
      name: 'caps',
      copy: ['Name', 'Cap Billions'],
      // if null rename the same as source
      get renameAs() {
        return 'caps-'   this.name
      }
    }]

  }]

The sheets array defines which sheets within each spreadsheet should be copied. Here are the properties (there are others which we’ll come to in later examples).

• name: the name of the sheet to copy from
• copy: an array of the names of the columns to copy. Wildcards are allows so [‘*’] copies all columns, [‘A*’] all beginning with ‘A’ etc. Embedded wildcards are supported eg [‘A*’, ‘n*m*’]
• get renameAs(): this a getter to allow you to rename the sheet from the original (to avoid name clashes between multiple input spreadsheets).

Putting it together

The final definition will look like this

  const settings = {
    masters,
    clone
  }

Doing the clone

Now you’ll need to inclue the bmCrypter library – details in the Links section at the end of this article, and doing the cloning is a one liner.

bmCrypter.newCrypter({settings}).exec()

The whole thing

Here’s the entire code.

const runnerPlain = () => {

  const clone = {
    id: '1lNLIpJwvz_GllYnloVBX02vOomZ68cP4Y2KZoPyi7Gg',
    // will create any missing sheets (but the spreadsheet must exist)
    createIfMissing: true,
    // this method generates a new public key for the spreadsheet
    // this example will make a random one, but yu can make anything you want here
    generatePublicKey() {
      return bmCrypter.randomString()
    }

  }
  const masters = [{
    id: '1cTRN6mGvH_vnWO2ehEFJkWi7Dpw9HcPNvg8zjrWKh4g',
    sheets: [{
      name: 'Billionaires',
      // wildcards are accepted so this just copies all columns
      copy: ['*'],
      // if null rename the same as source
      get renameAs() {
        return 'plain-'   this.name
      }
    }]
  },
  {
    id: '1vnwo8esaek0NPit1UgtERSobiYR5jCIP6dke5vsiPb8',
    sheets: [{
      name: 'caps',
      copy: ['Name', 'Cap Billions'],
      // if null rename the same as source
      get renameAs() {
        return 'caps-'   this.name
      }
    }]

  }]
  const settings = {
    masters,
    clone
  }
  bmCrypter.newCrypter({ settings }).exec()

}

And the summary spreadsheet now contains 2 sheets, one with only the selected columns, and the other with then all

Formats and formulas

The idea is for tabular data to be consolidated and shared for viewing, not to create new generations of complex sheets – the original data should always be the source and the summary data should always be able to be recreated from them. The clones of the sheets are values only, so all formatting and formulas are dropped. This also avoids any security and privacy complications that could arise if there were still links other sheets and documents which are not part of the newly created summary clones.

Encryption

It’s possible to encrypt the data in some of the columns so that the data can ony be decrypted by someone who has the private key. Each column can have a different key so you can share the same sheet with multiple people – but they’ll only be able to understand the data for those columns for which they have the key.

Public/Private keys

A cloned spreadsheet has a public key embedded in it’s developer meta data In addition, each column that has been encrypted is tagged in its developer meta data. However, the private key for each column is not held in the spreadsheet, but will be revealed when you run the copying process. You can then distribute these keys as required to those that need them. An encrypted column cannot be decrypted without both the public key in the spreadsheet and its private key that you will have distributed.

Best practise is that both these type of keys are random strings (bmCrypter will generate these automatically for you). However you can supply your own if you prefer the idea of a password to be distributed.

Encrypting and copying

The process for encrypting is only a very slight enhancement on copying. Here’s the whole thing which copies some columns and encrypts others.

const runnerEncrypt = () => {

  // create a new spreadsheet, cloning each of the masters, and encrypting the given columns
  const clone = {
    id: '1lNLIpJwvz_GllYnloVBX02vOomZ68cP4Y2KZoPyi7Gg',
    // will create any missing sheets (but the spreadsheet must exist)
    createIfMissing: true,
    // this method generates a new public key for the spreadsheet
    // this example will make a random one, but yu can make anything you want here
    generatePublicKey() {
      return bmCrypter.randomString()
    }
  }

  // collect all these sheets, andencrypt the given columns
  const masters = [{
    id: '1cTRN6mGvH_vnWO2ehEFJkWi7Dpw9HcPNvg8zjrWKh4g',
    sheets: [{
      name: 'Billionaires',
      // wildcards are accepted so this just copies all columns
      copy: ['*'],
      // and encrypts these
      encrypt: ['Age', 'Billions'],
      // this generates a private key for each column - this generates a random one, but you can do what you like
      // for example use the same key for all columns
      // the params are for convenience in case you want some logic for key generation
      generatePrivateKey(masterId, sheetName, columnName) {
        return bmCrypter.randomString()
      },
      // if null rename the same as source
      get renameAs() {
        return 'encrypted-'   this.name
      }
    }]
  }]

  const settings = {
    masters,
    clone
  }

  const privateKeys = bmCrypter.newCrypter({
    settings
  }).exec()

  // these can be distributed to those with accesss
  console.log(JSON.stringify(privateKeys, null, '\t'))
}

Additional properties

[
	{
		"id": "1lNLIpJwvz_GllYnloVBX02vOomZ68cP4Y2KZoPyi7Gg",
		"sheetName": "encrypted-Billionaires",
		"columnName": "Billions",
		"privateKey": "b4c54054cbc498a3befed0ea5846213c2de9cd12963d54fe33c968a8ab0e0ef4"
	},
	{
		"id": "1lNLIpJwvz_GllYnloVBX02vOomZ68cP4Y2KZoPyi7Gg",
		"sheetName": "encrypted-Billionaires",
		"columnName": "Age",
		"privateKey": "60f3e95f97cece18a5948dc45f16fd49328ea10a49935088621e383dbcb2b2e7"
	}
]

Encrypted data

Here’s how the sheet looks now with those columns encrypted. As you can see the Billions and the Age columns are now encrypted and can only be viewed by someone with the appropriate private key

Decryption

The process of decryption is straightforward – using some or all of the keys displayed during the encryption process (and of course setting appropriate sharing permissions on the cloned file), a viewer can see the original data. Here’s the code for decrypting

const derunner = () => {

  const settings = [
    {
      "id": "1lNLIpJwvz_GllYnloVBX02vOomZ68cP4Y2KZoPyi7Gg",
      "sheetName": "encrypted-Billionaires",
      "columnName": "Billions",
      "privateKey": "2ce4de86c1e936b453b79704c3c94661ce6c578fb251a667c2e0488268f9d690"
    },
    {
      "id": "1lNLIpJwvz_GllYnloVBX02vOomZ68cP4Y2KZoPyi7Gg",
      "sheetName": "encrypted-Billionaires",
      "columnName": "Age",
      "privateKey": "b25672bad05bab35bf2e15cfccc2474bcfea8af959ecdffb3207cd045df50496"
    }
  ]

  const fiddlers = bmCrypter.newDecrypter().exec({ settings })

  // now we can dump out these fiddlers with the decrypted values to the same or difference sheets
  fiddlers.forEach(fiddler => fiddler.dumpValues())
}

You’ll notice that the decrypted data is returned in the form a fiddler (See Fiddler: A functional approach to fiddling with sheet data) and many other articles on this site for what you can do with a fiddler. In this case all we are doing is dumping out the result to the same sheet, but with both the decrypted columns back to their original values (if you had only supplied one of the keys the other would have remained encrypted).

Decrypted data looks like this

Removing the decrypted versions of the data

Normally you wouldn’t bother to keep the encrypted versions of the data, so we can ask for it to be removed by setting the removeEncrypted property.

const derunner = () => {

  const settings = [
    {
      "id": "1lNLIpJwvz_GllYnloVBX02vOomZ68cP4Y2KZoPyi7Gg",
      "sheetName": "encrypted-Billionaires",
      "columnName": "Billions",
      "privateKey": "2ce4de86c1e936b453b79704c3c94661ce6c578fb251a667c2e0488268f9d690"
    },
    {
      "id": "1lNLIpJwvz_GllYnloVBX02vOomZ68cP4Y2KZoPyi7Gg",
      "sheetName": "encrypted-Billionaires",
      "columnName": "Age",
      "privateKey": "b25672bad05bab35bf2e15cfccc2474bcfea8af959ecdffb3207cd045df50496"
    }
  ]

  const fiddlers = bmCrypter.newDecrypter().exec({ settings, removeEncrypted: true })

  // now we can dump out these fiddlers with the decrypted values to the same or difference sheets
  fiddlers.forEach(fiddler => fiddler.dumpValues())
}

And we get this

Writing to another sheet

You may wish to manipulate or write the decrypted data another sheet. Because the result is delivered in the form a Fiddler there are plenty of manipulation or formatting options available if you really want to do that.

This example will write the decrypted data to other sheets, leaving the original encrypted sheets intact.

  // heres how we culd make new sheets for the decrypted
  bmCrypter.newDecrypter().exec({ settings, removeEncrypted: true })
    .forEach(fiddler => {
      // create a new sheet in the same spreadsheet
      bmPreFiddler.PreFiddler().getFiddler({
        id: fiddler.getSheet().getParent().getId(),
        sheetName: fiddler.getSheet().getName()   '-decrypted',
        createIfMissing: true
      }).setData(fiddler.getData()).dumpValues()
    })

One more thing

If you write back to the same sheet the columns will be moved around and/or deleted. The original Spreadsheet metadata that marks encrypted columns will now potentially be pointing to the wrong columns, so we need to repair that. If you’ve written to a different sheet and kept the original sheet intact, there’s no need to do this (although it’ll do no damage if you do) – so it only applies if you’ve decrypted to the same sheet as contained the original encrypted data. The decrypter provides a handy way to do that. Here’s how to write back the data to the same sheet and also repair the encryptioni tags in case you need to redo the decryption for some reason.

   
   const decrypter = bmCrypter.newDecrypter()
   
   // do the decryption
   const fiddlers = decrypter.exec({
    settings: encryptedSelection,
    removeEncrypted
   })

   // now we can update the data and adjust the metadata for the rearranged data
   fiddlers.forEach(fiddler => {
	 decrypter.repairMetaData({ fiddler })
	 fiddler.dumpValues()
   })

    

What next

This article has shown how to clone and encrypt sheets, and the library would make a great platform for an add-on where the user entered the private keys via a UI to reveal the encrypted data. I’ll cover that in the next post on this subject.

If you are interested in seeing how it’s all done, take a look at the code in the bmCrypter library referenced below and also Super simple cipher library for Apps Script encryption and decryption

Links

the library

bmCrypter (13EWG4-1cBN2gAhvhG4vQTPL04V363DaL19TbHRMydR1pVfn2Wju6PuypdukZAFK)

the handy fiddler wrapper

bmPreFiddler (13JUFGY18RHfjjuKmIRRfvmGlCYrEkEtN6uUm-iLUcxOUFRJD-WBX-tkR)

all the code in these examples

testBmCrypter (1VnWXCYlxxlg7AAk0YgMAvx2Fy-7RJuq2JJCvA-1obHRD6s_nCUTwClxT)

All are also on github under github.com/brucemcpherson

Related