Google Spreadsheet: Encrypt cell content with Google Apps Script

Question

I have a Google Spreadsheet and would like to encrypt the content of a few cells (I do not care which encryption method is being used as long as there is an equivalent decryption method for iOS).

Unfortunately there are no built-in encryption functions in Google Apps Script.

For this reason I would like to use a open source Javascript library like Crypto-JS and sjcl.

How can I use one of these libraries with Google Apps Script?

In the Google Apps Script documentation, I have not found any clue on how to use external JavaScript libraries with my Google Apps Script.

Answer 1

Well I'll say this, because this is the method that I used with Date JS. You can do the following:

1. Download the source .js file(s).
2. Open the .js file(s) in a text editor
3. Copy/paste all code into a new Script Project
   • here you can "recreate" the original .js files (copy/paste source individually) with the same names
4. Include the project key of that Script Project as a library of the project in which you want to use those functions.

Even if the projects are open-source you will want to make sure you comply with the licenses of those projects if you are going to use them.

This is basically a small "hack" around not being able to upload .js files into GAS Projects. Assuming that the JS is standard, this method will work with Google's system.

---

The other option is to simple find a light-weight one- or two-function crypto package, or a single crypto algorithm like AES-128 (taht you are given permission to use, of course). It really depends on how much encryption you want, if you need to reverse the cipher text to get the plain values, etc.

If this is a for some kind of password system, I would recommend using a simple hash. For example:

function stringHash (someString) {
    var hash = 0;
    if (this.length == 0) return hash;
    for (i = 0; i < this.length; i++) {
        char = this.charCodeAt(i);
        hash = ((hash << 5) - hash) + char;
        hash = hash & hash;
    }
    return Math.abs(hash); // Personally I don't like negative values, so I abs'd it
}

in which you would ask for a user's password, and if the password hash matched the hash stored in the spreadsheet or wherever, then you would validate. You can use this to simulate logging into a UiApp GUI, for example: store usernames/password hashes in a database and validate a user before loading the "real" app.

---

However, as Serge mentioned, Spreadsheets will contain revision history of the original value before it was hashed, as well as the value after it was hashed. If you want to avoid this, use ScriptDB.

---

PS - in addition to this work-around, I'll say that it's not currently possible to "import" a non-GAS code library into your Script Project, unless you manually copy the source file-by-file into your Script Project. There may be a feature request on the Issue Tracker already, if not you can create one and I'll star it.

---

EDIT: As per request, I've included an open source AES encryption "package" (contains base64 as well, which is nice) in the answer, to act as a reference for others who want to encrypt in GAS. Make sure you follow the author's request, which is to retain his original copyright and link back to the source.

Other than the AES I linked and the simple hash (equivalent to Java's String.hashCode()), whose resource can be found here, there is Crypto-JS as you mentioned in your question and, if you took the time to fully copy/paste all the code (assuming that agrees with the terms of the license - I haven't read it), you could use that by the steps I described in the top half of my answer.

MD5 in Javascript is also an algorithm that you could use. If you use the code in md5.js which is located at the top of the page, you'll have what you need. Again, make sure you're following licensing rules if you use it.

Personally I would probably just use the hash and the base-64 patterns, as most of what you would use this encryption for is probably not incredibly important. AES might take a bit longer to compute - you can probably benchmark it yourself to see if it will cause major problems with triggers running for an extended period of time, but I doubt it would be a problem anyway.

Note: base-64 is 2-way, so is AES. MD5 is a type of hash, and the simple hash function I provided is also (of course) a hash. Hash functions are one-way. So if you need two-way functionality (encrypt/decrypt), then use base-64 or AES. Base-64 is essentially the kid version of AES. And the simple hash function is the kid version of MD5. Keep this in mind :)

Edit again: I'm not familiar with iOS development or its internals, but it seems to me that iOS can at least do some cryptographic operations. You may want to read more into those methods though, because I'm not really sure how you're putting GAS and iOS together; I can't give you any more help in that area unfortunately.