Google-sheets – Automatically add rows based on condition of existing rows

google sheets

I would like to have a formula on a Google Sheets spreadsheet that checks the content of the existing top Nth rows, and adds extra rows based on the content of these.

I read this question:
How to automatically insert a new row and retain functions/formulas from last row?

But it seems like the trigger here is when the spreadsheet is opened (onOpen() function?), rather than a condition on existing rows.

Here is an example

an example

The spreadsheet has a header, and similarly to the question posted above, I would like to add a new row by checking on the content of the existing ones.

For example, given the fact that the users are going to be filling in the columns from bottom upwards, I would like a trigger that adds a new row 2 with Entry452 / NA / NA / NA, keeping the same formulas, etc. as Entry451, and it does so when we are running out of empty rows. E.g., when there are less than five rows left with NAs in column machine_type.

Best Answer

Something like this perhaps?

function conditionalNewRow() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getActiveSheet();
  var headerRow = 1;
  var firstRow = headerRow + 1;
  var range = sh.getRange("A2:D2"); // Get range of row 2.
  var futureRange = sh.getRange("A3:D3"); // Get range of row 3.
  var numCols = range.getNumColumns(); // Get the number of columns for row 2.
  var contentVal = false;

  for (i = 1; i <= numCols; i++) {
    var currentValue = range.getCell(1,i).getValue();
    if (currentValue == "NA"){
      contentVal = true;
      break;
    }
  }  
  if (contentVal == true) {
    sh.insertRowBefore(firstRow); // Insert an empty row into row 2.       
    futureRange.copyTo(sh.getRange(firstRow, 1, firstRow, numCols), {contentsOnly:false}); // Copy row 3 to row 2.
  }
}
Related Topic