Automated MD5 and Hex Encoding of Spreadsheet Columns

Question

I am receiving a CSV file, and in that file I need to Hex Encode one column, and MD5 Hash another column.

Final Outcome I am looking for is to take an incoming CSV file from an FTP account, somehow take data from two columns and encode (as above) the data into two other columns automatically.

While I can do this using a script I found in Excel manually, I really need to automate this process some other way so i am looking for help in knowing how to do this, perhaps sending the CSV file from FTP to Google Sheets and having a script there that automatically adds the two calculations as data is imported - so I can then pull from Google Sheets as the source to the program that will use it (automated)?

Or if there is a program out there that can do this?

Any help is much appreciated!

Answer 1

You can do it inside google sheets

Hex encode

If you want to hex encode decimal digit, you can use this built-in function: DEC2HEX(). Documentation about that function https://support.google.com/docs/answer/3093137?hl=en

MD5 hash

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.

Answer 2

This response may be a bit late, but could help to somebody out there:

In case you need a unique identifier for a Google sheets cell I just got this:

Formula: =IMPORTXML(https://md5.gromweb.com/?string=&C2, "//*[@id='content']/p[1]/em[2]")

Reference: https://support.google.com/docs/answer/3093342?hl=en&ref_topic=9199554

Detail: The idea is to have a column with a standard length string that is unique, the MD5 function is not in Google sheets unless you create a formula.

What did I do: With the IMPORTXML function, I created a formula and used the URL of the site https://md5.gromweb.com, and took the XPath path of the query result which is the second parameter of the formula, for the query parameter I concatenated several cells in one and from that concatenated cell extract an MD5 from the string resulting from the concatenation, in this way I manage to have an md5 hash that varies each time the cell is updated.