CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
HM Production
HM Production

Reputation: 37

Copy a range from another spreadsheet to another

I stumbled upon a code here a while ago which works to copy a range from one sheet to another within the same spreadsheet.

var source_sheet = ss.getSheetByName("Sheet1");
var target_sheet = target.getSheetByName("Sheet2");

var url = "#gid=" + newSheet.getSheetId()

var source_range = source_sheet.getActiveRange();
source_range = source_sheet.getRange(source_range.getRow(), 1, 1, 3);
source_sheet.getRange("I" + source_range.getRow()).setFormula('=IFERROR(Sheet2!C4," ")')
source_sheet.getRange("K" + source_range.getRow()).setFormula('=HYPERLINK("' + url + '","Generated Sheet")')
var target_range = target_sheet.getRange("B4");

source_range.copyTo(target_range);

The script works as intended but now I want to copy from one spreadsheet to another. And when I set the a sheet from a different spreadsheet as the target sheet, I get the error of source and target must be within the same spreadsheet.

Therefore, is there any workaround for this? I was think maybe using importrange, but how do I specify the URL and that selected range in the source range to be imported using the insert function script. If you can think of any other solution, please do let me know.

Upvotes: 0

Views: 366

Answers (1)

Tedinoz
Tedinoz

Reputation: 7949

source_range.copyTo(target_range);

You're getting this error message because copyTo() method is based on a range AND assumes that the source and target are in the same spreadsheet.

The more straightforward method to copy a range is to use setValues, even though there are two spreadsheets and two different sheets. The following script demonstrates this.

function so5894550501() {

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sourcesheetname = "sourcesheet";
  var source = ss.getSheetByName(sourcesheetname);

  var targetid = "<insert targetid>";
  var ts = SpreadsheetApp.openById(targetid);
  var targetsheetname = "targetsheet";
  var target = ts.getSheetByName(targetsheetname);

  var sourceRange = source.getRange(1,1,source.getLastRow(),source.getLastColumn());
  Logger.log("the source range = "+sourceRange.getA1Notation())
  var sourceValues = sourceRange.getValues();
  Logger.log(sourceValues);
  Logger.log(sourceValues.length)


  var targetRange = target.getRange(1,1,source.getLastRow(),source.getLastColumn());
  targetRange.setValues(sourceValues);// this works

}

Upvotes: 2

Related Questions