How to copy a row from one google spreadsheet to a

Question

Closed. This question needs to be more focused. It is not currently accepting answers.

---

Want to improve this question? Update the question so it focuses on one problem only by editing this post.

Closed 2 years ago.

Hello 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

Use range.setValues, is the best choice. Works for any range in any sheet, as far as the origin and destination range are similar.

Here's a sample code:

function openCSV() {

  //Buscamos el archivo
  var files = DriveApp.getFilesByName("ts.csv");

  //Cada nombre de archivo es unico, por eso next() lo devuelve.
  var file = files.next();

  fileID = file.getId();

  //Abrimos la spreadsheet, seleccionamos la hoja
  var spreadsheet = SpreadsheetApp.openById(fileID);
  var sheet = spreadsheet.getSheets()[0];

  //Seleccionamos el rango  
  var range = sheet.getRange("A1:F1");
  values = range.getValues();

  //Guardamos un log del rango
  //Logger.log(values);

  //Seleccionamos la hoja de destino, que es la activeSheet 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var SSsheet = ss.getSheets()[0];

  //Seleccionamos el mismo rango y le asignamos los valores
  var ssRange = SSsheet.getRange("A1:F1");
  ssRange.setValues(values);

}

Answer 2

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 3

Maybe this post helps you http://l.rw.rw/gscripts3. It describes functionality of copy function with different conditions (limit data by range, custom filters, place data to target with insert or replace methods). And has working example where you can try it.

Answer 4

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 5

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.

Answer 6

This does not work - returns an error "Target range and source range must be on the same spreadsheet." Looks like we need to do something similar to this:

function copyRow_ (origSheetName, destSheetName, origRowNumber, destRowNumber)
{
  var origSheetObject = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(origSheetName);
  var destSheetObject = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(destSheetName);
  var origLastCol = origSheetObject.getLastColumn();
  var arrOrigData = origSheetObject.getRange(origRowNumber, 1, 1, origLastCol).getValues();
  var destlastRow = destSheetObject.getLastRow() + 1;
  if (destRowNumber)
    destlastRow = destRowNumber;
  var cont;
  for (cont = 0; cont < origLastCol; cont++)
       destSheetObject.getRange(destlastRow, 1+cont).setValue(arrOrigData[0][cont]);
}

(blatantly stolen from here)