Ensure column range expands to include newly inserted rows

Question

I have a spreadsheet with 3 charts placed on it, vertically. Each has a header (Row 1, the header for Chart 1, is frozen). Chart 1 data is currently within the range A2 through P26. Data from Chart 1 is summed and referenced in the bottom row of Chart 1 (A27 through P27), as well as in cells of Chart 2 and Chart 3.

I have been working on a script that, once assigned to a button, will insert a new Row 2 at the top of the sheet (beneath Row 1, since header Row 1 is frozen). The newly inserted row maintains conditional formatting from the below Chart 1 rows, as well as 3 formulas that are needed, but its data is not included within any of the summations elsewhere on the sheet.

Is there a way to make sure that the formulas referencing A2 through P26 always include the newly inserted Row 2 each time a new Row 2 is inserted?

Here is my code:

function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menu = [{name: 'Insert Row', functionName: 'insertRow'}];
ss.addMenu('Insert', menu);  
}

function insertRow() {
var spsh = SpreadsheetApp.getActiveSpreadsheet();

var sheet = spsh.getSheetByName('testSheet');
sheet.activate();

sheet.setFrozenRows(1);

sheet.insertRows(2);

var range = sheet.getRange('G2');
range.setFormula('=sum(E2:F2)');

var range = sheet.getRange("O2");
range.setFormula('=iF(K2="ordered", G2, "$0.00")');

var range = sheet.getRange("P2");
range.setFormula('=iF(K2="lost", G2, "$0.00")');
}

Unknown Unknown · Accepted Answer · 2016-04-01T22:45:36

A cell reference that does not change with row insertion/deletion can be created with indirect(string):

=sum(indirect("A2"):A26)

This means the range will always begin with the cell A2, no matter what insertions happen.

Another approach

For completeness, I'll point out another way to deal with this: include row 1 in the range, so that insertion happens within the range. Depending on what the formulas are, they may already ignore the text content of the header row. If they don't, you can explicitly exclude the first row like this:

=sum(filter(A1:A26, row(A2:A26)>1))

Ensure column range expands to include newly inserted rows

1 Answers

Another approach