将行的动态范围复制/附加到另一个工作表 [英] Copy/Append dynamic range of rows to another sheet

问题描述

我询问是否有方法循环应用程序脚本，以根据用户输入的订单项数量将标题基本信息复制到多行：

I have asked if there is a way to loop an app script to copy header base information to multiple rows based on the amount of line items entered by a user:

解决方案使用带有中间表OrderKey的arrayformulas，最终可以隐藏它。

Solution is using arrayformulas with an intermediary sheet "OrderKey" that can eventually be hidden.

我已经更新了一个工作解决方案，感谢Aurielle Perlmann（见下文）。

I have updated a working solution thanks to Aurielle Perlmann (see below).

SHEET系统非常接近完成

The SHEET system is very close to completion

请参阅我的示例表单： https://docs.google.com/spreadsheets/d/151h1XjB98NOBnO0otNaql3ASjK84CccZZ399dX4BMBM/edit#gid=0

See my example Sheet here:https://docs.google.com/spreadsheets/d/151h1XjB98NOBnO0otNaql3ASjK84CccZZ399dX4BMBM/edit#gid=0

function onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu('Custom Menu')
// creates a menu item "Submit Sales Order"
.addItem('Submit Sales Order', 'menuItem1')
.addToUi();
}


function menuItem1() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var salesheet = ss.getSheetByName("salesOrder");  

var source = ss.getSheetByName(" OrderKey"); 
// sets the 'OrderKey' Sheet as source
var target = ss.getSheetByName("Orders");
// sets 'Orders' sheet as the target for copying data to.
var sourceData = source.getSheetValues(2,1,source.getLastRow(),16);
// sets range to gather source 'OrderKey' data by finding last row, and Line 2 to Column 16
target.getRange(target.getLastRow()+1, 1, sourceData.length,16).setValues(sourceData);
// finds last row of target 'Orders' and writes to +1 row past last row up to column 16 using setValues of sourceData

// Following simply clears Sales Order Sheet so new data can be entered
salesheet.getRange('B4:B5').clearContent();
salesheet.getRange('B8').clearContent();
salesheet.getRange('G6:G8').clearContent();
salesheet.getRange('F10:G10').clearContent();
salesheet.getRange('A13:C76').clearContent();
salesheet.getRange('J13:J76').clearContent();

// Following gets seed number from cell I1, and increases value by +1 so next Sales Order ID is incremented by 1
var cell = salesheet.getRange("I1");
var cellValue = cell.getValue();
cell.setValue(cellValue + 1);

  var lastID = salesheet.getRange("F1");
  var nextID = salesheet.getRange("G1");

  var lastIDValue = lastID.getValue();
  nextID.setValue(lastIDValue + 1);
}

已更新。

UPDATED.

推荐答案

我继续在你的工作表上为你添加公式，这个公式只需要添加到标题后的第一行，其余的将自动填入

I went ahead and added the formulas on your sheet for you, this formula only needs to be added to the very first line after the header and the rest will fill in automatically

使用的两个公式是：

the two formulas used are:

=arrayformula(if(istext(K2:K),salesOrder!B4,))

和

=ARRAYFORMULA(if(istext(salesOrder!A13:A),salesOrder!A13:A,))

单元格引用会根据您尝试导入的字段而发生变化。

the cell references change depending on which fields you are trying to import.

一个脚本，然后我将它附加到一个名为submit的按钮上，该按钮获取最后一行并将该值附加到可用于归档的单独工作表上：

after doing that sheet - i added a script which I then attached to a button called "submit" which gets the last rows and appends the value onto a separate sheet you can use for archiving:

function testing(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName(" OrderKey"); 
  var sheet2 = ss.getSheetByName("testing - aurielle");
  var sourceData = sheet1.getSheetValues(2,1,sheet1.getLastRow(),14);
  sheet2.getRange(sheet2.getLastRow()+1, 1, sourceData.length,14).setValues(sourceData);
}

这篇关于将行的动态范围复制/附加到另一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！