Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
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.
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.
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.
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.
The answer as per today is "Named and protected ranges" under Data menu. Works smoothly.
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.
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 asterisk 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...
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.
Simple solution from back when Excel had the same problem:
Another way you can do this is through Data Validation. After you have a cell populated with the desired contents,
Choose Data Validation and use the Custom option. For the formula enter =""
Then select "Reject input".
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.
