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

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.

Answers:

Answer

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

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

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:

enter image description here

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

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)

Answer

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

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

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

}  

Tags

Recent Questions

Top Questions

Home Tags Terms of Service Privacy Policy DMCA Contact Us

©2020 All rights reserved.