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

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.

CodePudding user response：

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:

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);
}