Adding new Rows changes Range cells in Google Sheets, skips new row

Question

The original question was here and had an answer I thought was working: Google Sheets Conditional Formatting changes when new rows added

But after a month or so of using this, I am still running into the issue of the Conditional Formating changing the values in "Range:" when I add a new row. For example, if my formatting is for the entire B column and then I add a row in row 35, the Conditional Formatting now has B1:B34 in the range, and row 35 has no formatting.

The solution was to put this code in the Script Editor of the Google Sheet.

function formatRange(){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Sheet1");
    var range = sheet.getRange("A1");
 range.copyFormatToRange(sheet,1,sheet.getLastColumn(),1,sheet.getLastRow())
}

Again, anytime a new row is added to the Sheet, the conditional formatting range info changes and skips the new row.

If you formatted an entire column, would that work for you? Or is there formatting and/or data below the current data with different formatting? — Alan Wells
The conditional-formatting is for Excel. For Google Sheets use gs-conditional-formatting — Rubén
Unable to comment on Rubén's answer, I would like to point out that this wouldn't work if you choose to insert cell rather than rows. Inserting row would break up the column-tall range into two different ranges above and below the insertion point. — Stampeder

Alan Wells Alan Wells · Accepted Answer · 2015-03-17T21:16:49

If formatting and entire column is feasible, that can be accomplished in code:

function columnBackground() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var range = sheet.getRange("B:B");
  range.setBackground("red");
}

The above code formats the background of the entire column B in red.

Adding new Rows changes Range cells in Google Sheets, skips new row

2 Answers

Short answer

Explanation