Check number of rows in named range and if more than 2, execute different script

Viewed 38

Currently I have a script that will add a specified number of rows into a named range. Due to the fact that the named range will always have 1 row to start, adding in "3" rows will really only add 2. This is the expected outcome for the first time you run the script. Obviously if the script has been run already, and you are expecting to add 3 more rows, having it only add 2 is frustrating. I cannot seem to wrap my head around how this works.

Here is the functional code:

//Take the Account and number of holes from
// the sidebar and insert rows to the proper named range    
function insertRowNext(account,n_rows) {  
        var ss = SpreadsheetApp.getActiveSpreadsheet(); 
        var ws = ss.getSheetByName('Next Week');  // Change to your sheet name
        
        //Replace space with underscore
        account=account.replace(/ /g,"_");
        var nameRange = 'Next Week!'+account;
        n_rows-=1;
        
        if(n_rows>0){
          //add row
          var range = ss.getRangeByName(nameRange);
          
          ws.insertRowsBefore(range.getLastRow(),n_rows);
          //Show all rows in the namedRange
          ws.showRows(range.getRow(),range.getNumRows()+n_rows);
          
        }
     }

Here is the code that I modified but it broke the script.

// Take the Account and number of holes from
// the sidebar and insert rows to the proper named range
function insertRowNext(account,n_rows) {
    var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    var ws = ss.getSheetByName('Next Week'); 
    //Replace space with underscore
    account=account.replace(/ /g,"_");  
    if (n_rows == 2){
      n_rows -= 1
      var range = ss.getRangeByName(nameRange);
      ws.insertRowsBefore(range.getLastRow(),n_rows);
      //Show all rows in the namedRange
      ws.showRows(range.getRow(),range.getNumRows()+n_rows);
    }else if (n_rows < 2){
      n_rows = 0
      var range = ss.getRangeByName(nameRange);
      ws.insertRowsBefore(range.getLastRow(),n_rows);
      //Show all rows in the namedRange
      ws.showRows(range.getRow(),range.getNumRows()+n_rows);
    }else{
      alert("unexpected number of rows -- please check template");
      //throw error        
    }
}

I just need the script to check how many rows are in the selected named range, and if it is 2, subtract one from the amount to add. If the amount of rows in the named range is >2, do not subtract the amount of holes.

1 Answers

I don't know how your sheet is organized to produce such kind of behavior when inserting rows, but if you really want to check, you can use this code block:

  var range = ss.getRangeByName(nameRange);
  var rangeRows = range.getNumRows();

  if (rangeRows == 2) {
    ws.insertRowsBefore(range.getRow(),n_rows-1);
  }
  else if (rangeRows > 2) {
    ws.insertRowsBefore(range.getRow(),n_rows);
  }

This should be the output if n_rows = 3:

enter image description here

Related