Google Sheets – Circular Dependency Detected Error

google sheets

I am very beginner to Google scripting. Here I need to count the number of times the word "FREE" occurred in my sheet. But my function returns me an error

Error: Circular dependency detected**

(See the attached screen shot.)

What am I doing wrong and how can I fix it?

Please note: I have some merged cells here.

enter image description here

Best Answer

This is happening because you have put the formula inside the range (A1:c6) that you are trying to count the number of "free"'s in.

Put the formula into a cell outside the range.

Related Solutions

Google Sheets Formulas – Fixing Circular Dependency with IFERROR

The pure spreadsheet answer is to create columns D and E to display your information. An example row:

A3: text
B3: (enter USD if known)
C3: (enter LBP if known)
D3: = if( isblank(B3) ; C3/1500 ; B3 )
E3: = if( isblank(C3) ; B3*1500 ; C3 )

The other way you can solve this is with JavaScript, if you know it, using the onEdit trigger. In that case the JavaScript would detect the edit to B3 and automatically add the formula in C3, or vice versa.

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

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

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.