Search code examples
google-apps-scriptformuladynamic-data

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

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])');
Solution

  • 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
}