“The coordinates or dimensions of the range are in

问题:

I'm trying to copy the first pair of name(A2) and emails(B2) from different sheets, and paste them in a Main sheet.

In the Main Sheet, I need them copied one under the other, so I thought of using getlastRow(), to get the first empty row after the first value is pasted.

At this point, I would do even if I managed to paste the values in no particular order, in the Main sheet.

When I run my script I get the following error - The coordinates or dimensions of the range are invalid

My code is below.

function myFunctions() {
 var sourceSpreadsheet = SpreadsheetApp.openById(); // source spreadsheet
 var sheets = sourceSpreadsheet.getSheets(); // array of sheets  

 var targetSpreadSheet = SpreadsheetApp.openById();
 var targetSheet = targetSpreadSheet.getSheetByName('Main'); // 

 for(var i=1; i < sheets.length;i++){  
     var temp = i;
     var tempval = sheets[i].getRange(2,1,1,2).getValues();

     var lastrow = targetSheet.getLastRow();
     var lastcol = targetSheet.getLastColumn();

    targetSheet.getRange(lastrow,1,1,lastcol).setValues(tempval[temp]);
  }

}

Please share if you have any input.

Thanks in advance for help!

回答1:

1. "ts" is undefined.

2. "tempval" is 2 dimensional array with "A2:B2". And the length is 1. So the loop of "for" with start of 1 shows out of range in Array.

3. Array for using "setValues" is 2 dimensional array. "tempval[temp]" is 1 dimensional array. So an error occurs.

4. Documents of "getRange" are follows. https://developers.google.com/apps-script/reference/spreadsheet/sheet#getRange(Integer,Integer,Integer,Integer)

5. Although about this, perhaps you may want. "lastrow" and "lastcol" are last row and column of data. So data is overwritten to last row data of "targetsheet".

Based on above, the modified script is as follows.

function myFunctions() {
  var sourceSpreadsheet = SpreadsheetApp.openById(); // source spreadsheet
  var sheets = sourceSpreadsheet.getSheets(); // array of sheets  
  var targetSpreadSheet = SpreadsheetApp.openById();
  var targetSheet = targetSpreadSheet.getSheetByName('Main'); // 
  for(var i=1; i < sheets.length;i++){  
    // var temp = i;
    var tempval = sheets[i].getRange(2,1,1,2).getValues();
    var lastrow = targetSheet.getLastRow(); // Is ts targetSheet?
    var lastcol = targetSheet.getLastColumn(); // Is ts targetSheet?
    targetSheet.getRange(lastrow + 1, lastcol + 1, 1, 2).setValues(tempval);
  }
}

Can I ask you about what you want?