13

Is there a function that will tell if a specific cell has a plain (manually entered) value or has a formula?

5

With this little snippet you can do that.

Code

function isFormula(startcol, startrow) {
  // prepare string
  var str = String.fromCharCode(64 + startcol) + startrow;

  // retrieve formula
  var fCell = SpreadsheetApp.getActiveSpreadsheet()
    .getRange(str).getFormula();

  // return false if empty else true 
  return fCell ? true : false;
}

Usage

enter image description here

In this answer, Tom Horwood found a way to circumvent the usage of quotes.

Example

Add the script under Tools>Script editor and press the bug button to activate the authentication.

I've created an example file for you: Check for Formula

  • I was wandering... Your function is actually getting a string which is the address of a cell isFormula2("A1"). so I tried to create a new version of it like this: function isFormula2(cell) { return (cell.getFormula())!=""; } and I expected it to be callable using isFormula2(A1) but this does not seem to work and when trying to debug it shows cell as undefined... any idea why is this and if things could be changed so it will work with f(A1) instead of f("A1") ? – epeleg Jan 24 '14 at 9:14
  • @epeleg See my revised answer. – Jacob Jan Tuinstra Jan 24 '14 at 19:26
  • :) so you changed it so that instead of getting the address it now gets a column and row. is here no syntax such that the call would just be isFormula(a1) ? (in MS excel it would be a function that gets a range parameter) – epeleg Jan 27 '14 at 9:37
  • @epeleg The closest is isFormula_1("A2:A2"), look at my example file. Custom functions work that way. Without the quotes, you will get the value. With quotes the string, that can be used as such, but can't be copied/pasted. The COLUMN and ROW solution is just a work-a-round (inevitably). – Jacob Jan Tuinstra Apr 20 '14 at 17:45
  • what would be the value that a custom function would get if you call it with isFormula(a1:a1)? and what about isFormula(a1:b2)? – epeleg Apr 21 '14 at 19:36
9

Short answer

Use ISFORMULA() built-in function.

Background and Explanation

I think I just discovered an undocumented formula in the new Google Sheets. @Pnuts was saying the CELL formula didn't work (partially because it's only available in the new Google Sheets), so I copied the code into a new Google Sheet. An error was raised, telling me my (custom) formula accepts only one argument and not two:
enter image description here

Renaming the isFormula into something else, resulted in a working custom function. After this being the case, I created yet another Google Sheet and used the isFormula formula. The formula doesn't appear in the auto-complete, but it is working:
enter image description here

So, if the formula is available throughout the new Google Sheets, then this would be the best answer:

Formula

=isFormula(A1)

Note

Only available in the new Google Sheets. I've asked a few other people to confirm the new formula and it's confirmed. As it's undocumented, its implementation is unsure and it might disappear. The ARRAYFORMULA doesn't work when used with this new formula.

Example

I've created an example file for you: isFormula

  • nice. looks like Google are adopting my recommendation :) But how do I switch to the "New Google Sheets" ? – epeleg Jul 9 '14 at 13:12
  • interesting. it works for my gmail account but not in my google apps account... – epeleg Jul 9 '14 at 13:16
  • 2
    ISFORMULA() is now documented. See support.google.com/docs/answer/6270316?hl=en – Rubén Jan 21 '16 at 0:47
2

It looks like there is a hidden built-in function ISFORMULA() from Google. I realized when I deleted my own function and was still working. I tried it in a new spreadsheet and still works. No cache issue.

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.