prevent cells in being edited in google spreadsheet - google docs script

Question

I've got a master sheet that I need to view and filter data from. The data is brought into the sheet using the =Sheet2!B7 syntax.

The problem is that I keep accidentally editing the data in the master sheet which does not update the original copy.

I would like a script that alerts me if I'm in the master sheet and stops me from editing the cells. I still want to be able to filter the data so simply locking the sheet would work.

I've been trying to modify this script (posted by Yuzhy) but so far I haven't been able to get it to work.

(I'm not sure whether this is the right place for this question, it was either this or stackexchange / superuser, if it's wrong please advise.)

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!"); } }

Any help much appreciated.

I think that stackoverflow is the right place - you are asking about code. — starbeamrainbowlabs
– starbeamrainbowlabs, Commented Jul 17, 2012 at 16:26

Henrique G. Abreu · Accepted Answer · 2012-07-16 00:25:38Z

From reading your code it seems that you're trying to do a different thing from what you've explained. I see that you trying to "repair" the formulas. But why if (CellColumn == 2 )? You want to protect only column B, not the whole "master" sheet?

Can you share a sample spreadsheet of what your sheet structure looks like?

Anyway, I'll suppose that you want to warn when you overwrite anything on your "master" sheet, auto-repairing the formula is a little more complicated (depending on your scenario), and after reading the popup it's easy to just hit undo and recover it. But you really mean to auto-repair the formulas, I'll enhance my code after you share a sample of your spreadsheet, since it's a slightly complicated and depends highly on your structure.

function onEdit() { var s = SpreadsheetApp.getActiveSheet(); if( s.getName() == "Master" ) Browser.msgBox("DON'T MESS WITH THE FORMULAS!"); }

If you're doing simple reference formulas on other sheets it's somewhat easier and probably a good solution for you to use an ArrayFormula, that will auto-replace back the value when you change any cell, except for the first cell, but then a manual undo will be so rare and obvious that it will not be an issue, I think. I did an example of array formula on the spreadsheet you linked, please take a look. On A1 I wrote:

=ArrayFormula(Sheet1!A1:F20)

Now, if you go and accidentally writes something on B2 for example, it value will automatically come back. Try and see if that's a good solution for you.

hi henrique, sorry for the late reply havnt been able to get to a computer, ive made an example in google docs HERE is their a way to get it to not allow you to change the data in the first place, or once the alert box comes up to undo the change thats just been made ?
No, there's no way to prevent it from changing the cells, unless you lock the spreadsheet, which you already said is not a solution. It's also not possible to trigger a real "undo" from the script, it's only possible to have the script know beforehand which formula goes in which cell and set it back automatically. But as I said, that is not easy.
had a look at it, it the example ive put one, two, three in the cells, to just show which sheet they come from, in practice the data in all those cells would be different so would the method using the arrays work if its all different data - either way, i really appreciate the help !

sam · Accepted Answer · 2012-08-08 08:53:02Z

Update - as of 8/8/12 google has updated its spreadsheets and now allows you to have "protected ranges" - https://support.google.com/docs/bin/answer.py?hl=en&answer=63175

Collectives™ on Stack Overflow

prevent cells in being edited in google spreadsheet - google docs script

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related