Google Apps Script Insert Row Code no longer working with New Sheets with protection set

Question

I have used the code below for 4 years now without a problem. However with the introduction on the New Sheets from Google I am getting some the following error:

Error encountered: Service error: Spreadsheets

I should add that I have protected the whole sheet except for the range of rows where the user may insert rows. Everywhere else is protected.

The function works 100% if the sheet is unprotected.

Here is my code:

//INSERT ROWS

//Function doGet(): Creates and sets up a User Interface (UI) with a pop-up box for user to enter dersired number of rows to insert.

function doGet(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var app =UiApp.createApplication().setTitle('Insert Rows').setHeight(120).setWidth(250);

  // Create a grid with text box and corresponding labels. 
  // Text entered into the text box is passed in to numRows.
  // The setName extension will make the widget available by the given name to the server handlers later.

  var grid = app.createGrid(3, 2);
  grid.setWidget(0, 0, app.createLabel('Number of Rows to Insert:'));
  grid.setWidget(0, 1, app.createTextBox().setName('numRows').setFocus(true).setWidth(50));
  grid.setWidget(1, 0, app.createLabel('Maximum 1000 Rows'));
  grid.setWidget(2, 0, app.createLabel(''));

  // Create a Vertical Panel and add the Grid to the Panel.

  var panel = app.createVerticalPanel();
  panel.add(grid);

  // Create a button and Click Handler.
  // Pass in the Grid Object as a callback element and the handler as a click handler.
  // Identify the function insertRows as the server click handler.

  var button = app.createButton('Insert Rows');
  var handler = app.createServerHandler('insertRows');
  handler.addCallbackElement(grid);
  button.addClickHandler(handler);

  // Add the button to the Panel, add Panel to the App, launch the App

  panel.add(button);
  app.add(panel);
  ss.show(app);
}

//Function insertRows(e): Makes the current Spreadsheet and Sheet active.
//Fetches the number of rows from the UI.
//Determines the current cursor position.
//Defines the hidden column range.
//Unhides the protected columns.
//Inserts the desired number of rows below the current cursor position.

function insertRows(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cursPos = sheet.getActiveCell().getRow();
  var valueRows = e.parameter.numRows;
  var lastColumn = sheet.getLastColumn();
  sheet.insertRowsAfter(cursPos, valueRows);

//Defines the source range for data to be copied to the newly inserted rows.
//Defnies the target range for where the source data is to be copied.
//Copies the source data into the target data range.
//Hides the protected columns.

  var source_range = sheet.getRange(cursPos,1,1,lastColumn);
  var target_range = sheet.getRange(cursPos+1,1,valueRows);
  source_range.copyTo(target_range);

//Closes the UI application and hands the spreadsheet back to the user.

  SpreadsheetApp.getActiveSpreadsheet().toast("...Completed", "Insert Rows:", 3);

  var app = UiApp.getActiveApplication();
  app.close();
  return app;
}

Thankyou.

Kriggs Kriggs · Accepted Answer · 2015-07-06T18:34:50

createGrid has been deprecated, you'll need to switch to HTMLService.

https://developers.google.com/apps-script/reference/ui/grid

Google Apps Script Insert Row Code no longer working with New Sheets with protection set

1 Answers