CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Mars
Mars

Reputation: 185

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:

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

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

The SHEET system is very close to completion

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.

Upvotes: 1

Views: 2677

Answers (1)

Aurielle Perlmann
Aurielle Perlmann

Reputation: 5529

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,))

and

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

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

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);
}

Upvotes: 2

Related Questions