"The coordinates of the target range are outside the dimensions of the sheet" error when looping

Question

I have 4 sheets. Form, Raw, URL and USA/Japan/Canada. I get the spreadsheet url value from the URL sheet and use 'IMPORTRANGE' in the Raw sheet to transfer the data table. From there, i loop through the table in Raw sheet to check if the values is match from the values in the Form sheet. If it's matched, i will transfer that row in the USA/Japan/Canada sheet.

So i got this code in my google sheets:

  var mainWS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form');
  var continentWS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('USA/Japan/Canada');
  var rawWS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Raw');
  var urlWS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('URLs');

  var tourName = mainWS.getRange('C4').getValue();
  var depDate = new Date(mainWS.getRange('C5').getValue());
  var status = mainWS.getRange('C6').getValue();

  //extract table data
  var url =  urlWS.getRange("B4").getValue();
  rawWS.getRange('A1').setValue('=IMPORTRANGE("' + url + '","Main Data!A1:AE")');

  var dummyHolder = rawWS.getRange("B1:B").getValues();
  var lastRow = dummyHolder.filter(String).length;

  Logger.log(lastRow);

  //loop through the raw extract
  for(var i = 2; i <= lastRow; i++){

    var dateHolder = new Date(rawWS.getRange("E" + i).getValue());

    if(rawWS.getRange("F" + i).getValue() == tourName && rawWS.getRange("I" + i).getValue() == status && dateHolder.getTime() === depDate.getTime()){
      var continentLR = continentWS.getLastRow() + 1; 
      rawWS.getRange('Raw!' + i + ':' + i + '').copyTo(continentWS.getRange(continentLR, 1), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
    }

  }

Then i suddenly get the "The coordinates of the target range are outside the dimensions of the sheet" error message once it enters the loop. Specifically in the first line after the for loop:

 var dateHolder = new Date(rawWS.getRange("E" + i).getValue());

To be clear, the rawWS where it is looping has 134 records and it registers 134 as its last row. I don't know why i am getting an error here.

When i remove that line, it still gives me an error on the next line. It all errors inside the for loop.

Any ideas?

You really need to read [this](https://developers.google.com/apps-script/guides/support/best-practices#use_batch_operations) You might wanna rewrite the whole thing. — TheMaster, Dec 29 '18 at 11:56
I used Logger.log and got the value. Since it's a date, i got "Sun Apr 07 12:00:00 GMT-04:00 2019". I tried to comment out the whole IF block inside the loop and suddenly the var dateHolder line of code runs without any error. But once i include the IF block, the error resumes. — Vince, Dec 29 '18 at 11:59
It's weird though, there are 3 similar functions like these that i use. I just replaced the ranges per function and they works perfectly fine except this one — Vince, Dec 29 '18 at 12:00
What is the value of `i` when the error occurred? How many rows has the destination sheet? — Rubén, Dec 29 '18 at 19:29

score 1 · Answer 1 · answered Dec 29 '18 at 19:34

I haven't tested this but give it a try:

I think that the problem with filter is that it will remove blanks before the end as well but getValues() won't. So you can end up with the wrong length depending upon the dataset.

function unknown() {
  var formWS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form');
  var continentWS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('USA/Japan/Canada');
  var rawWS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Raw');
  var urlWS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('URLs');
  var tourName = formWS.getRange('C4').getValue();
  var depDate = new Date(formWS.getRange('C5').getValue());
  var status = formWS.getRange('C6').getValue();
  //extract table data
  var url =  urlWS.getRange("B4").getValue();
  rawWS.getRange('A1').setValue('=IMPORTRANGE("' + url + '","Main Data!A1:AE")');
  var dummyHolder = rawWS.getRange(1,2,rawWS.getLastRow(),1).getValues();

  for(var i=dummyHolder.length-1;i>=0;i--){
    if(!dummyHolder[i][0]){
      dummyHolder.splice(i,1);
    }else{
      break;
    }
  }
  //loop through the raw extract
  for(var i=2;i<dummyHolder.length; i++){
    var dateHolder = new Date(rawWS.getRange(i,5).getValue());
    if(rawWS.getRange(i,6).getValue() == tourName && rawWS.getRange(i,9).getValue() == status && dateHolder.getTime() === depDate.getTime()){
      var continentLR = continentWS.getLastRow() + 1; 
      rawWS.getRange(i,1,).copyTo(continentWS.getRange(continentLR, 1), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
    }

  }
}

Array Filter Method

Also your last line included sheet name in the range but rawWS is that sheet. So I rewrote the range parameters. Sorry if this doesn't work.

"The coordinates of the target range are outside the dimensions of the sheet" error when looping

1 Answers1