CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user1650517
user1650517

Reputation: 75

Copy a range from one spreadsheet to another

I am attempting to copy the contents of an array from one sheet (where the array is created by iterating through and pushing select items for the column of the user's choosing) to a different sheet in a different spreadsheet.

I have come across a number of questions and answers on how to import a range from one spreadsheet to another, but none of them has worked for me (all returning the error "We're sorry, a server error occurred. Please wait a bit and try again.") I've tried 'copyTo' as well, with no success.

Here is the code I'm using currently. Where have I gone wrong??

function copyToTrix(featureList) {

  featureList = featureList.getValues();

  var tss = SpreadsheetApp.openById("0AtX9IYFZ..."); //define active trix as target 

var ts = tss.getSheetByName("US");          //define sheet in target trix

var newRange = ts.getRange("DA12:DA25");    //define target range

 newRange.setValues(featureList);  //set values in target range to items in featureList

Browser.msgBox('copied');
}

Upvotes: 6

Views: 18582

Answers (6)

Mehdi Chaouch
Mehdi Chaouch

Reputation: 455

Very quick and short :

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceData = ss.getRange('Sheet1' + '!A2:M2').getValues();
var targetSheetNextRow = ss.getSheetByName('Sheet2').getLastRow() + 1;
ss.getRange('Sheet2' + '!A' + targetSheetNextRow+':M' + targetSheetNextRow).setValues(sourceData);

Upvotes: 1

Zohar Karabelnik
Zohar Karabelnik

Reputation: 31

I use this function to copy an entire sheet's (values only) between documents:

 function updateSourceToTarget(sourceID,sourceName,targetID,targetname){
  Logger.log(sourceID + ' ' + sourceName + ' ' +targetname);
var source = SpreadsheetApp.openById(sourceID).getSheetByName(sourceName);
var destination = SpreadsheetApp.openById(targetID).getSheetByName(targetname);
var sourcelastRow = source.getLastRow();
var sourcelastCol = source.getLastColumn();
var sourcedata = source.getRange(1,1,sourcelastRow,sourcelastCol).getValues();
destination.getRange(1,1,sourcelastRow,sourcelastCol).setValues(sourcedata);
}

then I call this function, in example:

updateSourceToTarget('sourceID','sourceSheetName','targetID','targetSheetName');

getting a replica of the data in the original sheet in a different document. very useful for small documents that You have to keep the original untouched and unshared, if formatting and functions is not important.

another direction is the function sheet.copyto() that creates a new copy of the sheet, but then You require some various garbage disposal functions. Good luck!

Upvotes: 1

Matias
Matias

Reputation: 11

I am using somepart of someone here, I this code is working for me.

function CopyRange() {
 var sss = SpreadsheetApp.openById('spreadsheetid'); //replace with source ID
 var ss = sss.getSheetByName('sheetname'); //replace with source Sheet tab name
 var range = ss.getRange('A2:G50'); //assign the range you want to copy
 var data = range.getValues();

 var tss = SpreadsheetApp.openById('SpreadsheetID'); //replace with destination ID
 var ts = tss.getSheetByName('sheetname2'); //replace with destination Sheet tab name

 ts.getRange(ts.getLastRow()+1, 1,49,7).setValues(data); //you will need to define the size of the copied data see getRange()

}


This code, is getting the range A2:G50 and copied to sheetname2 in the lastrow available. For example if sheet2 have the first row availble un row11, this code will paste the information in Row11.

Upvotes: 1

Serge insas
Serge insas

Reputation: 46794

You defined your target range in a way that is quite dangerous as it relies only on your decision and not on the array content... You could use this method that works all the time :

var beginning_row = 12;
var beginning_col = /*what ever corresponds to 'DA' */ ;
var newRange = ts.getRange(beginning_row, beginning_col, featureList.length, featureList[0].length);    //define target range

it takes the number of rows from the array length (which is actually what it is) and the number of columns from the length of its first element (same comment)... quite straightforward and reliable ;-)

Also : the answer you gave to yourself is wrong (sorry about that, no personal offense...) as it leads to the exact contrary of what is described in the 'best practice' chapter of the doc and I'd recommend to remove its 'answered mark' and upvote.

Upvotes: 4

user1650517
user1650517

Reputation: 75

It turns out the GAS is having trouble with the setValues() function. It wasn't working to take a full range, so I ended up having to use setValue() instead. (Set value only allows you to operate in one cell at a time):

var tss = SpreadsheetApp.openById("0AtX9IYFZ9KkbdHg4TUdqYVppTTU5OENpb04tWTdNbHc");
var ts = tss.getSheetByName("US");
for (var i = 0; i < featureList.length; i++) {
var position = i + 12;
ts.setActiveCell("B" + position).setValue(featureList[i]);

Upvotes: -1

eddyparkinson
eddyparkinson

Reputation: 3700

Both copyTo, and setValues work - I use them in my code.

I would locate the line that produces the error - logs, or a try and catch.

or add something like this - to see if it gives you some clues

// The code below will set the values for range A1:D2 to the values in an array.
var myTable = [[1, 2, 3, 4],[5, 6, 7, 8]];
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(1,1,2,4).setValues(myTable);

Upvotes: 0

Related Questions