Excel Online Script to add row with formula

[chrisparkinsonarthouse]

Hi All,

Ive got a workbook saved to a SharePoint folder.

In the workbook is a script.  See below.  Its the part in bold i need help with.

Basically the script needs to add a header to the sheet and a footer at the bottom of the set of data (row number can change daily).

The script adding the header works fine.  I can't get the script to add the footer to the bottom of the list.

Footer needs to be 

99, rownumber (which changes)

function main(workbook: ExcelScript.Workbook) {

  let selectedSheet = workbook.getActiveWorksheet();

  // Insert at range 1:1 on selectedSheet, move existing cells down

  selectedSheet.getRange("1:1").insert(ExcelScript.InsertShiftDirection.down);

  // Set range A1:B1 on selectedSheet

  selectedSheet.getRange("A1:B1").setValues([["0","83854"]]);

  // Insert last row number

  let lastrow = selectedSheet.getUsedRange();

  selectedSheet.getRange(lastrow).setValues([["99", "83854"]]);

  // Paste to range A:C on selectedSheet from range A:C on selectedSheet

  selectedSheet.getRange("A:C").copyFrom(selectedSheet.getRange("A:C"), ExcelScript.RangeCopyType.values, false, false);

}

[#2]

Not sure I understand. How would you manually determine where to put that number 99?

[#3]

@Jan Karel Pieterse Hi,

Its keyed in column A, in the last row.

But the last row changes every day.

Chris

[#4]

@chrisparkinsonarthouse Is this what you intended?

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	let theRow = selectedSheet.getUsedRange().getRowCount();
	selectedSheet.getCell(theRow,0).setValue(99);
}

[#5]

@Jan Karel Pieterse Hi,

Amazing, so the script adds 99 to column A at the bottom of the row.  Which is great.

Now i need in Column B same row to be the formula =ROW()

.

Is this possible?

[#6 · Accepted solution]

You simply add this line of code immediately below that last one:
selectedSheet.getCell(theRow, 1).setFormula("=ROW()");

[#7]

@Jan Karel Pieterse Amazing!!

Thank you!!