I have a GAS script to copy a range of cells from one sheet to another the data inserts correctly, b-CodePudding

My users can enter new products on a separate sheet for review from the buyer. If accepted, the buyer then runs the script from the menu.

The process works perfectly, copying the new information into the price book and then clearing the data from the New Item sheet. But, the process seems to always add two blank cells, even though there are no extra rows in either sheet.

  function addNewItems() {
    //Get new item data from New Items Sheet
    var ss = SpreadsheetApp.getActive();
    var s = ss.getSheetByName("New Items");
    s.activate();

    //define non-contiguous data ranges from source sheet fpr clearing after copying
    var newInput1 = ss.getRange("New Items!A2:K");
    var newInput2 = ss.getRange("New Items!N2:O");
    var newInput3 = ss.getRange("New Items!Q2:R");
   
    // Get last row of data from source sheet
    lastR = s.getLastRow();
    // Select range of data with new item information (17 is the last column of data)
    var newItems = s.getRange(2,1,lastR,17).getValues();
    
    //Switch to target sheet to add new records into Price Book
    var ts = ss.getSheetByName("Price Book");
    ts.activate();
    
    //Find last row of Price Book data and add one to get next row
    tsIns = ts.getLastRow();

    //Start at the next row and insert the data range from the New Item sheet
    var newInsert = ts.getRange(tsIns  1,1,lastR,17).setValues(newItems);
    

    //Clear data from new item sheets after copying data to the Price Book
    newInput1.clearContent();
    newInput2.clearContent();
    newInput3.clearContent();

  }

CodePudding user response：

I don't know about 2, but definitely 1. In getRange, the third parameter is number of rows to get or set. Since you getValues() starting at row 2 you should subtract 1 from lastR since its value is the total number of rows with values including the headers.

Therefore you should change the following 2 lines of code as shown.

var newItems = s.getRange(2,1,lastR-1,17).getValues();

var newInsert = ts.getRange(tsIns  1,1,lastR-1,17).setValues(newItems);

CodePudding user response：

Try this:

function addNewItems() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName("New Items");
  let lastR = sh.getLastRow();
  var newInput1 = sh.getRange("A2:K"   lastR);
  var newInput2 = sh.getRange("N2:O"   lastR);
  var newInput3 = sh.getRange("Q2:R"   lastR);
  var newItems = sh.getRange(2, 1, lastR - 1, 17).getValues();
  var tsh = ss.getSheetByName("Price Book");
  tsIns = tsh.getLastRow();
  tsh.getRange(tsIns   1, 1, lastR - 1, 17).setValues(newItems);
  newInput1.clearContent();
  newInput2.clearContent();
  newInput3.clearContent();
}