google apps script - Collect Data from several Spreadsheets and copy it into another sheet -

i want copy data 1 spreadsheet one. 1. data on several sheets 2. columns (just speed up, don't need columns)

for beginning used 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 range   var a1range = srange.geta1notation();   //get 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 target range values of source data   ts.getrange(a1range).setvalues(sdata);  }   

i took 1 user2741, posted here:

..this 1 works fine 1 sheet. unsure how data more sheets , sort it. has idea on how this?

thank all, sascha

this 1 here works, if happens in 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 iterations: coordinates or dimensions of range invalid. (line 11, file "copy-m.js") -> might empty spreadsheet   var destsheet = ss.getsheetbyname(targetsheetname);   var destrange = destsheet.getrange(destsheet.getlastrow()+1,1);   source.copyto(destrange, {contentsonly: true});  } 

but reason don't run when using different spreadsheets error: "target range , source range must on same spreadsheet"

i tried 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 iterations: coordinates or dimensions of range invalid. (line 11, file "copy-m.js") -> might empty spreadsheet   var destsheet = ssd.getsheetbyname(targetsheetname);   var destrange = destsheet.getrange(destsheet.getlastrow()+1,1);   source.copyto(destrange, {contentsonly: true}); 

any ideas?

the solution simpler tried, since want copy values , knowing copyto works in same spreadsheet, values source , paste in destination 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 more flexible i'd recommend use 4 parameters , simplify again 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); }