Aaron Toponce : Digest Algorithms in Google Spreadsheets

I can't imagine there are a lot of uses for using digest algorithms in spreadsheets, but I came up with one, and I really wished I had access to them. Seeing as though most spreadsheet applications don't ship one, I figured I would create my own.

Mostly, I use Google for my document processing and spreadsheet use, and I had a spreadsheet of Louis L'Amour books. My grandfather gave me his entire selection of Louis L'Amour books last year, and I made a goal to read them all during 2016. I grew up listening to them on audiotape in the truck when I was on the road with him laying carpet, heading up to the family cabin in Idaho, and other things, so, I have memories of many of the stories. It will be fun to read them.

So, what do digest algorithms have to do with Louis L'Amour novels? Well, after the spreadsheet was created to track what I've read and what I have left, as well as a pace (I have to be reading at least 60 pages every day), I wanted to start reading the books in a random order. Sure, I'll read the Sackett, Hopalong Cassidy, Talon & Chantry, and Kilkenny series first, but when I'm finished with the series, I want to read the novels in random order. Why? Because I don't want to get caught up in published year watching him change as a writer, or go in alphabetical order, because that's boring. Randomness is exciting!

Now I could have used the =RAND() function in the spreadsheet, but when I sort the columns, the numbers change. So, I need to copy and paste their values, then sort the columns. Besides, is RAND() even cryptographically secure (indistinguishable from true random noise)? Even better, I could just get ASCII data off of /dev/urandom and paste those results into the column, then sort off of that. But that requires using an external tool. However, I could also use a digest algorithm to calculate the digest of the book title, then sort by the digest. Because digest algorithms aren't part of the Google Spreadsheet default functions, my OCD kicked in, and I had to create one.

Here is what I came up with. You'll notice that MD2(), MD5(), and SHA1() are created, even if they're not cryptographically secure for today's modern cryptographic applications. However, in this specific use case, such as sorting columns, they are fine. Also, notice that SHA256(), SHA384(), and SHA512() exist, but not SHA224(). This is because "Utilities.DigestAlgorithm" does not export a "SHA_224" algorithm, which in my opinion, is just odd. MD4 is also not available, nor any of the SHA-3 functions. Regardless, all the digest algorithms supported by the API are available.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

// Cryptographic hash functions for use in Google Spreadsheets
// Use with =MD5(string)
// A string or cell (or concatenation of cells) can be provided
// Output in hex
// Released to the public domain

function MD2(s) {
var hexstr = '';
var digest = Utilities.computeDigest(Utilities.DigestAlgorithm.MD2, s);
for (i = 0; i < digest.length; i++) {
var val = (digest[i]+256) % 256;
hexstr += ('0'+val.toString(16)).slice(-2);
}
return hexstr;
}
function MD5(s) {
var hexstr = '';
var digest = Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, s);
for (i = 0; i < digest.length; i++) {
var val = (digest[i]+256) % 256;
hexstr += ('0'+val.toString(16)).slice(-2);
}
return hexstr;
}
function SHA1(s) {
var hexstr = '';
var digest = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_1, s)
for (i = 0; i < digest.length; i++) {
var val = (digest[i]+256) % 256;
hexstr += ('0'+val.toString(16)).slice(-2);
}
return hexstr;
}
function SHA256(s) {
var hexstr = '';
var digest = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, s);
for (i = 0; i < digest.length; i++) {
var val = (digest[i]+256) % 256;
hexstr += ('0'+val.toString(16)).slice(-2);
}
return hexstr;
}
function SHA384(s) {
var hexstr = '';
var digest = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_384, s);
for (i = 0; i < digest.length; i++) {
var val = (digest[i]+256) % 256;
hexstr += ('0'+val.toString(16)).slice(-2);
}
return hexstr;
}
function SHA512(s) {
var hexstr = '';
var digest = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_512, s);
for (i = 0; i < digest.length; i++) {
var val = (digest[i]+256) % 256;
hexstr += ('0'+val.toString(16)).slice(-2);
}
return hexstr;
}

So, how do we apply it? Because this is a script which is applied to your spreadsheet, you need to add it manually every time you create a new spreadsheet document. Supposedly, you can permanently add it through the Chrome Web Store (I created a project that is currently pending review), but for the time being, copying/pasting works.

Navigate to "Tools > Script Editor", remove the default function Google provides, and add the code above. Save it as a project, then it will be available to your spreadsheet. Now you can use it.

For example,

To calculate the MD5 of cell "A1":
    =MD5(A1)

To calculate the SHA1 of cells "A1" through "D1":
    =SHA1(CONCATENATE(A1:D1))

Retrieve the 12 left-most characters from a SHA512 digest of cells "A1" through "D1":
    =LEFT(SHA512(CONCATENATE(A1:D1)), 12)

I'm sure there are a few uses for digest algorithms in spreadsheets, they're just not very common; at least web searching for their use gives me very scant results. If you find these helpful, even if there are other solutions, I would be interested in how you used them in the comments below.

Oh, and here's my Louis L'Amour spreadsheet tracking my reading progress. 🙂

Aaron Toponce

Digest Algorithms in Google Spreadsheets

{ 3 } Comments