Community mailing list archives

HOWTO: Content adressable csv recrords

DevCO Colombia , David Arnold
- 04/02/2016 08:54:57

maybe you have had that problem:
You are preparing a data loading in google spreadsheet and try to make ids by the spreadsheet auto increase function or some formula based on the row number or something similar. This is great until you put a value in the middle of your sheet, so everything get's mixed up.

Solution: content addressable record id's.

What's that? Basically, the idea is that you address records in their ids according to their contents. Therefore you need a hash function to hash your name field and some other fields which you want to make up and identify a unique record.

Now you can even easily supercharge the same record in the same csv, for example if there is a complicated tree relationship between your records: If the chosen fields are identical, the id will be identical.

So there is no hash function readily available in google spreadsheet, so let's create one. The code is the following:

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;

Put that in the code editor and now you can do things like =MD5(A1).
Beware that those functions somehow do not work with ARRAYFORMULA.

Hope this was useful.

If you can share some best practice or workflow on how to initially upload product and variants into a database by csv. I'm happy to hear.

Another tipp: Dynamic dropdowns is not a trivial one, but here is a simple guide to a viable solution.