How can I use Google Apps Script to sum a column that will vary in length?

0
votes

I am trying to add a sum formula of a column at the very bottom of that column using Google Apps scipt. The number of rows will vary, but the starting cell will remain constant.

I want to use a formula instead of inserting a static sum so that users can see the up to date sum without running any scripts.

constantslr+savingslr+needslr+wantslr = the number of rows to be summed. They are defined earlier.

I tried this with no luck

var sss = SpreadsheetApp.getActiveSpreadsheet(); var ss = sss.getSheetByName('Set Up Data'); ss.getRange(6+constantslr+savingslr+needslr+wantslr,4,1,1).setFormulaR1C1('=SUM(-['constantslr+savingslr+needslr+wantslr']C[0]:R[-1]C[0])');

2
google-apps-scriptformuladynamic-data

2 Answers

1
votes

This is not an app script formula, but if you set the region to be summed as a name range, and use:

 =SUM(YourRangeName)

it will sum the entire range.

As long as rows are inserted between the first and last row, the named range will contain all of the rows.

Example: Rows 2 - 17 are the named range SumThing

enter image description here

Inserted 5 rows in the middle: The named range expands as you insert rows. You can leave a blank row between the bottom of your data and the row containing the sum formula and then insert lines above the blank line to achieve the same thing.

enter image description here

Useful in App Script also

I use named ranges all the time within my app scripts so that I don't have to worry about somebody adding rows or columns to the sheet and breaking the script range. To do it in app script:

function useNamedRange(){
  var ss = SpreadsheetApp.getActive();
  
  // Named ranges belong to the spreadsheet, not an individual sheet
  var range = ss.getRangeByName("SumThing");

  // do something with the named range
}
0
votes

It can be done with formula SUM(). But if don't feel to use ranges and want just to get the sum of all numbers above some cell here is the custom function:

function SUMCOL() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell  = sheet.getActiveCell()
  var col   = cell.getColumn();
  var row   = cell.getRow();
  var range = sheet.getRange(1,col,row-1,1); // from row 1 and down to the cell with the formula
  var data  = range.getValues().flat().filter(Number); // skip empty cells and non-didgits
  var sum   = data.reduce((a,b) => a + b, 0);
  return sum;
}

enter image description here