Google Sheet (Script) copy Sheet to another SpreadSheet and name new sheet in target SS

0
votes

I am not that good in script cause i begin, so i want to copy a sheet from a SpreadSheet (source) and copy it to my target SpreadSheet by creating a new sheet (in target SS) and name it with my value in cell C9 in my source sheet....

Actually the new sheet is create with the new name but nothing Copy in the target sheet. It seem to be my .copyTo(targetrange) who as the problem... I try a lot of think but nothing works.

can you help me!

function CopyToSpreadSheet() {

//Source sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName("Reception");
//Name new sheet in target SpreadSheet from value in cell C9
var nameSheet = sourceSheet.getRange(9, 3).getValue();

    //open target SpreadSheet
    var target = SpreadsheetApp.openById('SpreadSheet_Id');
    var newSheet = target.insertSheet();
    var Sheetname = newSheet.setName(nameSheet).getSheetName();
    //CopyTo...  
    var targetSheet = target.getSheetByName(nameSheet);
    var targetrange = targetSheet.getRange(1, 1, targetSheet.getLastRow()+1, targetSheet.getLastColumn()+1);
    var sourceRange = sourceSheet.getRange(1, 1, 80, 15).copyTo(targetrange).getValues();
    targetrange.setValues(sourceRange);
    
    
    return;
    
}```
1
google-apps-scriptgoogle-sheets

1 Answers

0
votes

Solution

The copyTo method is available for Sheet Objects and for Range Objects. If you want to use it on a Range instance as you are doing at:

    var sourceRange = sourceSheet.getRange(1, 1, 80, 15).copyTo(targetrange).getValues();

It has a limitation: you cannot copy a range to a different Spreadsheet.

Proposed modification

You can use the copyTo method of a Sheet instance in order to achieve the desired result:

function CopyToSpreadSheet() {

  //Source sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Reception");
  //Name new sheet in target SpreadSheet from value in cell C9
  var nameSheet = sourceSheet.getRange(9, 3).getValue();

  //open target SpreadSheet
  var target = SpreadsheetApp.openById('target-sheet-id');

  //CopyTo...
  var targetSheet = sourceSheet.copyTo(target);
  targetSheet.setName(nameSheet);
    
  return;
}

Reference

Sheet copyTo