Google Spreadsheet: Insert Rows and copy down hidd

问题:

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.

回答1:

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

A) Creates a new menu in the Spreadsheet UI. http://goo.gl/qCPRC

B1) SpreadSheet getActiveCell http://goo.gl/1wiBp

B2) Displaying a User Interface from a Spreadsheet http://goo.gl/nL4y4

C) Copy the content of the range to the given location. http://goo.gl/WRfcc

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.

Good luck!