Using a variable in a formula to refer to the last row in Office Scripts

Question

Using Office Scripts (in Excel), I'd like to apply a formula to a new column in which the formula contains the function for finding the maximum value of an adjacent column. The number of data points collected varies from experiment to experiment, so I don't want to set finite bounds for finding the maximum of the adjacent column (i.e. MAX(G2:G10)).

I'd like to ideally create a variable for the number of rows so that I could then calculate the maximum of the column, despite the changing number of data points collected across experiments, such as MAX(G2:G(numrows)).

I've tried applying something along the lines of:

 let rowCount = range.getRowCount();
 const numrows = rowCount

 selectedSheet.getRange("H1:H2").setFormulasLocal([["Air Flow (%)"], ["=(G2/MAX($G$2:$G(numrows)))*100"]]);

I know that this syntax isn't correct, as it just results in an error (#NAME?), and was wondering if there were any suggestions on which variable types could make this work (or other approaches all together).

Thanks in advance!

Answer 1

You can try the code below:

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  let lastRow = selectedSheet.getRange("G1048576").getRangeEdge(ExcelScript.KeyboardDirection.up).getRowIndex() + 1;
  selectedSheet.getRange("H1:H2").setFormulas([["Air Flow (%)"], [`=(G2/MAX($G$2:$G${lastRow}))*100`]]);
}

I determine the lastRow by going to the last cell in column G and then doing the programming equivalent of hitting the ctrl key and pressing the up arrow on my keyboard. From here, I use getRowIndex() to get the row index and add one to it. I need to do that since row indexes start at zero instead of at one like row numbers.

Once I have the lastRow, I can update the string to use the lastRow variable. From there, you can use setFormulas() to write the value / formula.