Hash of a cell text in Google Spreadsheet

Question

Open Tools > Script Editor then paste the following code:

function MD5 (input) {
  var rawHash = Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, input);
  var txtHash = '';
  for (i = 0; i < rawHash.length; i++) {
    var hashVal = rawHash[i];
    if (hashVal < 0) {
      hashVal += 256;
    }
    if (hashVal.toString(16).length == 1) {
      txtHash += '0';
    }
    txtHash += hashVal.toString(16);
  }
  return txtHash;
}

Save the script after that and then use the MD5() function in your spreadsheet while referencing a cell.

This script is based on Utilities.computeDigest() function.

Thanks to gabhubert for the code.

This is the SHA1 version of that code (very simple change)

function GetSHA1(input) {
  var rawHash = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_1, input);
  var txtHash = '';
  for (j = 0; j <rawHash.length; j++) {
    var hashVal = rawHash[j];
    if (hashVal < 0)
      hashVal += 256; 
    if (hashVal.toString(16).length == 1)
     txtHash += "0";
    txtHash += hashVal.toString(16);
    }
  return txtHash;
}

Ok, got it,

Need to create custom function as explained in http://code.google.com/googleapps/appsscript/articles/custom_function.html

And then use the apis as explained in http://code.google.com/googleapps/appsscript/service_utilities.html

I need to handtype the complete function name so that I can see the result in the cell.

Following is the sample of the code that gave base 64 encoded hash of the text

function getBase64EncodedMD5(text)
{ 
  return Utilities.base64Encode( Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, text));
}

The difference between this solution and the others is:

1. It fixes an issue some of the above solution have with offsetting the output of Utilities.computeDigest (it offsets by 128 instead of 256)

2. It fixes an issue that causes some other solutions to produce the same hash for different inputs by calling JSON.stringify() on input before passing it to Utilities.computeDigest()

function MD5(input) {
  var result = "";
  var byteArray = Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, JSON.stringify(input));
  for (i=0; i < byteArray.length; i++) {
    result += (byteArray[i] + 128).toString(16) + "-";
  }
  result = result.substring(result, result.length - 1); // remove trailing dash
  return result;
}