How to protect from editing only rows that are odd numbered?

Question

In Google Sheets how do I protect the rows in Column A which are odd from any kind of edits?

I would like to use this function

=ISODD(ROW(A1))

Protected sheets and ranges gives you this by default

Sheet1!A1

I can do this

Sheet1!A1:A1000

which will protect all 1000 rows but how do I use functions in that so I can only use ODD rows.

Here is a picture of that feature:

ODD rows

`google-apps-script` but see https://code.google.com/p/google-apps-script-issues/issues/detail?id=1721&q=range%20protection&colspec=Stars%20Opened%20ID%20Type%20Status%20Summary%20Component%20Owner (still not possible to manipulate range protection with GAS). — AdamL, Aug 18 '14 at 06:17

score 1 · Accepted Answer · answered Aug 18 '14 at 06:33

1

As mentioned in the comments on your question, I don't believe there is currently any way of manipulating range protection with Google Apps Script.

So the only option that I can think of is to manually apply the protection to 500 individual cells (in your example).

A workaround - that is not particularly tidy - is to use data validation to thwart (ultimately not prevent) entering data in even rows, with this sort of arrangement (accessed from Data, Validation...):

Data validation example

Savvy users who have access to the spreadsheet will be able to go in to data validation and circumvent this, though.

answered Aug 18 '14 at 06:33

AdamL

23,691
6
68
59

Sorry, the "Show help" message doesn't really make sense, because it will appear in odd rows as well. But you might think of something more appropriate. – AdamL Aug 18 '14 at 06:35
It's perfect better then locking imo since it doesn't gray the cells for other people. any way to protect all cells without graying (probably also using this method of data validation) – SSpoke Aug 19 '14 at 04:54
The user can uncheck Protected ranges in the View menu to not show the grey shaded area. But that might not be the usage you are looking for. – AdamL Aug 19 '14 at 05:34
yeah I want it by default for any user ya.. ah it's good enough. – SSpoke Aug 19 '14 at 07:28
The issue 1721, pointed on the "currently" link was fixed on Feb 2015. Reference https://code.google.com/p/google-apps-script-issues/issues/detail?id=1721#c166. See the other [answer](http://stackoverflow.com/a/31052398/1595451) – Rubén Dec 12 '16 at 02:31

score 1 · Answer 2 · answered Jun 25 '15 at 13:53

Google has created api for protecting sheets using google app script.

For example, to protect range A1:B10, You can run

var ss = SpreadsheetApp.getActive();
var range = ss.getRange('A1:B10');
var protection = range.protect().setDescription('Sample protected range');

If You want to protect rows alternatively, You can try something like this

function alternateRowProtection() {
  var totalRows = SpreadsheetApp.getActiveRange().getNumRows();
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = 1;
  while (row < totalRows)
  {
      if(row%2 == 0){
           sheet.getRange(row).protect().setDescription('This is protected.');    
      }
    row++;
  }
}

How to protect from editing only rows that are odd numbered?

2 Answers2