Copy Paste From A to B by range

Question

I want to copy data from Sheet A to Sheet B and paste it to the last row of the Sheet B. The data has different categories which is i put as Col 1-Col7 and Col A-Col E. For Example:

Sheet A

Sheet B

when i run the code, the data will paste at the last row like this:

after run code

Is it possible if i want to copy and paste by range? so that there's no blank row?

//TEST
////Get Data from Sheet A and Paste to Sheet B
function CopyandPaste() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mysheet = ss.getSheetByName('Sheet B');
  var sourceSheet = ss.getSheetByName('Sheet A');
  var sourceRange = sourceSheet.getRange("Sheet A!A2:L");
  var rlast = mysheet.getLastRow();
  if (rlast === mysheet.getMaxRows()) mysheet.insertRowsAfter(rlast, 1); // Avoid error if last row with content is last row of the sheet
  var destRange = mysheet.getRange(rlast + 1, 1);
  sourceRange.copyTo(destRange,{contentsOnly:true});

};

score 0 · Accepted Answer · answered Mar 05 '23 at 11:56

In your script, it seems that the values of Sheet A!A2:L of "Sheet A" sheet are copied to the last row of "Sheet B" sheet. I think that this might be the reason for your current issue. From your question, when you want to use the ranges of columns A:G and columns H:L by separating, how about the following modification?

Modified script:

function CopyandPaste() {
  // Ref: https://stackoverflow.com/a/44563639
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
    return search ? search.getRow() : offsetRow;
  };

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mysheet = ss.getSheetByName('Sheet B');
  var sourceSheet = ss.getSheetByName('Sheet A');
  var lastRowOfcolAToG = mysheet.get1stNonEmptyRowFromBottom(1) + 1;
  var lastRowOfcolHToL = mysheet.get1stNonEmptyRowFromBottom(8) + 1;
  if (lastRowOfcolAToG >= mysheet.getMaxRows() || lastRowOfcolHToL >= mysheet.getMaxRows()) mysheet.insertRowsAfter(mysheet.getMaxRows(), 1);
  sourceSheet.getRange(2, 1, sourceSheet.get1stNonEmptyRowFromBottom(1) - 1, 7).copyTo(mysheet.getRange(lastRowOfcolAToG, 1), { contentsOnly: true });
  sourceSheet.getRange(2, 8, sourceSheet.get1stNonEmptyRowFromBottom(8) - 1, 5).copyTo(mysheet.getRange(lastRowOfcolHToL, 8), { contentsOnly: true });
}

In this modification, the ranges of columns A:G and columns H:L are separated, and each range of the source sheet is copied to the last row of each range of the destination sheet.
In this case, the ranges of columns A:G and columns H:L are used independently. So, even when the last row of columns H:L is smaller than that of columns A:G, this script works.

Copy Paste From A to B by range

1 Answers1

Modified script: