In Merging sheets from multiple sources and encrypting selected columns I published some code for selectively copying columns from multiple input spreadsheets/sheets to create summary sheets, and optionally encrypting columns. The idea was to distribute the same sheet to multiple people, along with private key(s) to decrypt columns to which they should have access. This seems a pretty handy thing to make into an Editor Add-on. I’m too impatient and life’s too short to bother getting into the Add-on publishing process, so I haven’t offcially released this one personally, but here is a fully functional decryption Add-on that any of you are welcome to fiddle with and publish yourself.

Background

Although the Add-on UI is fairly trivial, I have used Vue and Vuex as its basis, so it’s in good shape for fancying up if you feel like it. To use this Add-on, you’ll need to have encrypted particular columns and distributed private column keys as described in Merging sheets from multiple sources and encrypting selected columns. The technique I’ve used for integrating Vue and Vuex into Apps Script is taken from How to use Vue.js, Vuex and Vuetify to create Google Apps Script Add-ons and Improve Vue.js Apps Script add-ons with a Vuex store

Behind the scenes, I’m also using a few of libraries such as those described in Super simple cipher library for Apps Script encryption and decryption, Handly helper for fiddler, Fiddler: A functional approach to fiddling with sheet data and Merging sheets from multiple sources and encrypting selected columns.

Creating practise sheets

For the purposes of this article I’m using a couple of input spreadsheets to create a summary sheet with some encrypted columns. I’ve made the input sheets public so you can just run your own script on them to create a summary sheet for testing, or of course you can use your own.

The Add-on references the bmCrypter and bmPreFiddler libraries. Their details in the Links section towards the end of this article.

You’ll also need to provide the id of one of your own spreadsheet (it can be empty, but it must exist) into which the input spreadsheets will be summarized and encrypted.

And here’s the code – if you’re using your own sheets then modify the settings as required. These point to my input testing spreadsheets.

const runnerEncrypt = () => {


  // create a new spreadsheet, cloning each of the masters, and encrypting the given columns
  const clone = {
	
    id: 'enter the id of the sheet into which to write the summary here',
    // 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
      }
    }, {
      name: 'caps',
      copy: ['*'],
      encrypt: ['Cap Billions'],
      generatePrivateKey(masterId, sheetName, columnName) {
        // in this case we'll just have a fixed password
        return 'cap password'
      }
    }]
  }]

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

When that’s done,  you should have a log of private keys to distribute along with the encrypted summary sheet.

The private keys

Here’s an example of private keys you can distribute. The encryption code shown previously will display these on the console. By design, they are not stored anywhere, so if you’ve asked the crypter to generate random keys for you, you’ll need to capture them at time of encryption. Alternatively, you can use static pass phrases if you like. In the settings above, you should notice that one of the columns has a static pass phrase.

The receiver of the key will need to enter the private key into the UX of the Add-on for each column he has access to.

[
	{
		"id": "1lNLIpJwvz_GllYnloVBX02vOomZ68cP4Y2KZoPyi7Gg",
		"sheetName": "encrypted-Billionaires",
		"columnName": "Billions",
		"privateKey": "313e3e5b4b2632423b98a9bdc5b0b7fb91f3ab03cbdcf8df54163d5d5c713edf"
	},
	{
		"id": "1lNLIpJwvz_GllYnloVBX02vOomZ68cP4Y2KZoPyi7Gg",
		"sheetName": "encrypted-Billionaires",
		"columnName": "Age",
		"privateKey": "8fcc2c4eaeda5ada3933d23a300f3b303cb664b65c9667ef2b7cdb5770019248"
	},
	{
		"id": "1lNLIpJwvz_GllYnloVBX02vOomZ68cP4Y2KZoPyi7Gg",
		"sheetName": "caps",
		"columnName": "Cap Billions",
		"privateKey": "cap password"
	}
]

The Add-on

I won’t go through all the code in this article as it’s available in the IDE and on github via the links at the end of this article. Let’s start by looking at the UI and discuss how it works.

The clone spreadsheet has 2 tabs in it created by the encryption process. There are 3 encrypted columns in total. When the Add-on kicks off it detects which columns are encrypted, lists them in the UI and allows the user to enter any private keys he has for those columns.

Decryption

In normal operation, you’ll probably remove the original encrypted columns once decrypted. The Add-on will do this by default, but first we’ll untick that option.

Only the columns for which you’ve entered a private key will be decrypted. Lets’ do just the Billions column for now by inputing it’s private key. Entering any key enables the Test and Decrypt buttons. Test will just check the validity of the key(s), whereas Decrypt will go ahead and process the columns.

Decrypted column

Now there’s a new column, with the decrypted data. Note that the Add-on still notices that the encrypted version of the Billions column is still present.

Removing encrypted columns

In real life you’ll probably want to just get rid of those noisy encrypted columns, so let’s repeat the operation and decrypt everything. Note that any private keys supplied will be  stored in the Add-ons user properties store until the column to which they apply is removed. This allows you to restart the add-on and rerun without bothering to enter the key each time. Since it’s in the User store, the key will be associated with a specific authenticated user, rather than the document as a whole.

I’ve unmasked the keys here so you can see how they related to the sample private keys earlier in the article.

The decrypted sheets

Now we have a clean spreadsheet with all of the decryption complete and the original encrypted columns removed. The Add-on also detects that are no longer any encrypted columns.

Developer info

The Add-on is written using Vue/Vuex. I’ve described these in other articles on this site, but it’s probably worth a quick recap of some tips on how to both develop and test using this framework.

Combining the source code

I like to write the client side code using the IDE as if it were a server side script file (when it doesn’t use any specific browser code). It also allows sharing common code between server and client. The Include module referenced in the index.html of the App allows the inclusion of all types of files client side. Here’s the index.html of the add-on.

<!DOCTYPE html>
<html>
  <head>
  <base target="_top">
    <?!= Include.html(['cdn.css']); ?>
    <?!= Include.css(['app']); ?>
  </head>
  <body>
    <?!= Include.html(['cdn', 'appmarkup']); ?>
    <?!= Include.gs(['KeyStore']); ?>
    <?!= Include.js(['components','store','provoke']); ?>
    <?!= Include.vue([
      'bmMain',
      'bmThrow',
      'bmPrivateKeys',
      'bmOverlay'
    ]); ?>
    <?!= Include.js(['main']); ?>
  </body>
</html>

Vue components

If you enhance this Add-on, you’ll probably want to create some more Vue components. Put each component in a seperate html file in your project named yourcomponent.vue. It should look like this.

Store.addComponent({
  name: "bm-throw",
  template: `
 
    <v-snackbar
      v-model="snackbar"
      timeout= "-1"
      multi-line
      bottom
      :color="messageColor"
    >
      <v-btn
        text
        absolute
        top
        right
        @click="snackbar = false"
      >
        <v-icon small>
          mdi-close
        </v-icon>
      </v-btn>
        
      <v-card-text>{{ message }}</v-card-text>
   
    </v-snackbar>

  `,
  methods: {
    ...Store.mapMutations
  },
  computed: {
    snackbar: {
      get() {
        return this.showMessage;
      },
      set(value) {
        this.setShowMessage(value);
      }
    },
    ...Store.mapState
  }
});

Store.js

The vuex store, the prep for mapping its getters, state, methods and acctions, and the management of Vue components all happens in Store.js . It’s best to keep all that stuff there.

main.js

If you keep everything laid out as described earlier, your main app can be minimal and standard between similar Add-ons. Include.gs can also handle importing Es6 modules, but I don’t have any of those here so the code for that is excluded. If you need to do that too, then see How to use Vue.js, Vuex and Vuetify to create Google Apps Script Add-ons

window.onload = () => {
  const waitForModules = Store.moduleImports || Promise.resolve(null);

  waitForModules.then((modules) => {
    Store.init({ modules }).components.registerAll();

    new Vue({
      el: "#app",
      vuetify: new Vuetify(),
      store: Store.vxStore
    });

    Store.load();

    window.addEventListener("beforeunload", (event) => {
      Store.unload();

      event.preventDefault();

      event.returnValue = "";
    });
  });
};

Provoke, Expose, Server and whitelisting

As a simple wraper for google.script.run I use provoke.js.

// namespace for promisifying server side calls
const Provoke = (() => {
  const loiter = (ms, tag) =>
    new Promise(function (resolve, reject) {
      try {
        setTimeout(() => resolve(tag), ms);
      } catch (err) {
        reject(err);
      }
    });

  const run = (namespace, method, ...runArgs) => {
    return new Promise(function (resolve, reject) {
      google.script.run
        .withFailureHandler(reject)
        .withSuccessHandler(resolve)
        .exposeRun(namespace, method, ...runArgs);
    });
  };

  return {
    run,
    loiter
  };
})();

This will run promisify and run a Server side function, even if part of a namespace (so it avoids lots of stuff in the global space). It calls Expose.js – which is its server side counterpart.

/**
 * used to expose members of a namespace
 * @param {string} namespace name
 * @param {method} method name
 */
var exposeRun = (namespace, method, ...argArray) => {

  // I'm using whitelisting to ensure that only namespaces 
  // authorized to be run from the client are enabled
  // why? to avoid mistakes, or potential poking somehow from the dev tools
  const whitelist = [{
    namespace: "Server",
    methods: [
      "getActiveSpec",
      "decrypt",
      "getAllEncryptedColumns"
    ]
  }, {
    namespace: null,
    methods: ["globalTest"]
  }];

  // check allowed
  if (whitelist && !whitelist.some(d =>
    namespace === d.namespace &&
    (!d.methods || d.methods.some(e => e === method))
  )) {
    throw (namespace || "this")   "."   method   " is not whitelisted to be run from the client";
  }

  const func = (namespace ? this[namespace][method] : this[method]);

  if (typeof func !== 'function') {
    throw (namespace || "this")   "."   method   " should be a function";
  }
  return func(...argArray)

}

The whitelist

Expose refers a list of functions than can be called from the client side. This ensures you have well controlled structure in client/server communications. I generally put all of the functions that will be called Server side in a namespace called Server.gs. In the example I’ve added something in the global space, just so you can see how it’s done compared to the examples in a namespace.

Calling server side functions

Once Expose is set up, we can easily call whitelisted server side functions asynchronously from the client using Provoke. It’s generally simplest to centralize all that in the actions (because they are asynchronous) section of your vuex store and commit any returns values to the Vuex state. Here’s an example vuex action to call a server side function

      // these will come server side
      async getEncryptedFields({ state, commit }) {
        commit("setOverlay" , true)
        commit ("setToaster", {
          message: "Checking to see which columns are encrypted",
          color: state.COLORS.pending
        })
        
        return await Provoke.run ("Server", "getActiveSpec")
          .then (value=>{
            commit("setShowMessage", false)
            if (!value.length) {
              commit ("setToaster", {
                message: "There are no encrypted fields",
                color: state.COLORS.error
              })
            }
            commit("setEncryptedFields", value)
          })
          .catch(err=>{
            console.log(err)
            commit ("setToaster", {
              message: err.toString(),
              color: state.COLORS.error
            })
          })
          .finally(() => {
            commit("setOverlay" , false)
          })
      },

Server.gs

This namespace contains any function that will be called from the client side. Here’s the function that’s called in the example above.

  const getActiveSpec = (spec) => {
    const sheets = _getAllEncryptedColumns({ spreadsheet: _getSpreadsheet(spec) })

    return sheets.reduce((p, sheet) => {
      sheet.encryptedColumns.forEach(column => { 
        const s = {
          ...sheet.spec,
          columnName: column.value,
        }
        console.log(s,_getKeyFromProperties (s), _digestColumn(s))
        p.push({
          ...s,
          privateKey: _getKeyFromProperties (s) || ''
        })
      })
      return p;
    }, [])
  }

Testing server side

It’s pretty tricky to find errors when you try to debug an Add-on as a whole. I always recommend getting the server side running initially in Apps Script by emulating calls that would be sent from the client. For example – this runs Server.decrypt as if it were being called from the client. I’ve left my test.gs script in the repo for some useful server side tests

function myFunction() {
  // test expose run

  const unravelled = exposeRun("Server", "getAllEncryptedColumns", { id: "1lNLIpJwvz_GllYnloVBX02vOomZ68cP4Y2KZoPyi7Gg" })
  console.log(unravelled)


  exposeRun("Server", "decrypt", {
    encryptedSelection: Selection.getEncryptedSelection({ id: "1lNLIpJwvz_GllYnloVBX02vOomZ68cP4Y2KZoPyi7Gg" }),
    removeEncrypted: false,
    testOnly: false,
    returnData: false
  })

}

Testing client side

The first problem is to find syntax errors which are not able to be flagged in the IDE. The chrome console won’t help that much as the code is uglified, but at least you can see if there is an error.

That’s another reason I like to write the code that isn’t relying on browser functions (at least initially) as .gs files and either converting to html later, or using Include.gs to incorporate them into the final htmlservice code. There’s no need to put <script> tags in as Include will insert them if required.

Eventually though you have to put it all together. I usually log the html content, copy it into something like codePen to identify and fix any syntax errors then update the code in the IDE. Here’s where to log the html content.

function showTemplate() {

  const code = HtmlService.createTemplateFromFile('index.html')
    .evaluate()

  // if you want to debug, then uncomment this
  // and copy the code into codepen to play around with it
  // console.log(code.getContent())

  var ui = code
    .setTitle('bm Decrypter Add-on template');

  SpreadsheetApp.getUi().showSidebar(ui);
}

A note on libraries

The recomendation from Google is not to use libraries in Add-ons, but rather to copy the code into your Add-on. The reason given is that libraries are slower to load, but I can’t see any difference – see this analysis Measuring library load speed

However there are other reasons not to want libraries in your add-on, not least that they might disappear at some point if they are not under your control, so you might want to consider pulling in the code for each library from github. The code for any libraries they themsleves access wil be in their repos too under the libraries folder.

What’s next

Now we have a way to selectively encrypt columns, distribute the spreadsheet to multiple people, each of whom can use this add-on to reveal only the columns for which they have the private key. However it still means that now they could potentially have a sheet with decrypted sensitive data that they could save and if you’ve been careless with sharing options, others could see it too.

An enhancement I could suggest would be to create a new spreadsheet private to the decrypter with the decrypted data in it, rather than updating the distributed summary sheet.

Another interesting idea could be to use an ephemeral way of displaying decrypted data than writing it to a spreadsheet. Watch this space for a solution based on this idea.

Links

The Add-on

bmDecryptAddon 1Byrtnr_uuAt3BiZ6_qh6T8vBZq-YdgnDaqoJ64Ss7kM4q2XE-XGPcmCi

Crypter library

bmCrypter (13EWG4-1cBN2gAhvhG4vQTPL04V363DaL19TbHRMydR1pVfn2Wju6PuypdukZAFK)

Handy fiddler wrapper

bmPreFiddler (13JUFGY18RHfjjuKmIRRfvmGlCYrEkEtN6uUm-iLUcxOUFRJD-WBX-tkR)

All are also on github in their respective repos at github.com/brucemcpherson

Related

Merging sheets from multiple sources and encrypting selected columns

Sometimes you have a forest of spreadsheets each with multiple sheets, but you only want to share a selection of ...
Read More

Super simple cipher library for Apps Script encryption and decryption

Encryption and decryption can be a bit cheesy to come to grips with. The most common library is probably cryptojs ...
Read More

Import, export and mix container bound and standalone Apps Script projects

This article covers how to pull scripts from multiple projects and import them into another project. You can even use ...
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 ...
Read More

4 ways to customize scrviz info card behavior

Info Card customization By default the info card appears when you hover over a node in the scrviz visualization. Although ...
Read More

Find an Apps Script library id in 10 seconds with scrviz

You want to include an Apps Script library, and you know it's name, but not its id. A pain right? ...
Read More