How do i copyPaste through AppScript into another file instead of a sheet

Question

Right now I am using this formula to copy and paste to another Sheet. Though due to the file getting bigger and bigger, we want to place it in a seperate file which we dont really have to open.

function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Kenmerken Script");
  var pasteSheet = ss.getSheetByName("CopyPaste");

  // get source range
  var source = copySheet.getRange(3,3,300,6);
  // get destination range
  var destination = pasteSheet.getRange(pasteSheet.getLastRow()+2,1,500,4);

  // copy values to destination range
  source.copyTo(destination);

  // clear source values
  source.clearContent();
} 

I tried to use getsheetbyURL instead. This did not work as it gave an error.

I also tried to find more information on https://developers.google.com/apps-script/reference/spreadsheet/sheet#copyTo(Spreadsheet). Though I cannot find an clear answer here.

I tried to add another "var" but "var sss = SpreadsheetApp.getActiveSpreadsheet. And then put pastesheet = sss.getsheetbyURL". This didnt work either.

I understand the things in the code which I have now. I only need to find the correct string.

Answer 1

I believe your goal is as follows.

• You want to copy C3:H302 of a source sheet of Spreadsheet "A" to the last row of a destination sheet of Spreadsheet "B".
• You want to use Spreadsheet URL for retrieving the Spreadsheet.

In this case, when your showing script is modified, how about the following modification?

Modified script:

function copyInfo() {
  var destinationSpreadsheetUrl = "###"; // Please set your Spreadsheet URL.
  var destinationSheetName = "###"; // Please set the destination sheet name.

  // Source sheet.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Kenmerken Script");

  // Destination sheet.
  var dstSS = SpreadsheetApp.openByUrl(destinationSpreadsheetUrl);
  var dstSheet = dstSS.getSheetByName(destinationSheetName);
  var temp = copySheet.copyTo(dstSS);
  temp.getRange(3, 3, 300, 6).copyTo(dstSheet.getRange(dstSheet.getLastRow() + 2, 1));

  // clear source values
  copySheet.getRange(3, 3, 300, 6).clearContent();

  // Remove temp sheet.
  dstSS.deleteSheet(temp);
}

• When this script is run, the values of "C3:H302" are copied from the source sheet of the active Spreadsheet to the destination sheet of another Spreadsheet. And, "C3:H302" of the source sheet is cleared.

• If you want to use Spreadsheet ID, please use SpreadsheetApp.openById() instead of SpreadsheetApp.openByUrl().

• If you want to copy only the values, I thought that getValues and setValues can be also used.

References:

• openByUrl(url)
• copyTo(spreadsheet)

Added:

From your following reply,

The formula itself works which is great. But it seems to take the formulas and not only the values. See picture: i.imgur.com/VRYCuTP.png . This causes an error since this file is not aware of any sheet named X. Would there be a way to only copy the values? In this case, how about the following sample script?

Sample script:

function copyInfo() {
  var destinationSpreadsheetUrl = "###"; // Please set your Spreadsheet URL.
  var destinationSheetName = "###"; // Please set the destination sheet name.

  // Source sheet.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Kenmerken Script");
  var temp1 = copySheet.copyTo(ss);
  var r = temp1.getDataRange();
  r.copyTo(r, { contentsOnly: true });

  // Destination sheet.
  var dstSS = SpreadsheetApp.openByUrl(destinationSpreadsheetUrl);
  var dstSheet = dstSS.getSheetByName(destinationSheetName);
  var temp2 = temp1.copyTo(dstSS);
  temp2.getRange(3, 3, 300, 6).copyTo(dstSheet.getRange(dstSheet.getLastRow() + 2, 1));

  // clear source values
  copySheet.getRange(3, 3, 300, 6).clearContent();

  // Remove temp sheets.
  ss.deleteSheet(temp1);
  dstSS.deleteSheet(temp2);
}