5

How can I compute a MD5 or SHA1 hash of text in a list of cells? For example, I have 100 email addresses in column A row 1-100 and I wish to encrypt them all. I would like to write the encrypted emails in column B next to each decrypted email address. What is the best way to do this?

I have read the answer here, but it doesn't exactly work

I have minimal experience with google spreadsheets :-(

I began by using this script, but could only encrypt one email at a time

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;
}
Community
  • 1
  • 1

2 Answers2

2

Define MD5 function as you did, and then in B1 put:

=arrayformula(MD5(A1:A100))
mik
  • 3,575
  • 3
  • 20
  • 29
0

if you add the following function underneath and then run straightToText() it works

function straightToText() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var r = 1;
  var n_rows = 9999;
  var n_cols = 1;
  var column = 1;
  var sheet = ss[0].getRange(r, column, n_rows, ncols).getValues(); // get first sheet, a1:a9999
  var results = [];
  for (var i = 0; i < sheet.length; i++) {
    var hashmd5= GetMD5Hash(sheet[i][0]);
    results.push(hashmd5);
  }
  var dest_col = 3;
  for (var j = 0; j < results.length; j++) {
    var row = j+1;
    ss[0].getRange(row, dest_col).setValue(results[j]);  // write output to c1:c9999 as text
  }  
}