I have a Google Spreadsheet designed to calculate a balanced scorecard for businesses measuring employee demographics. The spreadsheet consists of several data entry cells and a number of 'hidden' columns containing specific formulae that calulates the data entered and produces a score.

I need a script that will at any row in the spreadsheet (current cursor position): a) Script to be called from the main menu (Insert Rows) b) Insert a User defined number of rows below the current cursor position (pop up UI box requesting number of rows to insert) c) Copy down the data from the row above, including all data/formulae contained in the hidden columns d) Re-hide the protected columns and then hand back to the user.

The hidden columns contain IP that I do not want the users to see, hence the hidden/protected aspect.

Can anyone help?

My script so far...

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

function doGet(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var app =UiApp.createApplication().setTitle('Insert Rows').setHeight(75).setWidth(225);
  // Create a grid with 1 text box and corresponding label. 
  // Test 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(1, 2);
  grid.setWidget(0, 0, app.createLabel('Number of Rows to Insert:'));
  grid.setWidget(0, 1, app.createTextBox().setName('numRows').setWidth(50));
  // 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('Submit');
  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) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cursPos = sheet.getActiveCell().getRow();
  var valueRows = e.parameter.numRows;
  sheet.insertRowsAfter(cursPos, valueRows);
  var app = UiApp.getActiveApplication();
  app.close();
  return app;
}

I now need to copy down the contents of the row above the current active cell (the top lh cell of the newly created rows) into the newly created rows.

I have a Google Spreadsheet designed to calculate a balanced scorecard for businesses measuring employee demographics. The spreadsheet consists of several data entry cells and a number of 'hidden' columns containing specific formulae that calulates the data entered and produces a score.

I need a script that will at any row in the spreadsheet (current cursor position): a) Script to be called from the main menu (Insert Rows) b) Insert a User defined number of rows below the current cursor position (pop up UI box requesting number of rows to insert) c) Copy down the data from the row above, including all data/formulae contained in the hidden columns d) Re-hide the protected columns and then hand back to the user.

The hidden columns contain IP that I do not want the users to see, hence the hidden/protected aspect.

Can anyone help?

My script so far...

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

function doGet(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var app =UiApp.createApplication().setTitle('Insert Rows').setHeight(75).setWidth(225);
  // Create a grid with 1 text box and corresponding label. 
  // Test 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(1, 2);
  grid.setWidget(0, 0, app.createLabel('Number of Rows to Insert:'));
  grid.setWidget(0, 1, app.createTextBox().setName('numRows').setWidth(50));
  // 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('Submit');
  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) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cursPos = sheet.getActiveCell().getRow();
  var valueRows = e.parameter.numRows;
  sheet.insertRowsAfter(cursPos, valueRows);
  var app = UiApp.getActiveApplication();
  app.close();
  return app;
}

I now need to copy down the contents of the row above the current active cell (the top lh cell of the newly created rows) into the newly created rows.

• javascript
• google-apps-script

Share Improve this question Follow edited Jun 5, 2013 at 1:48 Mogsdad 45.8k2121 gold badges163163 silver badges286286 bronze badges asked Apr 22, 2013 at 9:30 Mike EburneMike Eburne 35133 gold badges88 silver badges2323 bronze badges 1

• What have you tried? – EmmyS Commented Apr 24, 2013 at 21:51

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 2

The following are apps-script "ingredients" that you can use to acplish your needs:

A) Creates a new menu in the Spreadsheet UI

B1) SpreadSheet getActiveCell

B2) Displaying a User Interface from a Spreadsheet

C) Copy the content of the range to the given location

D) No need to "unhide" the data is still available to read/copy. You do realize that hiding is no security at all since the user can "unhide" from the data menu.