Google Sheet copying and paste values and formatting between different sheets

Question

I have been trying to make a script to work for copying and pasting data between two different sheets and I have read all the posts at this forum. Many solutions are too old and don't work anymore and other simply go to different kind of error. I have read Google documentation and tried some stuff, but I could not make it works. I will post here one script I have found and modified that comes closer to what I need, but it fails in some aspects. I need only values (and if possible, formatting too), I don't want to past any of the formulas from the source sheet.

function copiarValores2(){
  var source = SpreadsheetApp.openById('1hWTdUNhTEohOqg-UGNGV4XwY7nDOX5Ys9piz256QeIc');
  var sourceS = source.getSheets()[0];
  var range = sourceS.getRange('A1:S230');
  var destination = SpreadsheetApp.openById('15zvklK7NAEBKQcusTwYPEmlH3BNw0t-6MlgQ1fqBuPs');
  var destSheet = destination.getSheets()[0];
  range.copyValuesToRange(destSheet,1,20,1,230)
}

Running this script I have the following error:

Exception: Target sheet and source range must be on the same spreadsheet.

I don't know how to fix this. Thanks for attention and help.

Answer 1

Since it is not possible to copy a range to a different spreadsheet directly, You can use this workaround:

Sample Code:

function copiarValores2() {
  var source = SpreadsheetApp.openById('1PTRabYcpVY6hLNvUByj9ZJAouogc-uMCMhiexxxxx');
  var sourceS = source.getSheetByName("Sheet1");
  var destination = SpreadsheetApp.openById('1RPQ7WOTOZZSirf60kP6nULhViCdsuGHcOxxxxxx');
  var destSheet = destination.getSheetByName("Sheet1");

  //Copy source sheet to destination spreadsheet
  newSource = sourceS.copyTo(destination);
  //Get the range from the newly copied sheet
  var range = newSource.getRange('A1:S230');

  //Copy to the destination sheet, starting from cell A1
  range.copyTo(destSheet.getRange("A1"));

  //Delete copied source sheet
  destination.deleteSheet(newSource);
}

What it does?

1. Open a source spreadsheet and select a sheet to be copied using Spreadsheet.getSheetByName(name)
2. Open a destination spreadsheet and select a sheet where the values will be pasted using Spreadsheet.getSheetByName(name)
3. Copy your source sheet to your destination spreadsheet using Sheet.copyTo(spreadsheet). This copied sheet will be your new source sheet which is located in your destination spreadsheet.

The copied sheet is named "Copy of [original name]" in your destination spreadsheet

4. Select the range you want to copy from your new source sheet then copy the range to your destination sheet using Range.copyTo(destination)

I used this method since you mentioned in your post that you also want to copy the formatting. You can use other copy methods available in Range Class based on your preference

5. Lastly, delete your new source sheet in your destination spreadsheet using Spreadsheet.deleteSheet(sheet)

---

OUTPUT:

Source Sheet:

Destination Sheet: