Collect Data from several Spreadsheets and copy it into another sheet

Question

I want to copy data from one spreadsheet into another one. But 1. The Data are on several sheets 2. Only certain Columns (just to speed it up, don't need all columns)

For the beginning I used the following script:

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

}

I took this one from user2741, which was posted here:

..this one works fine for one sheet. But now I am unsure how to get the data from more sheets and actually sort it. Does anybody has a good idea on how to do this?

Thank you all, Sascha

This one here works, if everything happens in the same spreadsheet

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SummarySheet").clear();
mergeSheet2("SummarySheet", "123");
mergeSheet2("SummarySheet", "345");
mergeSheet2("SummarySheet", "748");
mergeSheet2("SummarySheet", "293");

function mergeSheet2(targetSheetName, sourceSheetName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName(sourceSheetName);
  var lastRow = sourceSheet.getLastRow();
  var lastCol = sourceSheet.getLastColumn();
  var source = sourceSheet.getRange(1,1,lastRow,lastCol); // Error after some iterations: The coordinates or dimensions of the range are invalid. (line 11, file "copy-m.js") -> might be empty spreadsheet
  var destSheet = ss.getSheetByName(targetSheetName);
  var destRange = destSheet.getRange(destSheet.getLastRow()+1,1);
  source.copyTo(destRange, {contentsOnly: true});

}

but for some reason I don't get it run when using different spreadsheets Error: "Target range and source range must be on the same spreadsheet"

I tried it with:

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SummarySheet").clear();
SpreadsheetApp.openById('ID');
mergeSheet2("SummarySheet", "123");


function mergeSheet2(targetSheetName, sourceSheetName) {
  var ss = SpreadsheetApp.openById('ID');
  var ssd = SpreadsheetApp.openById('ID');
  var sourceSheet = ss.getSheetByName(sourceSheetName);
  var lastRow = sourceSheet.getLastRow();
  var lastCol = sourceSheet.getLastColumn();
  var source = sourceSheet.getRange(1,1,lastRow,lastCol); // Error after some iterations: The coordinates or dimensions of the range are invalid. (line 11, file "copy-m.js") -> might be empty spreadsheet
  var destSheet = ssd.getSheetByName(targetSheetName);
  var destRange = destSheet.getRange(destSheet.getLastRow()+1,1);
  source.copyTo(destRange, {contentsOnly: true});

Any ideas?

Serge insas Serge insas · Accepted Answer · 2014-02-08T13:39:34

The solution is simpler than what you tried, since you want to copy values only and knowing that copyTo only works in the same spreadsheet, just get the values from source and paste it in destination like this:

function copyFromSourceToTarget(targetSheetName, sourceSheetName) {
  var ss = SpreadsheetApp.openById('ID');
  var ssd = SpreadsheetApp.openById('ID');
  var sourceSheet = ss.getSheetByName(sourceSheetName);
  var sourceData = sourceSheet.getDataRange().getValues();
  var destSheet = ssd.getSheetByName(targetSheetName);
  destSheet.getRange(destSheet.getLastRow()+1,1,sourceData.length,sourceData[0].length).setValues(sourceData);
}

To make it more flexible I'd recommend to use 4 parameters and simplify again the code :

function copyFromSourceToTarget(targetSheetName,targetSsId, sourceSheetName,sourceSsId) {
  var ss = SpreadsheetApp.openById(sourceSsId)getSheetByName(sourceSheetName);
  var ssd = SpreadsheetApp.openById(targetSsId).getSheetByName(targetSheetName);
  var sourceData = ss.getDataRange().getValues();
  ssd.getRange(ssd.getLastRow()+1,1,sourceData.length,sourceData[0].length).setValues(sourceData);
}

Collect Data from several Spreadsheets and copy it into another sheet

1 Answers