How do you copy cells from one spreadsheet to a sheet in a different spreadsheet?

0
votes

I'm trying to create a script that allows a user to copy a highlighted range of cells to another, unopened, google sheet document. I can make it work if the sheet I'm copying data to is in the current document, but I can NOT figure out how to make the data copy to a different spreadsheet.

I've tried openById or openbyUrl, but I keep getting the following error:

"Target range and source range must be on the same spreadsheet."

function copyToDifferentDocument() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

// *** Have to figure out how to make the target a different document!!!! ***

  var target = SpreadsheetApp.openById("targetsheetIDgoeshere");
    
  /* 
  Next we need to pick the particular sheets within those spreadsheets.
  Let's say your row is on the sheet named "New Stuff", and you have a sheet in the target spreadsheet named "Archive".
  */

  var source_sheet = ss.getSheetByName("New Stuff");
  var target_sheet = target.getSheetByName("Archive");
    
  // The below makes the highlighted cells the range that will be copied.

  var source_range = source_sheet.getActiveRange();
  var last_row = target_sheet.getLastRow();
  target_sheet.insertRowAfter(last_row);
  var target_range = target_sheet.getRange("A"+(last_row+1)+":G"+(last_row+1));  
  
  // Take the highlighted rant and put it on the last row of the target sheet.
  
  source_range.copyTo(target_range);

}

I'm trying to get the highlighted range to copy to a sheet in a different google sheet document.

1
javascriptgoogle-apps-scriptgoogle-sheetscopy-paste

1 Answers

0
votes
  • You want to copy the active range in the sheet of "New Stuff" on the active Spreadsheet to the last row in the sheet of "Archive" on the Spreadsheet of "targetsheetIDgoeshere"

If my understanding is correct, how about this modification? I think that there are 2 patterns for your situation.

Pattern 1:

In this pattern, only values are copied. Please modify as follows. If you are not required to copy the format, you can use this.

target_sheet.insertRowAfter(last_row);
var target_range = target_sheet.getRange("A"+(last_row+1)+":G"+(last_row+1));

// Take the highlighted rant and put it on the last row of the target sheet.

source_range.copyTo(target_range);

var values = source_range.getValues();
target_sheet.getRange(last_row + 1, 1, values.length, values[0].length).setValues(values);

Pattern 2:

In this pattern, both values and formats are copied. Please modify as follows. Unfortunately, copyTo() can copy the range to the range on only the same Spreadsheet. This situation can be also seen at the error message of your question. So in this modification, at first, the sheet is copied to the target Spreadsheet. Then, the range is copied.

target_sheet.insertRowAfter(last_row);
var target_range = target_sheet.getRange("A"+(last_row+1)+":G"+(last_row+1));

// Take the highlighted rant and put it on the last row of the target sheet.

source_range.copyTo(target_range);

if (last_row > 0) target_sheet.insertRowAfter(last_row);
var target_range = target_sheet.getRange(last_row + 1, 1);
var copiedsheet = source_sheet.copyTo(target);
copiedsheet.getRange(source_range.getA1Notation()).copyTo(target_range);
target.deleteSheet(copiedsheet);

References:

If I misunderstood your question, I apologize.