google-apps-script

Apps Scripts - .getLastColumn() works once in a for loop then errors out


I am trying to loop through Sheets and getLastColumn(). I activate the first sheet and get the last column, then I activate the next sheet, but this time I get an error on getLastColumn(). I am doing this in a for loop, could that be the issue?

    //sort fab, milling, turning, engraving
    sheet = SpreadsheetApp.getActive().getSheetByName("Fab Schedule");
    sheet.activate;
  
    var lastColumnFab = sheet.getLastColumn();
    var lastColumn = lastColumnFab;
    var lastColumnMilling;
    var lastColumnTurning;
    var lastColumnEngraving;
    var currentSheet = SpreadsheetApp.getActiveSheet().getSheetName(); 
    Logger.log(currentSheet);

    for(a=0;a<3;a++){ // 'a' is for looping through the different sheets
    Logger.log(a);

    // counting non-empty rows 
    var BValues = sheet.getRange("B3:B").getValues();
    var i=0;
    var singleBValue = "";
    //Logger.log(BValues[i]);
    for(i = 0; BValues[i] != ""; i++){
      singleBValue = BValues[i];
      //console.log({i,singleBValue});
    }
    var lastRow = i;
    i=i+3;

    // arrays begin with 0 // all rows and columns begin with 1
    sheet.getRange(3,2,lastRow,lastColumn).activate();
    //sheet.getRange('B3:V').activate(); //number of columns is different on every
    sheet
    var statusColumn = getHeaderNumber("Status")+1; //get header uses array, so it
    starts at 0
    var dueDateColumn = getHeaderNumber("Due Date")+1;
    var engineerColumn = getHeaderNumber("Engineer")+1;
  
    //sort by incomplete->complete, then
    sheet.getActiveRange().sort([{column: statusColumn, ascending: false}, {column: dueDateColumn, ascending: true},{column:engineerColumn, ascending: true}]);
  //sheet.getActiveRange().sort([{column: 14, ascending: false}]);//, {column: 18, ascending: true},{column:2, ascending: true}]);

  sheet.getRange(3,14,lastRow,lastColumn-14).setHorizontalAlignment("center"); //number of columns is different on every sheet
  sheet.getRange(3,1,lastRow,lastColumn).setVerticalAlignment("middle");
  sheet.getRange(3,1,lastRow,lastColumn).setFontFamily("Arial");
  sheet.getRange(3,1,lastRow,lastColumn).setFontSize(10);
  if (a==0){
      sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Milling Schedule");
      sheet.activate();
      var currentSheet = SpreadsheetApp.getActiveSheet().getSheetName(); 
      Logger.log(currentSheet);
      lastColumnMilling = sheet.getLastColumn(); //error?
      lastColumn = lastColumnMilling;
    }

Exception: The coordinates of the range are outside the dimensions of the sheet. Sortbyincompleteanddate @ sort.gs:62


Solution

  • The code is not working correctly because the for (a loop iterates three times but the code only attempts to handle two sheets. Use a more structured approach, perhaps like this:

    function test() {
      ['Fab Schedule', 'Milling Schedule', 'Turning Schedule', 'Engraving Schedule',]
        .forEach(sheetName => {
          const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
          if (!sheet) {
            console.log(`There is no sheet by the name ${sheetName}`);
            return;
          }
          sortAndFormat_(sheet);
        });
    }
    
    function sortAndFormat_(sheet) {
      const lastRow = sheet.getLastRow();
      const lastColumn = sheet.getLastColumn();
      sheet.getRange(3, 2, lastRow, lastColumn)
        .sort([
          { column: getHeaderNumber(sheet, 'Status') + 1, ascending: false },
          { column: getHeaderNumber(sheet, 'Due Date') + 1, ascending: true },
          { column: getHeaderNumber(sheet, 'Engineer') + 1, ascending: true },
        ]);
      sheet.getRange(3, 14, lastRow, lastColumn - 14).setHorizontalAlignment('center');
      sheet.getRange(3, 1, lastRow, lastColumn)
        .setVerticalAlignment('middle')
        .setFontFamily('Arial')
        .setFontSize(10);
    }
    

    Note that I've added a sheet parameter to the getHeaderNumber() call. You will need to adjust that function to accept and use the sheet parameter instead of locating the sheet with Spreadsheet.getActiveSheet() or similar means.

    Use a more structured approach to clean up your code and make it easier to track and maintain. See Clean Code JavaScript and take a look at methods such as Array.forEach().