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.
