google apps script - Copy a complete list on one spreadsheet to append on the bottom of another spreadsheet

Question

In Google Spreadsheet I would like to take only the values from a complete list on one spreadsheet and append it to the bottom of a list on another spreadsheet. My trouble is that using the the copyValuesToRange() function errors the following:

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

Here's my current code:

function transferList() {
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var target = SpreadsheetApp.openById("0ABCD"); 
  var target_sheet = target.getSheetByName("RFPData");
  var sheet = source.getSheetByName("RFP List");
  var sheet_last_row = sheet.getLastRow() + 1;
  var source_range = sheet.getRange("A2:I"+sheet_last_row);
  var sWidth=source_range.getWidth() + 1;
  var sHeight=source_range.getHeight() + 1;
  var last_row=target_sheet.getLastRow();
  source_range.copyValuesToRange(target_sheet , 1, sWidth, 
    last_row + 1, last_row + sHeight );
}

Any idea how I can get this to work?

Answer 1

As you've found, copyValuesToRange() is a Range method that affects a Sheet Object that is in the same Spreadsheet Object as the Range. There isn't an atomic method that will copy a range of values to another Spreadsheet, but there are a number of ways you could do it.

Here's one way.

• From the source sheet, get all the data in one operation, by selecting the complete range of data using getDataRange() and then grabbing all values into a javascript array with getValues().
• To ignore the first row of headers, use the javascript array method splice().
• Locate your destination, which is on the target sheet, starting after the last row of data that's currently there, using getLastRow().
• Write the source data (without the headers) to the destination sheet starting at the next row, using setValues().

Script:

function transferList() {
  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("RFP List");
  var sourceData = sourceSheet.getDataRange().getValues();
  sourceData.splice(0,1);  // Remove header
  var targetSS = SpreadsheetApp.openById("0ABCD").getSheetByName("RFPData");
  var targetRangeTop = targetSS.getLastRow(); // Get # rows currently in target
  targetSS.getRange(targetRangeTop+1,1,sourceData.length,sourceData[0].length).setValues(sourceData);
}