插入复制的细胞

Question

我的目标是创建类似于Excel的插入复制单元格的应用程序脚本函数。我想动态选择在哪里粘贴复制范围的鼠标输入，类似于您在a = sum(鼠标选择(公式中选择单元格的方式。

请帮助我了解如何使用鼠标选择一个范围，以便将复制的行粘贴在正确的位置。

function insertCopiedCells() {
 var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); 
 var copyRange = spreadsheet.getActiveRange();
 var copyRangeRows = copyRange.getNumRows();
 // This is where I want to select the range with a mouse selection
 var pasteRange = spreadsheet.getRange(rangeSelection);
 var pasteRangeRow = pasteRange.getRow();
 pasteRange.activate();
 spreadsheet.insertRowsBefore(pasteRangeRow, copyRangeRows);
 copyRange.copyTo(pasteRange);
}

Answer 1

您将需要这样的smth：

function insertCopiedCells() {
 var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); 
 var copyRange = spreadsheet.getActiveSheet().getActiveRange();
 var data = copyRange.getValues();
 // This is where I want to select the range with a mouse selection
 var cell = Browser.inputBox('Enter the cell address');
 var pasteCell = spreadsheet.getRange(cell)
 var h = data.length;
 var l =  data[0].length;
 spreadsheet.insertRowsBefore(pasteCell.getRow(), h)
 var pasteRange = pasteCell.offset(0, 0, h, l);
 pasteRange.setValues(data);
}

从自定义菜单启动它：

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Menu')
      .addItem('Copy Selected', 'insertCopiedCells')
      .addToUi();
}