How to copy a range of data to another google sheet using google apps script?

Question

Source:

Destination:

Hi everyone,

I have 2 google sheets, Source & Destination. I want to copy a range of data from Source sheet to Destination sheet by using google apps script. So the data should appear at the last row of the Destination sheet. This is my code:

function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Source");
  var pasteSheet = ss.getSheetByName("Destination");

  // get source range
  var source = copySheet.getRange(3,1,1,10);
  // get destination range
  var destination = pasteSheet.getRange(pasteSheet.getLastRow()+1,1,1,10);

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

}

Seems like the script only allow me to do the copying within one google sheet (different tab) instead of 2 different google sheet. May I know how should I modified it so that the script able to find my destination google sheet and paste the data at the last row? Any help will be greatly appreciated!

Answer 1

Use Range.getValues() and Range.setValues()

  var destination = pasteSheet.getRange(pasteSheet.getLastRow()+1,1,1,10);
  const values = source.getValues();
  destination.setValues(values);

Answer 2

The method getSheetByName is used to define a sheet within a spreadsheet. To define a specific spreadsheet, you should use openById and associate the spreadhseet ID (https://docs.google.com/spreadsheets/d/{spreadsheet ID}/edit).

Define your source sheet as:

var copysheet = SpreadsheetApp.openById('enter id for Source').getSheetByName('Sheet 1')

Define your destination sheet as:

var pastesheet = SpreadsheetApp.openById('enter id for Destination').getSheetByName('Sheet 1')