CODEWITHSUNDEEP
google-apps-script
Burt Lewis
Burt Lewis

Reputation: 21

How to copy last row from google sheet and copy to last row of other google sheet

I've tried every example and have been working on this for days, appreciate any help. I just want to copy the last row from one sheet and copy it to the last row of another sheet. This last example copies all the rows, not just the last row.

I have tried every sample and example I could find without success.

function lastrow2() {
var source = SpreadsheetApp.openById('MY ID');
var sourcesheet = source.getSheetByName('sheet1');
var target = SpreadsheetApp.openById('MY ID')
var targetsheet = target.getSheetByName('sheet2');
var targetrange = targetsheet.getRange(1, 1, sourcesheet.getLastRow(), 
sourcesheet.getLastColumn());
var rangeValues = sourcesheet.getRange(1, 1, sourcesheet.getLastRow(), 
sourcesheet.getLastColumn()).getValues();
targetrange.setValues(rangeValues); 
}

I expected just to get the last row from the source sheet and copy it into the last row of the target sheet, instead it copies all the rows.

Upvotes: 0

Views: 3734

Answers (2)

Tanaike
Tanaike

Reputation: 201378

  • You want to copy a values of last row of Sheet1 to the last row of Sheet2.

If my understanding is correct, how about this modification?

From:

var targetrange = targetsheet.getRange(1, 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn());
var rangeValues = sourcesheet.getRange(1, 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn()).getValues();

To:

var targetrange = targetsheet.getRange(targetsheet.getLastRow() + 1, 1, 1, sourcesheet.getLastColumn());
var rangeValues = sourcesheet.getRange(sourcesheet.getLastRow(), 1, 1, sourcesheet.getLastColumn()).getValues();

Note:

If you want to overwrite to the last row of Sheet2, please modify from targetsheet.getRange(targetsheet.getLastRow() + 1, 1, 1, sourcesheet.getLastColumn()) to targetsheet.getRange(targetsheet.getLastRow(), 1, 1, sourcesheet.getLastColumn()).

Reference:

If I misunderstood your question, please tell me. I would like to modify it.

Edit:

From your comment, how about this modification?

From:

var targetsheet = targetsheet.getRange(targetsheet.getLastRow(), 1, 1, sourcesheet.getLastColumn());

To:

var targetrange = targetsheet.getRange(targetsheet.getLastRow() || 1, 1, 1, sourcesheet.getLastColumn());

Upvotes: 1

Cooper
Cooper

Reputation: 64032

Copy lastrow of one spreadsheet to the last row of another spreadsheet

Neither spreadsheet is the script container in my tests

But one of them could have been.

function copyLastToLast() {
  var sss=SpreadsheetApp.openById('1u1d-eJVnR_UdAY_1ZnuF0kF3LLdnq30XIKW3wnQSFCY');
  var ssh=sss.getSheetByName('Sheet1');
  var srg=ssh.getRange(ssh.getLastRow(),1,1,ssh.getLastColumn());
  var svA=srg.getValues();
  var tss=SpreadsheetApp.openById('13hXYw9wrZtIi2OayGGurD2qOXZLaNcEZoculd179B_0');
  var tsh=tss.getSheetByName('sheet2');
  if(tsh.getLastRow()) {
    tsh.getRange(tsh.getLastRow(),1,1,svA[0].length).setValues(svA);
  }else{
    tsh.getRange(1,1,1,svA[0].length).setValues(svA);
  }
}

Upvotes: 1

Related Questions