How to copy a row from one google spreadsheet to another google spreadsheet using google apps script?

Question

I wanted to copy a particular row from one spreadsheet to another spreadsheet using google apps script.

Can anyone please help me to get the answer for this.

Answer 1

NOTE: This solution works for copying rows from one sheet to another sheet in the SAME spreadsheet, and does NOT work for copying a row from a sheet to a DIFFERENT spreadsheet.

Check out the documentation here:

http://code.google.com/googleapps/appsscript/service_spreadsheet.html

Let's assume you're working in the spreadsheet where you're copying from.

You'd have to get a handle to the current spreadsheet and the target spreadsheet. You'll need to get the ID for the target spreadsheet. Details are in the link up there.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var target = SpreadsheetApp.openById("abc1234567");

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");

Now, the concept that google apps spreadsheets use are ranges. A range is just a chunk of cells. So we need to determine the from-range and the to-range. Let's say your sheet has 7 columns and you want the 10th row.

var source_range = source_sheet.getRange("A10:G10");
var target_range = target_sheet.getRange("A1:G1");

So we're going to take that row and put it on the first row of the target sheet. Now for the actual copy:

source_range.copyTo(target_range);

And you're done!

Now, this will always clobber the first row of the target sheet. There's plenty you can do to stop that. Instead of always using the first line, you could use the sheet object methods to find the last row, add one after, and then use it as your range.

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));

That way each time you copy a row over, it just gets added to the bottom of the sheet.

Answer 2

The copyTo method is poorly documented, because it does not allow you to copy contents to a sheet in another spreadsheet.

You may want to use the getValues ans setValues like below:

function CopyDataToNewFile() {
  var sss = SpreadsheetApp.openById('0AjN7uZG....'); // sss = source spreadsheet
  var ss = sss.getSheetByName('Monthly'); // ss = source sheet
  //Get full range of data
  var SRange = ss.getDataRange();
  //get A1 notation identifying the range
  var A1Range = SRange.getA1Notation();
  //get the data values in range
  var SData = SRange.getValues();

  var tss = SpreadsheetApp.openById('8AjN7u....'); // tss = target spreadsheet
  var ts = tss.getSheetByName('RAWData'); // ts = target sheet
  //set the target range to the values of the source data
  ts.getRange(A1Range).setValues(SData);

}  

Answer 3

I ran into this as well. I managed to figure out a nice workaround that copies everything from one sheet to another (except for images, graphs, scripts and stuff). It copies formulas, values, formating for sure.

Basically the solution is to

• copy the source sheet to the target spreadsheet as a temp source sheet
• copy the data from the temp source sheet to the target sheet
• remove the temp source sheet

Code:

var sss = SpreadsheetApp.openById(spreadsheet); // sss = source spreadsheet
var ss = sss.getSheetByName(sheet); // ss = source sheet
var ss_temp = ss.copyTo(targetsheet);
var tss = SpreadsheetApp.openById(spreadsheet); // tss = target spreadsheet
var ts = tss.getSheetByName(sheet); // ts = target sheet
var range2Copy = ss_temp.getRange(range);
range2Copy.copyTo(ts.getRange(range));
bss.setActiveSheet(ss_temp);
bss.deleteActiveSheet()

Based on the above I created a spreadsheet that allows me to update many copies from a master spreadsheet. With about 15 copies, it saves a lot of copy-paste actions.

Answer 4

Another way of doing it would be to import the row (or any range) from the source into the target spreadsheet.

Assuming that you want to import row 3 from source spreadsheet's sheet called "source-spreadsheet-sheet-name", put in the first column of the target location the following formula:

=IMPORTRANGE("source-spreadsheet-key","source-spreadsheet-sheet-name!3:3")

where "source-spreadsheet-key" is the unque ID of the source spreadsheet that you extract from its URL.

If you do it first time, the security message will appear:

Just confirm and the whole row will be filled in automatically.

Please remember that sometimes it takes couple of minutes to update the imported cells after changes have been made in the source area. However you can speed it up by pressing ctrl-E, or ctrl-shift-E, while beeing in the target spreadsheet, whichever works.