Set formula in Google Apps Script on conditional range-CodePudding

I would like to set a formula in Google Sheets using Google Apps Script so that users may select a title column (=A1= "*") which will sum all columns below until the next title row.

Here is a link to a screenshot of my google sheet.

My code works as long as the range is static however as you can see for title 3, Column S does not take into account the 7th line. And the number of my rows may vary until a next title row (marked by a * in Column A).

Is there any way for row number in setFormulaR1C1 to be conditional?

function updatewithformula() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  
  // This represents ALL the data
  var range = sheet.getDataRange()
  var values = range.getValues()

  var sheet1 = ss.getSheetByName('sheet1')
  var source = sheet1.getDataRange().getDisplayValues()

  for (var i = 1; i<source.length; i  ){


    if (source[i][0] == "*"){

      var rangetomodify = sheet1.getRange(i 1,13,1,20)
      rangetomodify.setFormulaR1C1("=SUM(R[1]C[0]:R[4]C[0])")

      //rangetomodify.setFormula("=SUM(P7:P10)")
    }
  }
  
  }

Any help would be greatly appreciated! Many thanks and good day

CodePudding user response：

Try with this:

function updatewithformula() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  
  // This represents ALL the data
  var range = sheet.getDataRange()
  var sheet1 = ss.getSheetByName('sheet1')
  var source = sheet1.getDataRange().getDisplayValues()
  var index = [] //since you need to calculate how many rows are there in between the titles, I created a first for loop to gather all those row numbers
  for (var i = 1; i<source.length; i  ){
    if (source[i][0] == "*"){
      index.push(i 1) //this collects all the rows that has * in them
        }
  }
  index.push(range.getLastRow() 1) //adds the final row with data for the last formula
  Logger.log(index)
  for(var i = 0;i<index.length-1;i  ){ //this for loop navigates through  the rows and creates the formulas. "i<index.length-1" skips the last number so you don't have an empty formula at the last
       var rangetomodify = sheet1.getRange(index[i],13,1,20)
       var l = index[i 1]-index[i]-1 //calculates the length of the formula with the next title row. It substracts "the next" title from "this" title, and minus one so the formula doesn't include the title row itself
      rangetomodify.setFormulaR1C1("=SUM(R[1]C[0]:R[" l "]C[0])")
  }

}