2

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?

2
  • @pnuts: because b1 =max(a:a) contains a1 which references b1 -> circular dependency
    – Geert-Jan
    Sep 10, 2014 at 20:02
  • @pnuts: I should have made a correct if-statement (corrected). The intention of the formula in A1 is: set the value of A1 to be the value of B1 + 1. However, only do that when that assignment hasn't been performed before. In other words, A1.. AN need to be autogenerated from B1. At least that was the idea. Pretty sure it can't be done without App Script atm.
    – Geert-Jan
    Sep 10, 2014 at 20:17

2 Answers 2

1

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.

0

You can make the formula to run only once, but you cannot control in which order they run (think of them as having independent computers running in parallel). Since they all reference B1, which keeps changing, you will get garbage.

You could change in settings how many circular dependencies are allowed (for example 1), but you still have other problems.

I feels this is a bit like the X Y problem ( https://en.wikipedia.org/wiki/XY_problem ).

If you want 'unique IDs' ask for that.

For example:

A1=1
A2=if(A1<>"",A1+1,"")
....

B1=max(A1:A)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.