Google New Sheets Protection Issues

Question

I am experiencing some strange problems with protection in Google New Sheets. I have created a demo of what I am experiencing. The URL to the sheet is:

https://docs.google.com/spreadsheets/d/1IbAiqU6oN48Ql_wM3TeRl9TqG6DFsBKtc86jElv0Kbo/edit?usp=sharing

I have protected the sheet for edit by owner only except for rows 5 to 7 using the 'Sheet protect except certain cells' method under 'Data - Protected sheets and ranges...'

I also have a simple User Function menu which is invoked on open wich contains a simple Google Apps Script to insert a given number of rows (code below).

The following is happening when another user accesses the sheet:

The basic protection seems to be working. The user can only edit the rows 5 to 7.
The insert row function (selected under User Functions menu) produces a 'Service error: Spreadsheets'.
If the user tries to delete any of the 3 unprotected rows then the message 'Can't save your changes. Copy any recent changes, then revert to an earlier version...' appears in a red box at the top of the screen. Clicking on the 'revert to an earlier version...' link reverses the delete.

If I remove all protection then everything is 100% for the user - insert rows funtion - delete rows etc.

The functionality I have reproduced here is very similar to what I have been using in the old sheets for years without any problems (i.e. protecting certain areas of the spreadsheet from edit by shared users).

I must add, I posted the issue about the insert row function not working a couple of days ago.

Here's my function code:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [{name: "Insert Rows", functionName: "insertRows"}];
  ss.addMenu("User Functions", menuEntries); 
}

function insertRows() {
  var numRows = Browser.inputBox('Insert Rows', 'Enter the number of rows to insert', Browser.Buttons.OK);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var curs = sheet.getActiveCell();
  var cursRow = curs.getRow();
  var cursLastCol = sheet.getLastColumn();
  sheet.insertRowsAfter(cursRow, numRows);

  var source_range = sheet.getRange(cursRow,1,1,cursLastCol);
  var target_range = sheet.getRange(cursRow+1,1,numRows);
  source_range.copyTo(target_range);

  Browser.msgBox('Insert Rows', +numRows+' rows successfully inserted.', Browser.Buttons.OK);

}

Can anyone help with this. I have some large customers I have built complex online spreadsheets for that now don't function correctly under New Sheets.

Is there anyone who can help with this. I am getting desperate... — Mike Eburne
It looks like you filed a bug here, although with a lot less information: code.google.com/p/google-apps-script-issues/issues/…. It's generally a better idea to isolate the problem down to a small case that reproduces the problem, rather than including your entire code. — Eric Koleda
Follow up on the issue tracker, as that is the best place to report bugs. — Eric Koleda

n2ygk n2ygk · Accepted Answer · 2015-08-20T16:21:21

Try setting up a project trigger for onOpen() rather than using the simple onOpen(). Then it runs with your privileges rather than those of the current user. Just click on Resources/Current Project Triggers and add a new trigger.

Google New Sheets Protection Issues

1 Answers