Google-sheets – Use named ranges to dynamically add rows in google spreadsheets, while maintaining conditional formatting in newly added rows

conditional formattinggoogle sheetsgoogle-sheets-named-ranges

I have a table that looks like in Google Spreadsheets:

Some numbers in this table are calculated upon a range of cells (for instance, the total in the last row).

If I add a new row to this table, I naturally would like the total to consider the new row, without having to update every single formula. To achieve this, I use named ranges.

If I add a new row, I can simply open the named ranges window, change the size of range (say, from F2:F8 to F2:F9) without having to touch the formulas.

I would also like conditional formatting to behave like this but, unfortunately, it doesn't accept named ranges as parameters.

With this, the more computed columns I have, the more places I have to edit something if I add a new row:

This would be perfect but, as you can see, it is not accepted as valid:

How can I do this? How can I add new rows without having to update all my conditional formatting and formulas?

Best Answer

At this time it's not possible to use named ranges to the "Apply to range" setting of Conditional Formatting.

Code

// global 
var ss = SpreadsheetApp.getActive();

function onOpen() {
  var menu = [{name:"Add New Last Row", functionName:"addRow"}];
  ss.addMenu("Extra", menu);
}

function addRow() {
  var sh = ss.getActiveSheet(), lRow = sh.getLastRow(); 
  var lCol = sh.getLastColumn(), range = sh.getRange(lRow,1,1,lCol);
  sh.insertRowsAfter(lRow, 1);
  range.copyTo(sh.getRange(lRow+1, 1, 1, lCol), {contentsOnly:false});
}

Remark

Setting the contentOnly to false will yield a standard copy. Setting it to true, will paste only values. The example script you found, does way more then pasting values.....

Example

I've created an example file for you: Add Row With Formula's

Google-sheets – Return name of range as string in Google Sheets

That's currently not possible in Google Apps Script. See this enhancement request in the Google Apps Script issue tracker, to make that possible:

issue 4245

As mentioned in the issue itself:

To subsequent readers: If you are also interested in this requested feature, please click the star next to the issue number.

Best Answer

Related Solutions

Google-sheets – How to automatically insert a new row and retain functions/formulas from last row

Code

Remark

Example

Google-sheets – Return name of range as string in Google Sheets

Related Topic