Copy/paste incremental data from one tab to another tab

Question

I have little coding experience but can usually cobble together a solution by looking at examples. This time have not been able to get it working or found examples within my skill range to parse.

Essentially I'd like to copy data from tab 1 and place it underneath the running data on tab 2, but only when there is data on tab 1 to copy.

I started with the following which does work, but doesn't check for available data, or vary the size the of the range of data captured so it runs and pastes a lot of blank space.

   function CopyRows() {
    var source = SpreadsheetApp.openById('key');
    var sourcesheet = source.getSheetByName('ImportPage');
    var target = SpreadsheetApp.openById('key')
    var targetsheet = target.getSheetByName('TargetPage');
    var targetrange = targetsheet.getRange(targetsheet.getLastRow() + 1 , 1);
    sourcesheet.getRange(2, 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn()).copyTo(targetrange);}

I think that what I need to add to this is a way to vary the range of data captured, and to halt the function if no data is present.

I have the data coming in from another sheet though the importrange function. it might be better to do the whole thing via script but that part is working well enough for now.

Any advice would be appreciated.

Alan Wells Alan Wells · Accepted Answer · 2016-01-09T02:02:16

This might work:

function CopyRows() {
  var source = SpreadsheetApp.openById('key');
  var sourcesheet = source.getSheetByName('ImportPage');

  var target = SpreadsheetApp.openById('key')
  var targetsheet = target.getSheetByName('TargetPage');
  var lastRowOfTrgtSheet = targetsheet.getLastRow(); 

  var srcData = sourcesheet
      .getRange(2, 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn())
      .getValues();
  targetsheet.getRange(lastRowOfTrgtSheet+1, 1, srcData.length, srcData[0].length)
      .setValues(srcData);

};

It uses the setValues() method instead of copyTo().

If you want to use copyTo(), you'll need to use the version of getRange() that takes 4 parameters, and subtract 1 from the length of the rows in the source sheet:

function CopyRows() {
  var source = SpreadsheetApp.openById('key');
  var sourcesheet = source.getSheetByName('ImportPage');

  var target = SpreadsheetApp.openById('key')
  var targetsheet = target.getSheetByName('TargetPage');
  var lastRowOfTrgtSheet = targetsheet.getLastRow(); 
  var srcLastRw = sourcesheet.getLastRow();

  var targetrange = targetsheet
      .getRange(lastRowOfTrgtSheet + 1, 1, srcLastRw-1, sourcesheet.getLastColumn());

  sourcesheet.getRange(2, 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn())
    .copyTo(targetrange);
};

Copy/paste incremental data from one tab to another tab

1 Answers