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()
  }

}
clone definition

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
      }
    }]

  }]
masters definition

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
  }
final settings

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()
executing the clone

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()

}
the whole thing with no encryption

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

You’ll notice that there are a couple of additional properties in the definition to specify the encryption required.
  • encrypt: works the same as copy – an array of column names that should be encrypt, and it supports wildcards in the same way. Any columns appearing in both copy and encrypt will only be encrypted – that means you can still use [‘*’] to copy all the columns. The encrypted ones will be automatically excluded from plain copying
  • generatePrivateKey(masterId, sheetName, columnName): this is a method for how to generate a private key for each column. The arguments are provided in case you want to conditionally assign a password or some other scheme. The best method is just to create a random string for each column as in the example above, but you may want to use a single key for multiple columns, for example.

Private Keys

You’ll also have noticed that the Crypter returns an array of privateKeys – one for each column encrypted. These can be distributed to authorized column consumers as required to decrypt the data within.

The result looks like this, and is not recorded anywhere – this is the only copy that will be produced.

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

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

encrypted columns

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())
}
decrypting and dumping

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

decrypted data

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())
}
remove encrypted

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()
    })
writing to other sheets

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()
   })

    
repairing the metadata if writing back

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

Fiddler now supports joins to merge matching columns from multiple sheets

You may have come across my post on SQL for Apps Script it's here where I shared a library for using ...
Read More

A fourth way to preserve and create formulas with Fiddler for Sheets, plus some more new methods

This is (probably) the last in a series of posts describing how Fiddler for Sheets can be used to help ...
Read More

A third way to preserve formulas with fiddler, plus 2 new methods

In 2 ways to create and preserve formulas with fiddler for Google Apps Script I gave some examples of how to ...
Read More

2 ways to create and preserve formulas with fiddler for Google Apps Script

I've had a few queries from fiddler users about how to deal with formulas. Fiddler is primarly for abstracting spreadsheet ...
Read More

Handly helper for fiddler

If you use some of my libraries, you may have come across  the Fiddler class, which abstracts sheet data and ...
Read More

Optimize updates to Google Sheets with fiddler fingerprints

If you use some of my libraries, you may have come across  the Fiddler class, which abstracts sheet data and ...
Read More

Fiddler and rangeLists

Header formatting with fiddler  shows how to use fiddler to easily set formats for your sheet headings and Formatting sheet column ...
Read More

Formatting sheet column data with fiddler

Header formatting with fiddler  shows how to use fiddler to easily set formats for your sheet headings. here's how to ...
Read More

Header formatting with fiddler

When playing around with sheet data, I always find myself using the Fiddler object - some more posts on this ...
Read More

Populating sheets with API data using a Fiddler

Another place that Fiddler can be handy is if you are populating a sheet with data retrieved from an API ...
Read More

A functional approach to updating master sheet with Fiddler

A common thing to do is to update a master sheet from an update sheet - where you want to ...
Read More

Unique values with data fiddler

Fiddler is a way to handle spreadsheet data in a functional way, as described in A functional approach to fiddling ...
Read More

Fiddler – A functional approach to fiddling with sheet data

  I wondered if there might be more functional programming, more declarative approach to the problem of fiddling around with ...
Read More

Pulling in Apps Script libraries – how is it done

This is a follow on from the article on Pull libraries inline to your Apps Script project (which you should probably ...
Read More

12 Years and 1000 pages in Office,Google (Docs,Gsuite) Workplace, and other stuff

1000 pages and counting Most years I do a post on 'a year in Apps Script', looking back over the ...
Read More

Add-on for decrypting columns in Google Sheets

In Merging sheets from multiple sources and encrypting selected columns I published some code for selectively copying columns from multiple input ...
Read More