Google Spreadsheet：插入行并复制隐藏的内容 [英] Google Spreadsheet: Insert Rows and copy down hidden content

问题描述

我有一张Google Spreadsheet，旨在为衡量员工人口统计特征的企业计算平衡计分卡。电子表格包含多个数据输入单元格和一些包含特定公式的隐藏列，这些公式可以计算输入的数据并生成分数。

我需要一个脚本在电子表格的任何一行（当前光标位置）：
a）从主菜单调用的脚本（插入行）
b）在当前光标位置下方插入用户定义的行数（弹出UI box请求插入的行数）
c）从上面的行中复制数据，包括隐藏列中包含的所有数据/公式
d）重新隐藏受保护的列，然后交还给用户。

隐藏的列包含我不希望用户看到的IP，因此隐藏/保护方面。

到目前为止我的脚本...

  function onOpen（）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var menuEntries = [{name：Insert Rows，functionName：doGet}]; 
 ss.addMenu（用户函数，menuEntries）; 
} 
 
函数doGet（e）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var app = UiApp.createApplication（）。setTitle（'Insert Rows'）。setHeight（75）.setWidth（225）; 
 //创建一个带有1个文本框和相应标签的网格。 
 //输入到文本框中的测试被传递给numRows。 
 // setName扩展名将使得该部件稍后可以通过给定名称提供给服务器处理程序。 
 var grid = app.createGrid（1，2）; 
 grid.setWidget（0，0，app.createLabel（'要插入的行数：'））; 
 grid.setWidget（0，1，app.createTextBox（）。setName（'numRows'）。setWidth（50））; 
 //创建一个垂直面板并将网格添加到面板。 
 var panel = app.createVerticalPanel（）; 
 panel.add（grid）; 
 //创建一个按钮并单击处理程序。 
 //将网格对象作为回调元素并将处理程序作为点击处理程序传入。 
 //将函数insertRows标识为服务器点击处理程序。 
 var button = app.createButton（'Submit'）; 
 var handler = app.createServerHandler（'insertRows'）; 
 handler.addCallbackElement（grid）; 
 button.addClickHandler（handler）; 
 //将按钮添加到面板，将面板添加到应用程序，启动App 
 panel.add（button）; 
 app.add（panel）; 
 ss.show（app）; 
} 
 
函数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（）; 
返回应用程序; 
} 
  

现在我需要复制当前活动单元格上方的行内容（新创建的行的顶部lh单元格）添加到新创建的行中。 解决方案

以下是应用程序脚本 您可以使用它来完成您的需求：

A）在电子表格UI中创建一个新菜单。 http://goo.gl/qCPRC

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

B2）从电子表格显示用户界面 http://goo.gl/nL4y4

$ b）将b范围的内容复制到给定的位置。 http://goo.gl/WRfcc

D ）无需取消隐藏数据仍然可以读取/复制。你确实意识到隐藏并不是安全的，因为用户可以从数据菜单中取消隐藏。

祝你好运！

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.

解决方案

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!

这篇关于Google Spreadsheet：插入行并复制隐藏的内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！