Original Poster

find if thre is a formula in a cell?

since there is no way to lock a cell, I need to be able to check if the user deleted a a formula. I want to 'error trap' if that happens.
how do I, say, insert another formula (g9) to look on c9 if the formula was deleted?

thank you

Community content may not be verified or up-to-date. Learn more.
Recommended Answer
Was this answer helpful?
How can we improve it?
All Replies (5)
There is no way to test if a cell contains a formula, using a spreadsheet function, not even a (GAS) spreadsheet function.

I could envision a e.g. onEdit triggered GAS script that performs a test (look if the retrieved formula and the retrieved value are equal, when they are equal the function has been deleted.), but the setup of such would mean the script needs to know in which cells to expect a formula. Note this does not keep the user from changing the formula...

(Perhaps you may be interested in [1] )
Hmm, I spoke too soon, misleaded by some GAS server errors. There is a possibility using a GAS custom function like this:

function IsFormularowoffset, columnoffset{
  var SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell();
  return  r.offset(rowoffset,columnoffset,1,1).getFormula().length != 

Usage after installing and saving the custom function:
A1: =INT( 123.45)
B1: =isformula( 0 ; -1)

Note the method using offsets is less than elegant, so the function needs refining, but I fear no direct references can be used - it's a current limitation in GAS that custom spreadsheet function only can get values passed, and nothing else. So the above is something of a workaround.

P.S. [1] contain the link I apparently forgot to add in my previous post

Original Poster
Ahhh!!! wonderful! Thanks Ahab. "weak pseudo protection" is was looking for. That was so easy, I would have never found the function to use. How come is not listed ?

thank you so much
Original Poster
but you know what, I dont know how to make it work in my case. I explain:

A1 will be the cell I use the EXPAND function (hidden to the user)
B1 and C1 user enters data
D1 will have the calculation: B1*C1

can you help me do this? I can't find much reference on help with this function

There are some limitations to the weak pseudo protection method, i.e. it can not 'jump over' cells that should not be protected.
E.g. you want the formula to be in A1, the result in D1 but you want to use B1 and C1 for input. This can't be established as the formula in A1 has to create content - even if this means a blank cell - for all the cells it covers, so the formula in A1 must cover B1 and C1 for it to be able to put a result in D1

You can however use a different strategy, don't use a hidden column but use a first hidden row (or both a hidden row and column).
Like this
D1: ={ "" ; B2 * C2}
Hide row 1 in which resides D1
You now can enter data in (A2,) B2 and C2 and have the formula result in D2 protected this way.

Note that in the embedded array { ... } commas are used as column separators ( but backslashes "\" in locales using the decimal comma in numbers!) and semi-colons as row separators. e.g. {1,2;3,4} .
Note on the EXPAND function: this function was added to force the re-creation of the CONTINUE functions that contain the result in case they get deleted. This appears no longer to happen automatically when EXPAND is omitted, instead there is an error message in the cell the formula is in which suggest manual intervention to recreate the CONTINUE function (a select cell and press Ctrl+R message), and we won't want that to happen as the formula is hidden. (Google Docs spreadsheets is so wonderful, for each omission or aberration you can always try to find a workaround - unfortunately not with the entire 100% of them. ;)
This question is locked and replying has been disabled. Still have questions? Ask the Help Community.


Some community members might have badges that indicate their identity or level of participation in a community.

Google Employee — Google product team members and community managers
Community Specialist — Google partners who help ensure the quality of community content
Platinum Product Expert — Community members with advanced product knowledge who help other Google users and Product Experts
Gold Product Expert — Community members with in-depth product knowledge who help other Google users by answering questions
Silver Product Expert — Community members with intermediate product knowledge who help other Google users by answering questions
Product Expert Alumni — Former Product Experts who are no longer members of the program
Community content may not be verified or up-to-date. Learn more.


Member levels indicate a user's level of participation in a forum. The greater the participation, the higher the level. Everyone starts at level 1 and can rise to level 10. These activities can increase your level in a forum:

  • Post an answer.
  • Having your answer selected as the best answer.
  • Having your post rated as helpful.
  • Vote up a post.
  • Correctly mark a topic or post as abuse.

Having a post marked and removed as abuse will slow a user's advance in levels.

View profile in forum?

To view this member's profile, you need to leave the current Help page.

Report abuse in forum?

This comment originated in the Google Product Forum. To report abuse, you need to leave the current Help page.

Reply in forum?

This comment originated in the Google Product Forum. To reply, you need to leave the current Help page.

Clear search
Close search
Google apps
Main menu
Search Help Center