CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
LAD Service Desk
LAD Service Desk

Reputation: 289

Google Script: How to CopyRows from one sheet to another spreadsheet

I'm trying to copy rows from one sheet to another spreadhsheet using google sciprt but the same is not working.

Please could you help me or explain me how to do this works?

   function CopyRows() {
    var source = SpreadsheetApp.openById('XXXXXX');
    var sourcesheet = source.getSheetByName('sheet1');

    var target = SpreadsheetApp.openById('YYYYY')
    var targetsheet = target.getSheetByName('sheet1');
    var targetrange = targetsheet.getRange(targetsheet.getLastRow() + 1 , 1);
    sourcesheet.getRange(2, 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn()).copyTo(targetrange);

   }

Do you know if exist anyway to copy rows from one sheet to another sheet in other spreadsheet?

Best Regards

EDIT: 01/07/2013

Thanks PhysLabTsar , your script works very well for copyrows from one sheet to other spreadsheet.

   function myFunction() {
     var source = SpreadsheetApp.openById('xxxxxx');
       var sourcesheet = source.getSheetByName('sheet1');

       var target = SpreadsheetApp.openById('xxxxx')
       var targetsheet = target.getSheetByName('sheet1');
var targetrange = targetsheet.getRange(2, 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn());
       var rangeValues = sourcesheet.getRange(2, 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn()).getValues();
       targetrange.setValues(rangeValues);

}

EDIT: CopyRows from Multiples Sheets

   function ShellCopyValues(){
     var sheets = ['sheet1','sheet2','sheet3','sheet4','sheet4'];
     for (var s in sheets){
   CopyRows(sheets[s]);
     }
   }

   function CopyRows(uname) {
     var source = SpreadsheetApp.openById('XXXXXX');
       var sourcesheet = source.getSheetByName(uname);

       var target = SpreadsheetApp.openById('YYYYYY')
       var targetsheet = target.getSheetByName(uname);
       var targetrange = targetsheet.getRange(2, 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn());
       var rangeValues = sourcesheet.getRange(2, 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn()).getValues();
       targetrange.setValues(rangeValues);

   }

If anyone have a best way to do it. Please feelfree to letme know.

Best Regards,

Upvotes: 1

Views: 5386

Answers (1)

PhysLabTsar
PhysLabTsar

Reputation: 266

I believe your problem is the copyTo() method. That only works when the target is in the same spreadsheet. To fix this you can grab the range that you want from the initial spreadsheet and use getValues() to store them in an object ARRAY. Then open up your final spreadsheet and grab a same size range and use setValues() to dump them. The size of the final range must match the size of the initial range.

Here is something that should work:

function myFunction() {
  var source = SpreadsheetApp.openById('xxxxxx');
    var sourcesheet = source.getSheetByName('sheet1');

    var target = SpreadsheetApp.openById('xxxxx')
    var targetsheet = target.getSheetByName('sheet1');
    var targetrange = targetsheet.getRange(2, 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn());
    var rangeValues = sourcesheet.getRange(2, 1, sourcesheet.getLastRow(), sourcesheet.getLastColumn()).getValues();
    targetrange.setValues(rangeValues);

}

I hope this helps. This is my first answer in this place, so I'm sure there is a better way that someone else knows about. But it is the way I've been using. :)

Upvotes: 3

Related Questions