Range Not Found, Google Apps Script for Google Sheets

I'm trying to build a custom range for this sort function in an Apps Script for Google Sheets but I keep getting the error: "Exception: Range not found; sortProductionLog @ macros.gs:15"

I'm sure this is something basic but I have searched far and wide and can't seem to find it. Can anyone shed some light?

Thank you, Ryan

SORT_ORDER = [
{column: 118, ascending: true},  // 3 = column number, sorting by descending order
{column: 119, ascending: true}, // 1 = column number, sort by ascending order 
{column: 117, ascending: true},
{column: 25, ascending: true}
];

function sortProductionLog(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(SHEET_NAME);
  var LastRow_WithValue = sheet.getLastRow();
  var LastColumn_WithValue = sheet.getLastColumn();
  var buildRange = "4,1," + (LastRow_WithValue - 4) + "," + (LastColumn_WithValue - 1);
  var range = sheet.getRange(buildRange);
  ss.toast(buildRange);
  range.sort
}

2 answers

  • answered 2022-05-06 22:35 NEWAZA

    The problem is you are trying to use a string "4, 1, ..." as the .getRange() argument. The function takes up to four numbers, or a single string in "A1 notation".

    Source:

    Recommended fix:

    var buildRange = [4,1, LastRow_WithValue - 4, LastColumn_WithValue - 1];
    var range = sheet.getRange(...buildRange);
    

    Additionally, here's a consolidated version:

    function sortProductionLog() {
      const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME)
      sheet.getRange(4, 1, sheet.getLastRow()-4, sheet.getLastColumn()-1)
           .sort(SORT_ORDER)
    }
    

  • answered 2022-05-07 01:10 Cooper

    Try it this way:

    function sortProductionLog() {
      const ss = SpreadsheetApp.getActive()
      var sh = ss.getSheetByName("SHEET_NAME");
      var range = sh.getRange(4, 2, sh.getLastRow() - 3, sh.getLastColumn());
      ss.toast(range.getA1Notation());
      range.sort([{ column: 118, ascending: true }, { column: 119, ascending: true }, { column: 117, ascending: true }, { column: 25, ascending: true }]);
    }
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum