Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I created very simple spreadsheet application in Google Spreadsheet for my mom. But I really need to lock/protect the cell with formulas so she cannot edit them. Every time I told her to try new versions she overwritten the formulas.

Any idea how to approach that from technical point of view?

I searched the net and it seems to me that it is not doable right now in Google Apps.

share|improve this question

7 Answers 7

In Microsoft Excel I can lock the entire sheet and then selectively unlock individual cells or ranges.

In Google Spreadsheets, I lock all cells in one or more separate ranges except the cells that are to be available for input by other users. See this example of a Google sheet I made today: sample

One variation from Excel is that all in the Google sheet all locked cells have grey diagonal stripes as background.

share|improve this answer
    
Please do not user URL shorteners here. –  Al E. Apr 6 at 17:26
up vote 3 down vote accepted

The answer as per today is "Named and protected ranges" under Data menu. Works smoothly.

share|improve this answer
3  
The limitation of this solution is that the owner of the spreadsheet cannot lock cells against herself. To solve the OP's problem, the OP must also own the spreadsheet and share it with mom. –  MετάEd Dec 7 '12 at 16:32
    
I cannot see any limitation to this solution. Could you elaborate? Owner change protect range of cells against himself too. –  Radek Mar 24 '13 at 23:06

Try making the boxes where she is supposed to be entering the text a different colour and tell her to write only in the coloured boxes.

share|improve this answer

A workaround this is to use DATA- Validation...

Go to Data menu, click validation..., then define your range of cells that you need to "protect". Ex: SheetA!F2:F9999. Then select Criteria: Text, equals. Then enter into the box something like: "=*" without the quotation marks. Uncheck the "allow invalid data..." box. Save.

This should only let user enter a value that begins with = sign (which is the beginning of your formula to protect). If they enter the = sign then they can modify that cell.

The askterisk allows anything after the = to be valid. You could specify it further to protect it more: Ex: Text, equals, =sum(), or =AVG(B:D*), etc...

share|improve this answer

You can use google apps script to write your own locking mechanism in javascript.

For example:

Lets say you have a formula in column B and it is equal to the value in column A + 1. If we look at cell (B3) it should have a formula in it of "=A3+1"

To write a script to protect this from modification you would write something like this using Script Editor:

function onEdit(event)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var CellRow = SpreadsheetApp.getActiveRange().getRow();
  var CellColumn = SpreadsheetApp.getActiveRange().getColumn();

  if (CellColumn == 2){
    sheet.getRange(CellRow, CellColumn).setFormula("=A"+CellRow+"+1");
    Browser.msgBox("DON'T MESS WITH THE FORMULAS!");
  }
}

If you get it working every time someone edits any cell in column B the script will automatically put your formula back in and give your a warning message.

share|improve this answer

I found out that if you create a comment attached the cells that should not be edited, you can have it warn someone when they hover the mouse over the cell to edit it. It, of course, doesn't actually lock the cell, but hopefully, enough times seeing the note pop up and maybe your mom will stop manually changing those numbers.

I think you can only add comments one cell at a time, so depending on how many cells are formulas, this solution may not be viable.

share|improve this answer
    
you're right that I cannot use it in this case as the formulas are in the whole column but it will be great solution in some cases... –  Radek Feb 17 '11 at 22:33

It is not currently possible to lock a given cell. However, you can create new sheets in the spreadsheet and lock those. If you really want to prevent her from changing the formulas, you could have all of the results/formulas be in a second sheet, referring to the first sheet's data, then lock the second sheet so it would be read-only to her.

In order to reference data from one sheet in another, use the syntax sheetname!range. For example, to take the average of sheet1's column A in rows 2 through 10, you would use Average(sheet1!A2:A10).

To add sheets, use the + sign on the bottom right side of the spreadsheet. You can switch between and rename sheets using the tabs and dropdown arrows on the lower left side of the spreadsheet.

google docs screenshot

share|improve this answer
    
very very nice explanation. Thank you. Not sure if it is usable in case you have simple table and want to calculate sums of colums and rows. User usually wants to see the sums and data on one screen. Well,if she keeps deleting formulas I have a solution for that :-) –  Radek Nov 9 '10 at 4:53
    
Happy to be of help. There is also a function called importrange which can import a whole section of one sheet into another one. Using that, you could import the entire first sheet's data into the second sheet, and then display all the equations on there near the data they correspond to. Google has documentation for it, which you may find useful. It still wouldn't be a perfect solution, but would be closer since it would display all the results on one page with the original data at least. –  nhinkle Nov 9 '10 at 5:54
    
looks like this is the only solution at this time .... thank you –  Radek Dec 13 '10 at 12:08
    
No problem. If you do eventually find a better solution, please update your post; I'd be interested to hear about it. –  nhinkle Dec 13 '10 at 19:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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