Set values to ranges in a different spreadsheet

Question

Can anybody offer suggestions about how to update or set values to a range in a different spreadsheet. I know the importRange function will bring values into the active sheet, but I want to "push" a single row of data from the active sheet to a row at the bottom of a sheet called FinalData, which is in a different spreadsheet.

The data I want to "push" is populated by other code, resulting in a single row of data in a sheet called TempData. The data exists in range TempData!A2:U2.

My goal is to append data from TempData!A2:U2 to a new row at the bottom of a table called "DataFinal", which is in a completely separate google spreadsheet (but on the same "google drive".)

Here's what I tried so far:

// Row to FinalData
   var ss = SpreadsheetApp.getActiveSpreadsheet ();

   var startSheet = ss.getSheetByName("TempData");

   var sourceRange = ss.getRange ("TempData!A2:U");

   var target = SpreadsheetApp.openById("1bWKS_Z1JwLSCO5WSq1iNP1LLQpVXnspA4WkzdyxYDNY");

   var targetSheet = target.getSheetByName("DataFinal");

   var lastRow = targetSheet.getLastRow();

   targetSheet.insertRowAfter(lastRow); 

   sourceRange.copyTo(targetSheet.getRange(lastRow + 1,1), {contentsOnly: true});

When I run it I get an error that says "Target range and source range must be on the same spreadsheet.". There must be a way to do this-- any suggestions would be welcome.

Answer 1

copyTo() can be used for the same spreadsheet.

From your script, I think that you can achieve it using getValues() and setValues(), because you use contentsOnly: true. So how about this modification?

From :

sourceRange.copyTo(targetSheet.getRange(lastRow + 1,1), {contentsOnly: true})

To :

var sourceValues = sourceRange.getValues();
targetSheet.getRange(lastRow + 1, 1, sourceValues.length, sourceValues[0].length).setValues(sourceValues);

Note :

• If you want to use copyTo(), this thread might be useful for your situation.

References :

• copyTo()
• getValues()
• setValues()

If this was not what you want, please tell me. I would like to modify it.