Google Sheets - App Script - Copy/Append dynamic range of rows to another sheet -

i have asked if there way loop app script copy header base information multiple rows based on amount of line items entered user:

solution using arrayformulas intermediary sheet "orderkey" can hidden.

i have updated working solution aurielle perlmann (see below).

the sheet system close completion

see 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 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 'orderkey' sheet source var target = ss.getsheetbyname("orders"); // sets 'orders' sheet target copying data to. var sourcedata = source.getsheetvalues(2,1,source.getlastrow(),16); // sets range gather source 'orderkey' data finding last row, , line 2 column 16 target.getrange(target.getlastrow()+1, 1, sourcedata.length,16).setvalues(sourcedata); // finds last row of target 'orders' , writes +1 row past last row column 16 using setvalues of sourcedata  // following clears sales order sheet new data can 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 cell i1, , increases value +1 next sales order id incremented 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 , added formulas on sheet you, formula needs added first line after header , rest fill in automatically

the 2 formulas used are:

=arrayformula(if(istext(k2:k),salesorder!b4,)) 

and

=arrayformula(if(istext(salesorder!a13:a),salesorder!a13:a,)) 

the cell references change depending on fields trying import.

after doing sheet - added script attached button called "submit" gets last rows , appends value onto separate sheet can use 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); }