Google Sheets – How to Execute Function Only Once to Prevent Circular Dependency

google sheets

I'm trying to do something like this:

a1 = if(a1 = "", b1 + 1, )
…
aN = if(aN = "", b1 + 1, )

b1 = max(a1,1)

The idea is that column A has unique ID's, and b1 keeps the highest generated id until then. The conditional a1 ="" would make sure the function only executes once (for a1 in this case).

This construct would allow me to generate unique ids, that would be stable under row inserts, deletes, etc.

The above fails because the script detect circular references, which is correctly when statically examined I guess.

Is there a way to do something similar without falling onto the circular dependency pit?

Best Answer

With formula's it can't be done, as per your other question, but with a little piece of Google Apps Script you can roughly accomplish what you want.

Code

// global
var app = SpreadsheetApp.getUi();

function onOpen() {
  // create menu entry
  app.createMenu("IDs").addItem("Set all IDs", "myIDs").addToUi();
}

function myIDs() {
  // set variables
  var sh = SpreadsheetApp.getActive();  
  var rText = app.prompt("Give range", "Like A1:A10 (single column only)", app.ButtonSet.OK_CANCEL); 
  var r = sh.getRange(rText.getResponseText()), v = r.getValues(), max = 0;

  // retrieve max value
  for(var i = 0, iLen = v.length; i < iLen; i++) {
    var value = v[i][0];
    if(value > max) {
      max = value;
    }
  }

  // prepare output
  var output = [];
  for(var j = 0, jLen = v.length; j < jLen; j++) {
    var val = v[j][0];
    if(val == "") {
      output.push([max + 1]);
      max++;
    } else {
      output.push([val]);
    }
  } 

  // show/return results
  r.setValues(output);  
}

Explained

From the menu, the script is activated. A prompt is shown and a range is requested. The script will retrieve all data from that range and find the highest value. Afterwards it will evaluate the range for empty entries. Once found, it will add a value (max + 1) to that position and re-set the maximum value. At the end of the script all data is added new data to the spreadsheet.

Screenshot

before & after
enter image description here enter image description here

Example

I've created an example file for you: check if cell itself is empty
Add the script under Tools\Script editor from the menu. Press the bug button to authenticate the script.

Related Topic