CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Julie Stenger
Julie Stenger

Reputation: 1

Google Sheets Error Msg: Exception: The coordinates of the range are outside the dimensions of the sheet

Google Apps Script is throwing me an error on a script that had been working fine for a couple of weeks.

It is throwing the error on line 6 of the code:

Exception: The coordinates of the range are outside the dimensions of the sheet. (line 6, file "TL")

which is a line of code to the the last row in a range.

Any input on why this is happening and how to remedy it?

function submitTLData() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("Truckload Invoice FORM"); //Data entry Sheet
  var datasheet = ss.getSheetByName("Truckload Invoice DATA"); //Data Sheet 

datasheet.getRange(datasheet.getRange("N5").getDataRegion().getLastRow()+1,18,1,1).insertCheckboxes();

  //Input Values
  var values = [[formSS.getRange("L6").getValue(),  //Timestamp
                 formSS.getRange("L7").getValue(),  // Type
                 formSS.getRange("G5").getValue()]];  //Location

  datasheet.getRange(datasheet.getRange("N5").getDataRegion().getLastRow(), 14, 1, 3).setValues(values);

//Input Values
  var values = [[formSS.getRange("L5").getValue(),  //Checkbox Value
                 formSS.getRange("G6").getValue(),  //TL Description
                 formSS.getRange("G7").getValue(),  //TL Qty 
                 formSS.getRange("G8").getValue(),  // TL Value  
                 formSS.getRange("L10").getValue(),  // TL Fee Amt             
                 formSS.getRange("L12").getValue(), //OTHER Description
                 formSS.getRange("L13").getValue(),  //OTHER Qty            
                 formSS.getRange("L14").getValue(), //OTHER Value                
                 formSS.getRange("L16").getValue(),  //OTHER Fee Amt           
                 formSS.getRange("L17").getValue(), // Freight               
                 formSS.getRange("L18").getValue(),  //Shipping          
                 formSS.getRange("L19").getValue(), // Deposit              
                 formSS.getRange("L23").getValue(), //Cr to Loan Amt  
                 formSS.getRange("L26").getValue()]]; //OTHER Cr Amt

  datasheet.getRange(datasheet.getRange("N5").getDataRegion().getLastRow(), 18, 1, 14).setValues(values); 

  //Input Values
  var values = [[formSS.getRange("L25").getValue(), //OTHER Cr Description
                 formSS.getRange("L20").getValue()]]; //TL Notes 

  datasheet.getRange(datasheet.getRange("N5").getDataRegion().getLastRow(), 33, 1, 2).setValues(values);  

var rangesToClear = ['G5:G27',"M7","M13"];
  for (var i=0; i<rangesToClear.length; i++) { 
    formSS.getRange(rangesToClear[i]).clearContent();
    }

  var noValues = ['G11', "G21:G23","G25"];
  for (var i=0; i<noValues.length; i++) {
    formSS.getRange(noValues[i]).setValue("No");  //Reset Form Cells to No
    }

  var yesValues = ['G9',"G15"];
  for (var i=0; i<yesValues.length; i++) {
    formSS.getRange(yesValues[i]).setValue("Yes");  //Reset Form Cells to No
    }

  datasheet.getRange('N5').activate();
  datasheet.getFilter().sort(14, false);

    var getInvNum = datasheet.getRange("Q6").getValue();
    datasheet.getRange("G6").setValue(getInvNum);  

  Browser.msgBox("New Truckload Invoice \\nHas Been Successfully Added");  

}

Upvotes: 0

Views: 167

Answers (1)

Cooper
Cooper

Reputation: 64062

If you data goes all the way down to the end of rows then try something like this:

var mr=datasheet.getMaxRows();
var nr=datasheet.getLastRow()+1;
if(n>mr) {
  datasheet.insertRowsAfter(mr,1)
}
datasheet.getRange(datasheet.getRange("N5").getDataRegion().getLastRow()+1,18,1,1).insertCheckboxes();

This should help you to avoid these errors.

Upvotes: 1

Related Questions